Collecting data for CCSID problems

If CCSID settings are not correct, Db2 might display an incorrect character or encounter a CCSID conversion error. If Db2 returns an unexpected character from a query, message DSNT552I, SQLCODE -330, or SQLCODE -332, collect data for diagnosing CCSID problems.

Procedure

To collect data to diagnose CCSID problems:

  1. If you receive an unexpected display character or message DSNT552I, collect the application default CCSID setting that is stored in DSNHDECP.
  2. If you receive an unexpected SQLCODE, such as SQLCODE -330 or SQLCODE -332, generate a DSN1SDMP data set.
  3. Collect the bind encoding CCSID from the catalog.
    For example, issue the following select statements, where:
    • collection-id is the id of the collection
    • package-name is the name of the package
    • plan-name is the name of the plan
    SELECT ENCODING_CCSID FROM SYSIBM.SYSPACKAGE WHERE COLLID=collection-id 
 AND NAME=package-name;
SELECT ENCODING_CCSID FROM SYSIBM.SYSPLAN WHERE NAME=plan-name;
  4. Collect the object CCSID settings from the catalog for any involved databases, table spaces, columns, and stored procedures.
    For example, issue the following select statements, where:
    • database-name is the name of the database
    • tablespace-name is the name of the table space
    • column-name is the name of the column
    • table-name is the name of the table
    • routine-name is the name of the routine
    • schema-name is the name of the schema
    SELECT SBCS_CCSID FROM SYSIBM.SYSDATABASE WHERE NAME=database-name;
SELECT SBCS_CCSID FROM SYSIBM.SYSTABLESPACE WHERE NAME=tablespace-name 
 AND DBNAME=database-name;
SELECT CCSID FROM SYSIBM.SYSCOLUMNS WHERE  NAME=column-name
 AND TBNAME=table-name;
SELECT CCSID FROM SYSIBM.SYSPARMS WHERE  NAME=routine-name
 AND SCHEMA=schema-name;
  5. Collect the hexadecimal representation of the column data in error.
    For example, issue the following select statement, where:
    • column-in-error is the name of the column with the invalid character
    • user-table is the name of the user table with the invalid character
    SELECT column-in-error, HEX(column-in-error) FROM user-table
  6. If you are using a local terminal emulator program, find the CCSID setting.
    The Personal Communications (PCOM) CCSID setting is in the "Personal Parameters" menu "Host Code Page" field. For other terminal emulator programs, consult related documentation for its CCSID or code page setting.
  7. If data is being passed from a distributed system, generate a client performance trace.
    For example, issue the following -START TRACE command, where auth-id is the authorization ID needed to start the trace command:
    -START TRACE(PERFM) CLASS(1,2,32) DEST(GTF) TDATA(COR,TRA) IFCID(180)  AUTHID(auth-id)