The different uses of the default system CCSIDs are shown in Choosing the Application Server Default CHARNAME and CCSID. Data in columns which were migrated from a release earlier than Version 3 Release 3 have a CCSID which is obtained from rows in the SYSTEM.SYSOPTIONS catalog table. These rows are: MCCSIDSBCS, MCCSIDMIXED and MCCSIDGRAPHIC. For more information on the SYSTEM.SYSOPTIONS catalog table, see the DB2 Server for VSE & VM SQL Reference manual. To change either the application server default CCSIDs or the CCSIDs that are used for data in migrated columns, you must have DBA authority.
The only way to change the application server default CCSID is to change the application server default CHARNAME. This is done by specifying the CHARNAME parameter of the SQLSTART EXEC the next time the application server is started. This also updates the following columns of the SYSTEM.SYSOPTIONS catalog table: CCSIDSBCS, CCSIDMIXED and CCSIDGRAPHIC. For more information, see Character Set Considerations at Startup.
You may have to use different default CCSIDs for columns that were created before the migration than for columns created after the migration. For example, suppose that you are migrating your database and want to use the INTERNATIONAL character set (CCSID=500) for character columns that were created after the migration. Character columns that existed before migration were created with the ENGLISH character set (CCSID=37). To ensure that the data in existing character columns is displayed and interpreted correctly, (that is, as was done before the migration), you require an MCCSIDSBCS value of 37, and a CCSIDSBCS value of 500.
Be very careful when using different default CCSIDs. This should only be done when there is a specific reason for them to be different. When the application server and application requester have different CCSID values, character conversion cannot be avoided. This conversion has an associated performance overhead, and causes performance degradation. CCSID conversion of data also affects the sargability of predicates. For more information on performance, see the DB2 Server for VSE & VM Performance Tuning Handbook.
| Note: | Use caution when you change the application server default CCSIDs. For more information, see Determining CCSID Values. |
For many characters, the corresponding hexadecimal value in the
International code page is the same as in the English code page.
However, this is not true of all characters. For example, in the
English code page the hexadecimal value corresponding to the exclamation mark
(!) is '5A', but in the International code page the value is
'4F'. Table 23 lists the differences between the International code
page and the English code page.
Table 23. Differences between International Code Page and English Code Page
| Character | CCSID=37 | CCSID=500 |
|---|---|---|
| ^ | X'B0' | X'5F' |
| ¢ | X'4A' | X'B0' |
| ! | X'5A' | X'4F' |
| [ | X'BA' | X'4A' |
| ] | X'BB' | X'5A' |
| | | X'4F' | X'BB' |
| ¬ | X'5F' | X'BA' |
For more information on code page details, see the Character Data Representation Architecture Level 1, Registry manual.
Columns must be tagged with the CCSID that corresponds to the code page with which they were created or the results of queries on these columns will be unreliable. For example, suppose that the following column was created with the English character set before migration:
CHARDATA
--------
ABCDEFGH
kjp!
¬ds!
If MCCSIDSBCS is 37 (corresponding to English), and CCSIDSBCS is 500 (corresponding to International), performing a SELECT operation on this column after the migration gives the results shown above. However, if MCCSIDSBCS is incorrectly set to 500 (corresponding to the International character set), performing a SELECT operation on the column produces the following result:
CHARDATA
--------
ABCDEFGH
kjp]
^ds]
In this example, to ensure reliable results, MCCSIDSBCS must be 37, regardless of the value of CCSIDSBCS.
If you want to change the CCSID attribute of an existing column, use the DBS utility. For example, to change the default CCSID for data in columns that were created previous to the migration to Version 3 Release 3, use the DBS utility to do the following:
| Note: | You must use the DBSU "DATALOAD/DATAUNLOAD" commands, NOT the "UNLOAD/RELOAD" commands. |
The subtype attribute is only used when the CCSID attribute is null. If you have migrated from a release previous to Version 3 Release 3, existing character columns will have a CCSID value of null. For these columns, the subtype value is used to indicate their CCSID value. The CCSID value is either the value for MCCSIDSBCS (for a subtype of "S") or the value for MCCSIDMIXED (for a subtype of "M").
In some cases, columns with a null CCSID could have a subtype of "S" and contain mixed data. This can occur if the column was created without specifying the FOR MIXED DATA clause. In this case, the subtype must be changed to "M" in order for the correct CCSID to be used for this column. Otherwise, conversion errors can occur (for example, SQLCODE -330, SQLSTATE 22517).
To change the subtype, DBA authority is required to update the SYSTEM.SYSCOLUMNS table. Change the value in the SUBTYPE column from "S" to "M" for the required character column.