Each new release of DB2 introduces new columns or modifies existing columns in the EXPLAIN tables. DB2 has traditionally honored EXPLAIN tables in previous release formats, but it is best practice to use the current-release format in order to gather maximum benefit from your EXPLAIN data.

In addition, EXPLAIN tables were traditionally created in the EBCDIC encoding scheme because data in the DB2 catalog was EBCDIC encoded. Beginning with DB2 V8, almost all catalog data is Unicode encoded, and it is therefore increasingly beneficial to store EXPLAIN data in Unicode tables.

The APAR PK85068 deprecates use of EBCDIC EXPLAIN tables, and use of EXPLAIN tables in previous release formats. Beginning in DB2 V10 conversion mode, use of EXPLAIN tables in DB2 V7 or a prior-version format will fail with SQLCODE -20008, and use of EBCDIC EXPLAIN tables and EXPLAIN tables in DB2 V8 or V9 format will result in SQLCODE +20520. It is recommended that you begin to identify non-compliant tables and eliminate or convert them. To facilitate this task, this APAR provides the following:

• Queries for discovering non-compliant tables. In DB2 V8 and V9, these queries are added to job DSNTIJPM. In DB2 V8 only, they are added to job DSNTIJP9.
• A new sample job, DSNTIJXA, that drives a DB2 REXX exec called DSNTXTA to alter explain tables into current-version format. You can specify to convert all explain tables in DB2 or you can limit the conversion to a particular schema (creator).
• Another new sample job, DSNTIJXB, that drives a DB2 REXX exec called DSNTXTB to generate statements for migrating data from EBCDIC explain tables in a specified schema. DSNTXTB produces two equivalent types statements:
  • Control statements for processing by the DB2 Utilities cross loader (EXEC SQL)
  • SQL statements for processing by SPUFI or a similar dynamic SQL tool.

These statements should not be processed before they are inspected and validated by the user.

• A third new sample job, DSNTIJXC, that shows how to process the statements generated by running DSNTIJXB.

In general, use this process (specific details follow):

• Identify non-compliant explain tables. Reports are provided in job DSNTIJPM (and in DSNTIJP9 in V8 only) that identify all schemas having one or more such tables.
• Bring all explain tables into current release format. You can use job DSNTIJXA to bring all explain tables in a specified schema into current release format. To convert all non-compliant explain tables regardless of the schema, specify an asterisk as the schema.
• Migrate all EBCDIC-encoded explain tables to Unicode. This is a two step process:
  1. Use job DSNTIJXB to generate DB2 cross loader control statements (and equivalent SQL statements) that can be used to copy all EBCDIC explain tables in a specified schema ID to Unicode equivalents. All explain tables belonging to the specified schema must first be in the current release format.
  2. After examing the control statements generated by DSNTIJXB, use job DSNTIJXC to process them. DSNTIJXC assumes that you have purchased the DB2 Utilities Suite. If you have not, you can use the equivalent SQL generated by DSNTIJXB. The process works as follows for each table:
     1. RENAME TABLE to append ‚_EBCDIC‘ to the EBCDIC explain table. Renaming the EBCDIC table makes its original name available to its Unicode replacement.
     2. CREATE TABLE to create the explain table as a Unicode table
     3. CREATE AUX TABLE (if the table is a LOB table)
     4. CREATE INDEX for each index on the EBCDIC table. To avoid name conflicts, the new indexes are prefixed with DSNU. If the name already starts with DSN then a ‚U‘ is inserted at the fourth position.
        Examples:
        PLAN_TABLE_IDX1 -> DSNU_PLAN_TABLE_IDX1
        DSN_STATEMNT_TABLE_IDX1 -> DSNU_STATEMNT_TABLE_IDX1
     5. DECLARE CURSOR on the EBCDIC table to extract the data
     6. LOAD DATA INTO the Unicode table, using the cursor on the _EBCDIC table
  3. After converting an EXPLAIN table from EBCDIC to Unicode, check for joins to this table. When you convert EXPLAIN tables to UNICODE, their applications which join with EXPLAIN tables can have different results due to the CCSID conversion. For more information, see ‚Objects with different CCSIDs in the same SQL statement‘ in the DB2 Internationalization Guide.

Repeat these steps until all explain tables are Unicode encoded and in current release format.

For more information the IBM’s description of APAR PK85068: EXPLAIN TABLE MIGRATION.