A fix is available
APAR status
Closed as new function.
Error description
Enhance Db2 installation CLIST, Db2-supplied stored procedures, batch REXX execs, and DSNTESC to support new EXPLAIN table columns
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS and Db2 13 for z/OS * * users of the following: * * o Db2-supplied stored procedures * * - SYSPROC.ADMIN_EXPLAIN_MAINT * * - SYSPROC.ADMIN_INFO_SQL * * o Db2-supplied REXX execs: * * - DSNTXTA (Job DSNTIJXA) * * - DSNTXTB (Job DSNTIJXB) * * o Db2 sample job DSNTESC * * o Pre-migration check out jobs * * DSNTIJPE/DSNTIJPM * * o Db2 installation CLIST * **************************************************************** * PROBLEM DESCRIPTION: * * APAR PH44421 introduced new EXPLAIN * * table columns. The Db2-supplied * * stored procedures, REXX execs, sample * * job, pre-migration checkout jobs, and * * Db2 installation CLIST that are * * affected by these new EXPLAIN table * * columns must be updated. * **************************************************************** * RECOMMENDATION: * **************************************************************** APAR PH44421 introduced new EXPLAIN table columns and corresponding new indexes on the new columns, as listed in Table 01 below. o Db2 12: These new columns and new indexes are valid only with Db2 function level V12R1M500 and higher. o Db2 13: These new columns and new indexes are valid with Db2 function level V13R1M100 and higher. Table 01. EXPLAIN Tables New Columns and New Indexes -------------------------------------------------------------- |Row| ExplainTbName | New Columns & New Indexes | |---|--------------------------------------------------------| | 1 | PLAN_TABLE | New Column: | | | DSN_FUNCTION_TABLE | ----------- | | | DSN_PREDICAT_TABLE | "AP_PLANID" | | | DSN_STRUCT_TABLE | CHAR(16) | | | DSN_PGROUP_TABLE | FOR BIT DATA | | | DSN_PTASK_TABLE | | | | DSN_FILTER_TABLE | New Index: | | | DSN_DETCOST_TABLE | ---------- | | | DSN_SORT_TABLE | CREATE INDEX | | | DSN_SORTKEY_TABLE | <ExplainTbName>_APID_IX | | | DSN_PGRANGE_TABLE | ON <ExplainTbName> | | | DSN_VIEWREF_TABLE | ( "AP_PLANID" ); | | | DSN_QUERYINFO_TABLE | | | | DSN_COLDIST_TABLE | | | | DSN_KEYTGTDIST_TABLE | | | | DSN_STAT_FEEDBACK | | |---|--------------------------------------------------------| | 2 | DSN_STATEMENT_CACHE_TABLE| New Columns: | | | | ------------ | | | | "STMT_HASHID2" | | | | CHAR(8) | | | | FOR BIT DATA | | | | "STMT_HASH2VER" | | | | INTEGER | | | | "AP_PLANID" | | | | CHAR(16) | | | | FOR BIT DATA | | | | "AP_PLANHASH" | | | | CHAR(8) | | | | FOR BIT DATA | | | | "AP_PLANHASHVER" | | | | INTEGER | | | | "CONNECTION_TYPE" | | | | CHAR(8) | | | | "CLIENT_USERID" | | | | VARCHAR(128) | | | | "CLIENT_APPLNAME" | | | | VARCHAR(255) | | | | "CLIENT_WRKSTNNAME" | | | | VARCHAR(255) | | | | | | | | New Index: | | | | ---------- | | | | CREATE INDEX | | | | DSN_STATEMENT_CACHE_ML_IX1| | | | ON | | | | DSN_STATEMENT_CACHE_TABLE | | | | ( "STMT_HASHID2" | | | | ,"AP_PLANID" | | | | ); | |---|--------------------------------------------------------| | 3 | DSN_STATEMNT_TABLE | New Columns: | | | | ------------ | | | | "STMT_HASHID2" | | | | CHAR(8) | | | | FOR BIT DATA | | | | "STMT_HASH2VER" | | | | INTEGER | | | | "AP_PLANID" | | | | CHAR(16) | | | | FOR BIT DATA | | | | "AP_PLANHASH" | | | | CHAR(8) | | | | FOR BIT DATA | | | | "AP_PLANHASHVER" | | | | INTEGER | | | | "AP_SERVICE_DATA" | | | | VARCHAR(512) | | | | FOR BIT DATA | | | | "CONNECTION_TYPE" | | | | CHAR(8) | | | | "CLIENT_USERID" | | | | VARCHAR(128) | | | | "CLIENT_APPLNAME" | | | | VARCHAR(255) | | | | "CLIENT_WRKSTNNAME" | | | | VARCHAR(255) | | | | | | | | New Index: | | | | ---------- | | | | CREATE INDEX | | | | DSN_STATEMNT_ML_IX1 | | | | ON DSN_STATEMNT_TABLE | | | | ( "STMT_HASHID2" | | | | ,"AP_PLANID" | | | | ); | -------------------------------------------------------------- The following Db2-supplied stored procedures, REXX execs, sample job, pre-migration checkout jobs for Db2 13, and Db2 installation CLIST that are affected by these new EXPLAIN table columns must be updated: o Db2-supplied stored procedure SYSPROC.ADMIN_EXPLAIN_MAINT and REXX execs DSNTXTA and DSNTXTB which standardize Db2 EXPLAIN tables to the current release format. o Db2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL which captures statistics about a Db2 subsystem, its objects, and applications that helps IBM Support re-create and troubleshoot problems. o Db2 sample job DSNTESC which contains DDL statements to create sample EXPLAIN tables. o Pre-migration checkout jobs for Db2 13 which run queries on the Db2 12 catalog: - Db2 12: DSNTIJPE - Db2 13: DSNTIJPM o Db2 installation CLIST which generates the following customized jobs for install and migration, where appicable: - Db2 12: DSNTESC and DSNTIJXA - Db2 13: DSNTESC SQLEXPLAIN DB2MIGV13/K
Problem conclusion
Temporary fix
Comments
This APAR enhances the Db2-supplied stored procedures, REXX execs, sample job DSNTESC, pre-migration checkout jobs for Db2 13 (DSNTIJPE/DSNTIJPM), and Db2 installation CLIST, as listed in the PROBLEM SUMMARY section above, to enable them to process the new Db2ZAI columns added to EXPLAIN tables by APAR PH44421.
APAR Information
APAR number
PH48053
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2022-07-18
Closed date
2023-01-10
Last modified date
2023-02-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI83990 UI83991
Modules/Macros
DSNTINMF DSN@DMEM DSNADMSS DSNTINST DSNTINS3 DSNTXTA DSNTIPMN DSNTXTB DSNADMEM DSNTIWMN DSNTIJPE DSNTESC DSN@DMSS DSNTIJPM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
01 February 2023