迁移步骤 25： 将EXPLAIN表转换为当前格式

关于本任务

在 Db2 12 中，如果EXPLAIN表是 Db2 11 格式，则调用EXPLAIN处理的语句或命令将返回SQLCODE +20520原因代码2。

Db2 返回SQLCODE -20008，原因代码2，用于调用EXPLAIN处理的语句或命令，前提是EXPLAIN表采用 格式。DB2® 10 在 Db2 11 中，EXPLAIN表需要使用Unicode编码。 如果EXPLAIN表使用EBCDIC编码，则返回SQLCODE -878。

注意： 此迁移步骤将EXPLAIN表更新为 Db2 12 在函数级别100支持的格式。

要转换EXPLAIN表，请完成以下步骤：

1. 您可以通过运行以下查询来识别Db2 12 格式的EXPLAIN表：

   

   

   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_STAT_FEEDBACK'
   AND MAX(COLNO) < 21)
   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;

   

   

2. 如果查询识别出任何非 Db2 12 格式的EXPLAIN表，请自定义并运行作业DSNTIJXA。

   作业 DSNTIJXA 调用 REXX 执行程序 DSNTXTA，后者将所有 EXPLAIN 表或属于指定创建者 ID 的 EXPLAIN 表转换为 Db2 12 格式。

   要自定义作业，请提供要迁移为 Db2 12 格式的表的创建者ID、 Db2 子系统名称和授权ID。 要迁移所有EXPLAIN表，请指定星号作为创建者ID。

后续操作

在启用501级或更高功能之前，请自定义并运行作业DSNTIJXA，将EXPLAIN表和相关对象更新为 Db2 12 500级或更高功能的格式。

重要提示： 在完成此步骤之前，请确认 APAR PH48053 已申请PTF。

更多信息，请参阅迁移时激活 Db2 12项新功能。