Migration step 24: Convert EXPLAIN tables to the current format

When you migrate to Db2 13, you should convert your EXPLAIN tables to the current (Db2 13) format. 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.

About this task

In Db2 13, statements or commands that invoke EXPLAIN processing return SQLCODE +20520 reason code 2 if an EXPLAIN table is in Db2 12 format.

Db2 returns SQLCODE -20008 reason code 2 for statements or commands that invoke EXPLAIN processing if the EXPLAIN tables are in a pre-DB2 10 format. In Db2 12, EXPLAIN tables are required to be encoded in Unicode. If an EXPLAIN table is encoded in EBCDIC, SQLCODE -878 is returned.

Procedure

To convert EXPLAIN tables, complete the following steps:

  1. You can identify EXPLAIN tables that are in a pre-Db2 13 format by running the following query:

    Begin general-use programming interface information.

    Start of change
    WITH OLD_FORMAT_EXPLAIN_TABLES (FULL_NAME) AS
    ( SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)
    FROM SYSIBM.SYSCOLUMNS
    WHERE TBNAME IN( 'DSN_COLDIST_TABLE'
    , 'DSN_DETCOST_TABLE'
    , 'DSN_FILTER_TABLE'
    , 'DSN_FUNCTION_TABLE'
    , 'DSN_KEYTGTDIST_TABLE'
    , 'DSN_PGRANGE_TABLE'
    , 'DSN_PGROUP_TABLE'
    , 'DSN_PREDICAT_TABLE'
    , 'DSN_PREDICATE_SELECTIVITY'
    , 'DSN_PROFILE_HISTORY'
    , 'DSN_PROFILE_TABLE'
    , 'DSN_PTASK_TABLE'
    , 'DSN_QUERY_TABLE'
    , 'DSN_QUERYINFO_TABLE'
    , 'DSN_SORT_TABLE'
    , 'DSN_SORTKEY_TABLE'
    , 'DSN_STAT_FEEDBACK'
    , 'DSN_STATEMENT_CACHE_TABLE'
    , 'DSN_STATEMNT_TABLE'
    , 'DSN_STRUCT_TABLE'
    , 'DSN_VIEWREF_TABLE'
    , 'DSN_VIRTUAL_INDEXES'
    , 'DSN_VIRTUAL_KEYTARGETS'
    , 'PLAN_TABLE'
    )
    GROUP BY TBCREATOR, TBNAME
    HAVING (TBNAME = 'DSN_COLDIST_TABLE'
    AND MAX(COLNO) < 23)
    OR (TBNAME = 'DSN_DETCOST_TABLE'
    AND MAX(COLNO) < 139)
    OR (TBNAME = 'DSN_FILTER_TABLE'
    AND MAX(COLNO) < 20)
    OR (TBNAME = 'DSN_FUNCTION_TABLE'
    AND MAX(COLNO) < 22)
    OR (TBNAME = 'DSN_KEYTGTDIST_TABLE'
    AND MAX(COLNO) < 23)
    OR (TBNAME = 'DSN_PGRANGE_TABLE'
    AND MAX(COLNO) < 17)
    OR (TBNAME = 'DSN_PGROUP_TABLE'
    AND MAX(COLNO) < 44)
    OR (TBNAME = 'DSN_PREDICAT_TABLE'
    AND MAX(COLNO) < 44)
    OR (TBNAME = 'DSN_PREDICATE_SELECTIVITY'
    AND MAX(COLNO) <  17)             
    OR (TBNAME = 'DSN_PROFILE_HISTORY'
    AND MAX(COLNO) < 15)
    OR (TBNAME = 'DSN_PROFILE_TABLE'
    AND MAX(COLNO) < 15)
    OR (TBNAME = 'DSN_PTASK_TABLE'
    AND MAX(COLNO) < 27)
    OR (TBNAME = 'DSN_QUERY_TABLE'
    AND MAX(COLNO) < 16)
    OR (TBNAME = 'DSN_QUERYINFO_TABLE'
    AND MAX(COLNO) < 19)
    OR (TBNAME = 'DSN_SORT_TABLE'
    AND MAX(COLNO) < 18)
    OR (TBNAME = 'DSN_SORTKEY_TABLE'
    AND MAX(COLNO) < 23)
    OR (TBNAME = 'DSN_STATEMENT_CACHE_TABLE'
    AND MAX(COLNO) < 104)
    OR (TBNAME = 'DSN_STATEMNT_TABLE'
    AND MAX(COLNO) < 34)
    OR (TBNAME = 'DSN_STRUCT_TABLE'
    AND MAX(COLNO) < 22)
    OR (TBNAME = 'DSN_VIEWREF_TABLE'
    AND MAX(COLNO) < 14)
    OR (TBNAME = 'DSN_VIRTUAL_INDEXES'
    AND MAX(COLNO) < 150)
    OR (TBNAME = 'DSN_VIRTUAL_KEYTARGETS'
    AND MAX(COLNO) < 16)           
    OR (TBNAME = 'PLAN_TABLE'
    AND MAX(COLNO) < 68)
    UNION
    SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)
    FROM SYSIBM.SYSCOLUMNS
    WHERE TBNAME IN( 'DSN_DETCOST_TABLE'
    , 'DSN_FILTER_TABLE'
    , 'DSN_FUNCTION_TABLE'
    , 'DSN_PGRANGE_TABLE'
    , 'DSN_PGROUP_TABLE'
    , 'DSN_PREDICAT_TABLE'
    , 'DSN_PROFILE_HISTORY'
    , 'DSN_PROFILE_TABLE'
    , 'DSN_PTASK_TABLE'
    , 'DSN_QUERY_TABLE'
    , 'DSN_SORT_TABLE'
    , 'DSN_SORTKEY_TABLE'
    , 'DSN_STATEMENT_CACHE_TABLE'
    , 'DSN_STATEMENT_RUNTIME_INFO'
    , 'DSN_STATEMNT_TABLE'
    , 'DSN_STRUCT_TABLE'
    , 'DSN_VIEWREF_TABLE'
    , 'DSN_VIRTUAL_INDEXES'
    , 'PLAN_TABLE'
    )
    AND ( (NAME = 'ACCESSCREATOR' AND LENGTH <> 128)
    OR (NAME = 'ACCESSNAME' AND LENGTH <> 128)
    OR (NAME = 'APPLNAME' AND LENGTH <> 24)
    OR (NAME = 'COLLID' AND LENGTH <> 128)
    OR (NAME = 'CORRELATION_NAME' AND LENGTH <> 128)
    OR (NAME = 'CREATOR' AND LENGTH <> 128)
    OR (NAME = 'FUNCTION_NAME' AND LENGTH <> 128)
    OR (NAME = 'FUNCTION_TEXT' AND LENGTH <> 1500)
    OR (NAME = 'GROUP_MEMBER' AND LENGTH <> 24)
    OR (NAME = 'HINTUSED' AND LENGTH <> 128)
    OR (NAME = 'OPTHINT' AND LENGTH <> 128)
    OR (NAME = 'PATH' AND LENGTH <> 2048)
    OR (NAME = 'PLANNAME' AND LENGTH <> 24)
    OR (NAME = 'PROGNAME' AND LENGTH <> 128)
    OR (NAME = 'REMARKS' AND LENGTH <> 762)
    OR (NAME = 'SCHEMA_NAME' AND LENGTH <> 128)
    OR (NAME = 'SPEC_FUNC_NAME' AND LENGTH <> 128)
    OR (NAME = 'TNAME' AND LENGTH <> 128)
    OR (NAME = 'VERSION' AND LENGTH <> 122)
    OR (NAME = 'VIEW_CREATOR' AND LENGTH <> 128)
    OR (NAME = 'VIEW_NAME' AND LENGTH <> 128)
    )
    )
    SELECT DISTINCT(FULL_NAME)
    FROM OLD_FORMAT_EXPLAIN_TABLES
    ORDER BY FULL_NAME;
    End of change

    End general-use programming interface information.

  2. If the query identifies any EXPLAIN tables that are not in Db2 13 format, customize and run job DSNTIJXA.

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

    To customize the job, provide the creator ID of the tables to migrate to the Db2 13 format, a Db2 subsystem name, and an authorization ID. To migrate all EXPLAIN tables, specify an asterisk as the creator ID.