Converting EXPLAIN tables for migration from DB2 Version 8

If you migrated from Version 8, convert remaining pre-DB2® 10 and EBCDIC-encoded EXPLAIN tables after you migrate to DB2 10 new-function mode.

About this task

If you run job DSNTIJXA in conversion mode from Version 8, most of the EXPLAIN tables are converted to DB2 10 format. The DSN_STATEMENT_CACHE_TABLE EXPLAIN table is converted to DB2 9 format because the DB2 10 format is not supported in conversion mode from Version 8.

Start of changeIf you ran installation job DSNTIJSG, applied the PTF for APAR PM28500, and then ran DSNTIJXA, the DSN_PROFILE_TABLE and DSN_PROFILE_HISTORY tables contain a downlevel IPADDR column name. This occurs because renaming of columns is not supported in conversion mode from Version 8.End of change

To convert the table to DB2 10 format, you must run DSNTIJXA again in DB2 10 new-function mode. You can also choose to skip running DSNTIJXA in conversion mode from Version 8 and just run it once in new-function mode to convert all EXPLAIN tables to DB2 10 format. If you skip DSNTIJXA in conversion mode from Version 8, you must also convert the EXPLAIN tables to Unicode encoding after you migrate to new-function mode.

Procedure

To convert EXPLAIN tables while the subsystem is in new-function mode:

  1. Customize and run job DSNTIJPM to identify EXPLAIN tables that need to be removed or converted. Report 14 identifies EBCDIC-encoded EXPLAIN tables. Report 15 identifies EXPLAIN tables that are not in the DB2 10 format.
  2. Customize job DSNTIJXA by providing the creator ID of the table or tables to migrate to the DB2 10 format, a DB2 subsystem name, and an authorization ID. To migrate all EXPLAIN tables, specify an asterisk as the creator ID.
  3. Run job DSNTIJXA. Job DSNTIJXA calls REXX exec DSNTXTA, which migrates to the DB2 10 format all EXPLAIN tables or EXPLAIN tables that belong to the specified creator ID.
  4. If you did not convert EBCDIC-encoded tables to Unicode in conversion mode from Version 8, complete the following steps to migrate all EBCDIC-encoded EXPLAIN tables to Unicode:
    1. Customize job DSNTIJXB with the following values:
      • A DB2 subsystem name
      • An authorization ID
      • The schema name of the EXPLAIN tables to convert
      • The name of the target database
      • The name of the target 4 KB page table space
      • The name of the target 8 KB page table space
      • The name of the target 16 KB page table space
      • The name of the target 8 KB page LOB table space
      • The name of the target 32 KB page LOB table space
    2. Run job DSNTIJXB. Job DSNTIJXB calls REXX exec DSNTXTB, which generates DB2 cross-loader control statements and equivalent SQL statements that can be used to copy all EBCDIC EXPLAIN tables under a specified creator ID to Unicode equivalents. All EXPLAIN tables that belong to the specified creator ID must be in the current release format.
    3. If you have purchased the DB2 Utilities Suite, run job DSNTIJXC to process the control statements that were generated by DSNTIJXB. Otherwise, use the equivalent SQL statements that were generated by DSNTIJXB to convert the tables to Unicode.
    4. After you convert an EXPLAIN table from EBCDIC encoding to Unicode encoding, check for joins to that table. When you convert EXPLAIN tables to Unicode, applications that join with EXPLAIN tables might have different results because of the CCSID conversion. For more information, see Objects with different CCSIDs in the same SQL statement.
    5. Repeat steps a, b, c, and d for each creator that has at least one EBCDIC EXPLAIN table.