Start of change

Migration step 24: Convert EXPLAIN tables to the current format and encoding type

When you migrate to DB2® 10, you should convert your EXPLAIN tables to the current (DB2 10) format and Unicode encoding. A table that is not in the current format has fewer columns than are available in the current release, or it has columns that have a different length or data type than expected in the current release. Unicode EXPLAIN tables are preferred because the DB2 catalog tables are encoded in Unicode.

About this task

Starting in DB2 DB2 10 conversion mode (from both Version 8 and Version 9), EXPLAIN tables must be in Version 8 or later format and preferably encoded in Unicode. DB2 returns SQLCODE -20008 reason code 2 for statements or commands that invoke EXPLAIN processing if the EXPLAIN tables are in a pre-Version 8 format. Statements or commands that invoke EXPLAIN processing return SQLCODE +20520 reason code 2 if an EXPLAIN table is in Version 8 or DB2 9 format, regardless of the encoding type. After you convert your EXPLAIN tables to Version 10 format, you must also convert the tables to Unicode encoding. If an EXPLAIN table is in Version 10 format and encoded in EBCDIC, SQLCODE -878 is returned.

Procedure

To convert EXPLAIN tables:

  1. If premigration job DSNTIJPM report 15 identifies any EXPLAIN tables that are not in DB2 10 format, customize job DSNTIJXA. To customize the job, provide the creator ID of the 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. Start of change

    Begin general-use programming interface information.

    End general-use programming interface information.

    Begin DB2 Version 8 to DB2 10 migration information
    The DB2 10 format of the DSN_STATEMENT_CACHE_TABLE EXPLAIN table is not supported in conversion mode from Version 8. If you run DSNTIJXA in conversion mode from Version 8, this table is converted to DB2 9 format.
    End DB2 Version 8 to DB2 10 migration information

    Also, if 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.

    To convert these tables to DB2 10 format, you must run DSNTIJXA again in 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. If you choose to run DSNTIJXA once in new-function mode, stop this procedure here.

    End of change
  2. Run job DSNTIJXA.

    Job DSNTIJXA calls REXX exec DSNTXTA, which alters to the DB2 10 format all EXPLAIN tables or EXPLAIN tables that belong to the specified creator ID.

    Exception:
    Begin DB2 Version 8 to DB2 10 migration information
    In conversion mode from Version 8, DSNTIJXA cannot convert DSN_QUERY_TABLE and DSN_PTASK_TABLE from the Version 8 format. Use job DSNTIJXB to convert these tables instead.
    End DB2 Version 8 to DB2 10 migration information
  3. If premigration job DSNTIJPM report 14 identifies any EXPLAIN tables that use EBCDIC encoding, convert them to Unicode by completing the following steps.
    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 except for DSN_QUERY_TABLE, which can be in Version 8 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.
End of change