DB2 Server for VM: System Administration

Examples of Setting Values for an Installation

This section discusses two examples of using the application server default CHARNAME JAPANESE-ENGLISH (CCSID=5035). The first example shows how to specify this CHARNAME and enable mixed string manipulation. The second example shows how to specify this CHARNAME without enabling mixed string manipulation and how to prevent the verification of character strings that contain mixed data. (Mixed string manipulation is the ability to specify mixed SQL identifiers, such as columns.)

Example 1

Suppose that you want to use the mixed JAPANESE-ENGLISH CCSID, 5035, as your application server default CCSID, and you also want to have the ability to do mixed string manipulation. To do this, set up your environment as follows:

1. Ensure that your terminal controllers are generated to use the correct code pages.

   The CCSID you want to use is 5035. You must define the controller to use the character set 1172 for the SBCS character set, and code page 1027 for the SBCS code page. For the DBCS characters, specify the character set 370 and the code page 300.

2. Install the database manager.

   The application server default CCSID for a newly installed database manager is 500 (CHARNAME=INTERNATIONAL). After installation, the SYSTEM.SYSOPTIONS catalog table contains the following information:

     CHARNAME=INTERNATIONAL (the name of 500)
     CCSIDSBCS=500
     CCSIDMIXED=0
     CCSIDGRAPHIC=0
     DBCS=NO
     CHARSUB=SBCS
     .............
   

3. Change the value of the application server default CHARNAME to JAPANESE-ENGLISH

   Start the application server by using the SQLSTART EXEC. Specify CHARNAME=JAPANESE-ENGLISH. Message ARI0159D is displayed that informs you that the new CHARNAME (JAPANESE-ENGLISH) is different from the current default (INTERNATIONAL). You are prompted to enter either 1 (YES) to change the default, 0 (NO) to leave the default unchanged, or 111 (QUIT) to shut down the application server. Type 1 (for YES) and press ENTER.

   After the application server is started, the SYSTEM.SYSOPTIONS catalog table should contain the following information:

     CHARNAME=JAPANESE-ENGLISH
     CCSIDSBCS=1027      (the single-byte portion of 5035)
     CCSIDMIXED=5035
     CCSIDGRAPHIC=4396   (the double-byte portion of 5035)
     DBCS=NO
     CHARSUB=SBCS
     .................
   

4. To enable mixed string manipulation, change the value for DBCS in SYSTEM.SYSOPTIONS from NO to YES. You can use either ISQL or the DBS utility.
5. Because most of the character columns will contain mixed data, you should also change the value for CHARSUB from SBCS to MIXED.
6. To cause the DBCS and CHARSUB values in SYSTEM.SYSOPTIONS to be used as the new application server defaults, you must stop the application server, and then restart it.

   The changes are now complete. The SYSTEM.SYSOPTIONS catalog table contains the following information:

     CHARNAME=JAPANESE-ENGLISH
     CCSIDSBCS=1027      (the single-byte portion of 5035)
     CCSIDMIXED=5035
     CCSIDGRAPHIC=4396   (the double-byte portion of 5035)
     DBCS=YES
     CHARSUB=MIXED
     .................
   

7. To set these values for all the application requesters, run the SQLGLOB EXEC. Issue the following command:

     SQLGLOB CHARNAME(JAPANESE-ENGLISH) DBCS(YES)...
   

   Note that CHARSUB is only applicable to the application server.

Example 2

Suppose that you want to use the mixed JAPANESE-ENGLISH CCSID, 5035, as your application server default CCSID. Because you must be able to both store DBCS characters, and retrieve DBCS characters from graphic columns (GRAPHIC, VARGRAPHIC, or LONG VARGRAPHIC), you cannot specify an ENGLISH single-byte CCSID such as 37 or 1027. Also suppose that you do not want the ability to do mixed string manipulation, and you want to prevent the database manager from verifying character strings for mixed data. In addition, you also want character columns that are created without the explicit specification of a CCSID or a subtype to default to the SBCS subtype and CCSID. To do this, set up your environment as follows:

1. Ensure that your terminal controllers are generated to use the correct code pages.

   The CCSID you want to use is 5035. You must define the controller to use the character set 1172 for the SBCS character set, and code page 1027 for the SBCS code page. For the DBCS characters, specify the character set 370 and the code page 300.

2. Install the database manager.

   The application server default CCSID for a newly installed database manager is 500 (CHARNAME=INTERNATIONAL). After installation, the SYSTEM.SYSOPTIONS catalog table contains the following information:

     CHARNAME=INTERNATIONAL (the name of 500)
     CCSIDSBCS=500
     CCSIDMIXED=0
     CCSIDGRAPHIC=0
     DBCS=NO
     CHARSUB=SBCS
     .............
   

3. Change the value of the application server default CHARNAME to JAPANESE-ENGLISH.

   Start the application server by using the SQLSTART EXEC. Specify CHARNAME=JAPANESE-ENGLISH. Message ARI0159D is displayed that informs you that the new CHARNAME (JAPANESE-ENGLISH) is different from the current default (INTERNATIONAL). You are prompted to enter either 1 (YES) to change the default, 0 (NO) to leave the default unchanged, or 111 (QUIT) to shut down the application server. Type 1 (for YES) and press ENTER.

   After the application server is started, the SYSTEM.SYSOPTIONS catalog table should contain the following information:

     CHARNAME=JAPANESE-ENGLISH
     CCSIDSBCS=1027      (the single-byte portion of 5035)
     CCSIDMIXED=5035
     CCSIDGRAPHIC=4396   (the double-byte portion of 5035)
     DBCS=NO
     CHARSUB=SBCS
     .................
   

4. Because you do not want to enable mixed string manipulation, and you do not want the database manager to verify character strings for mixed data, leave the DBCS option set to NO (even though the database manager uses a mixed CCSID). This still allows you to:
   • Issue CREATE TABLE or ALTER TABLE statements to either add or create GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC columns. The CCSID for these columns will be taken from value for CCSIDGRAPHIC in the SYSTEM.SYSOPTIONS catalog table.
   • Insert into graphic columns from graphic host variables.
   • Select graphic columns into graphic host variables.
   • Use graphic constants in SQL statements.
5. Because most character columns will contain SBCS data, leave the value for CHARSUB as SBCS. When you need to either create or add a mixed character column, you can specify the FOR MIXED DATA clause or the CCSID clause explicitly for the CREATE TABLE or the ALTER TABLE statement.
6. To set these values for all the application requesters, run the SQLGLOB EXEC. Issue the following command:

     SQLGLOB CHARNAME(JAPANESE-ENGLISH) DBCS(YES)...
   

   For an application requester to be able to use graphic data, the application requester must use a mixed CCSID as the default. One exception exists. If SQLINIT PROTOCOL(SQLDS) is issued from an application requester (or if the application server was started with SQLSTART PROTOCOL(SQLDS)), the application server responds to the application requester with the expectation that the application requester is using the same mixed CCSID as the application server is using. If the user specified a different value for the SQLINIT CHARNAME parameter, the application server ignores this value. (However, folding performed by the application requester is still based on the application requester default CHARNAME, regardless of the PROTOCOL parameter specified. In this case, if the application server CHARNAME and the application requester CHARNAME are not the same, unexpected results can occur. See Setting the Application Requester Default CHARNAME and CCSIDs for more information.)

   In this step, the DBCS option is set to YES. Because DBCS strings can be truncated when displayed by ISQL, setting the DBCS option to YES ensures that truncation is done properly. If truncation is not a consideration, you can set the DBCS option to NO.

Identifying Classification and Translation Tables for a CCSID

To identify either the classification table or the translation table that is used for folding characters to uppercase for a specific CCSID, do the following:

1. Review the CHARNAME column of the SYSTEM.SYSCCSIDS catalog table for the CHARNAME value of the CCSID.
2. Review the NAME column of the SYSTEM.SYSCHARSETS catalog table for the value that matches the CHARNAME of the CCSID. That row contains both the classification table and the translation table for the CCSID.