Start of change

Converting EXPLAIN tables (before migration)

In DB2® 10 , EXPLAIN tables that are in pre-Version 8 format are no longer supported. EXPLAIN tables that are in Version 8 or Version 9 format and EBCDIC-encoded EXPLAIN tables are deprecated. Before you migrate to Version 10, convert your EXPLAIN tables to Version 8 or DB2 9 format and Unicode encoding.

About this task

Starting in DB2 10 conversion mode (from both Version 8 and Version 9), DB2 returns SQLCODE -20008 reason code 2 for statements or commands that invoke EXPLAIN processing if 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 Version 9 format. If an EXPLAIN table is in Version 10 format and encoded in EBCDIC, SQLCODE -878 is returned.

Because converting your EXPLAIN tables might be time-consuming, you should complete this task as far before migration as possible.

Procedure

To convert your EXPLAIN tables before migration:

  • If you have a Version 8 subsystem:
    1. Ensure that the subsystem is in Version 8 new-function mode.
    2. Customize and run job DSNTIJPA 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 Version 8 format.
    3. Customize the Version 8 DSNTIJXA job by providing the creator ID of the table or tables to migrate to the Version 8 format, a DB2 subsystem name, and an authorization ID. To migrate all EXPLAIN tables, specify an asterisk as the creator ID.
    4. Run the Version 8 DSNTIJXA job. Job DSNTIJXA calls REXX exec DSNTXTA, which migrates to the Version 8 format all EXPLAIN tables or EXPLAIN tables that belong to the specified creator ID.
    5. Migrate all EBCDIC-encoded EXPLAIN tables to Unicode by completing the following steps:
      1. Customize the Version 8 DSNTIJXB job 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 the Version 8 DSNTIJXB job. 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 the Version 8 DSNTIJXC job 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 through d for each creator that has at least one EBCDIC EXPLAIN table.
  • If you have a DB2 9 subsystem:
    1. Ensure that the subsystem is in Version 9 new-function mode.
    2. Customize and run job DSNTIJPA 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 Version 9 format.
    3. Customize the Version 9 DSNTIJXA job by providing the creator ID of the table or tables to migrate to the Version 9 format, a DB2 subsystem name, and an authorization ID. To migrate all EXPLAIN tables, specify an asterisk as the creator ID.
    4. Run the Version 9 DSNTIJXA job. Job DSNTIJXA calls REXX exec DSNTXTA, which migrates to the Version 9 format all EXPLAIN tables or EXPLAIN tables that belong to the specified creator ID.
    5. Migrate all EBCDIC-encoded EXPLAIN tables to Unicode by completing the following steps:
      1. Customize the Version 9 DSNTIJXB job 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 the Version 9 DSNTIJXB job. 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 the Version 9 DSNTIJXC job 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 through d for each creator that has at least one EBCDIC EXPLAIN table.

What to do next

After you migrate to DB2 10, you will need to also convert your EXPLAIN tables to DB2 10 format.
End of change