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:
- Ensure that the subsystem is in Version 8 new-function
mode.
- 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.
- 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.
- 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.
- Migrate all EBCDIC-encoded EXPLAIN tables to Unicode
by completing the following steps:
- 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
- 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.
- 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.
- 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
- Repeat steps a through d for each creator that has at least one
EBCDIC EXPLAIN table.
- If you have a DB2 9 subsystem:
- Ensure that the subsystem is in Version 9 new-function
mode.
- 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.
- 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.
- 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.
- Migrate all EBCDIC-encoded EXPLAIN tables to Unicode
by completing the following steps:
- 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
- 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.
- 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.
- 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
- 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.