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:
- 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.
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.
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.
- 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: 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.
- If premigration job DSNTIJPM report
14 identifies any EXPLAIN tables that use EBCDIC encoding, convert
them to Unicode by completing the following steps.
- 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
- 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.
- 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.
- 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, b, c, and d for each creator that has
at least one EBCDIC EXPLAIN table.