A fix is available
APAR status
Closed as program error.
Error description
DSNTIJOS may create DB2OSC.DSN_PTASK_TABLE with the wrong column names when using a CCSID other than CCSID 37. . Also - Add blanks after commas that separate numeric parms in the IN option of the CHECK clauses for columns defined in the DSN_VIRTUAL_INDEX(ES) table. The blanks are needed to prevent SQLCODE -104 errors when the DSNHDECP COMMA=DECIMAL option is in effect. This change is required in DSNTESC and DSNTIJOS.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All users of the following explain tables in * * DB2 UDB for z/OS Version 8 are affected by * * this change: * * - DSN_PTASK_TABLE * * - DSN_VIRTUAL_INDEX * * . * * All users of the following explain tables in * * DB2 Version 9.1 for z/OS are affected by * * this change: * * - DSN_PTASK_TABLE * * - DSN_VIRTUAL_INDEXES * * - DSN_PGROUP_TABLE * **************************************************************** * PROBLEM DESCRIPTION: One or more of the following symptoms * * occurs: * * . * * In DB2 V8 and V9 * * ---------------- * * DSN_PTASK_TABLE explain tables * * created by running DB2 for z/OS * * installation jobs DSNTIJOS, * * DSNTIJOM (DB2 Version 9 only), * * DSNTIJOC (DB2 Version 9 only), * * or sample DDL member DSNTESC cannot * * be used by the IBM Optimization * * Service Center (OSC) for DB2 for * * z/OS because OSC reports that they * * have the wrong number of columns. * * . * * In DB2 V8 only * * -------------- * * Customers who use the DSNHDECP * * option DECIMAL=COMMA can encounter * * SQLCODE -104 when using DSNTIJOS * * or DSNTESC to create the * * DSN_VIRTUAL_INDEX explain table. * * . * * In DB2 V9 only * * -------------- * * (1) Customers who use the DSNHDECP * * option DECIMAL=COMMA can encounter * * SQLCODE -104 when using DSNTIJOS * * or DSNTESC to create the * * DSN_VIRTUAL_INDEXES explain table. * * (2) DSN_PGROUP_TABLE explain tables * * created by running jobs DSNTIJOS, * * DSNTIJOM, DSNTIJOC or sample DDL * * member DSNTESC cannot be used by * * OSC because the PLANNAME field * * is CHAR(8) instead of VARCHAR(24) * * (3) Sample DDL member DSNTESC does not * * show how to migrate the following * * sample explain tables to V9: * * - DSN_PREDICAT_TABLE * * - DSN_STRUCT_TABLE * * - DSN_PGROUP_TABLE * * - DSN_PTASK_TABLE * * - DSN_FILTER_TABLE * * - DSN_DETCOST_TABLE * * - DSN_SORT_TABLE * * - DSN_SORTKEY_TABLE * * - DSN_PGRANGE_TABLE * * - DSN_VIEWREF_TABLE * * - DSN_QUERY_TABLE * * - DSN_VIRTUAL_INDEXES * * (4) The sample LE environment options * * data set created by job DSNTIJOS * * for the WLM environment used to * * execute OSC Java stored procedures * * uses an incorrect HFS path separa- * * tor of semicolon (;) * * (5) The sample LE environment options * * data set created by job DSNTIJOM * * for the WLM environment used to * * execute OSC Java stored procedures * * does not include the HFS path for * * SYSPROC.EXPLAIN_SQL * **************************************************************** * RECOMMENDATION: * **************************************************************** DB2 for z/OS provides a number of explain tables for use in evaluating performance. Among these are DSN_PTASK_TABLE, DSN_PGROUP_TABLE and DSN_VIRTUAL_INDEXES. In DB2 Version 8, this latter table is also known as DSN_VIRTUAL_INDEX. Sample member DSNTESC contains DDL that shows how to create and migrate sample versions of these and other DB2 explain tables. . DSN_PTASK_TABLE =============== The DSN_PTASK_TABLE explain table is used to record parallel task activity in DB2 for z/OS. In DB2 V8 and V9, DSN_PTASK_TABLEs can be created via the IBM Optimization Service Center (OSC) for DB2 for z/OS, or by running DB2 installation job DSNTIJOS, or by processing the DDL in sample member DSNTESC. In DB2 V9 only, DSN_PTASK_TABLEs can also be created by running DB2 V9 migration job DSNTIJOM or conversion job DSNTIJOC. . The current format of the DSN_PTASK_TABLE includes four columns with names that contain the hashmark symbol (#). These columns are: LPTLOPG#, LPTHIPG#, LPTLOPT#, and LPTHIPT#. In the EBCDIC encoding scheme, the hashmark is a variant character, meaning that it does not reside at a consistent code point across all code pages. . The DDL provided in DSNTIJOS, DSNTIJOM, DSNTIJOC, and DSNTESC uses code point 7B for the hashmark. This DDL can be processed successfully when DB2 uses a default EBCDIC in which code point 7B is not the hashmark. However, the resulting DB2 catalog definition for DSN_PTASK_TABLE will not have the format expected by OSC, and OSC will then flag the DSN_PTASK_TABLE as having the wrong number of columns. DSN_PTASK_TABLEs created using the OSC client are not exposed to this problem. . DSN_PGROUP_TABLE ================ The DSN_PGROUP_TABLE explain table is used to record information about the parallel groups in a query. The PLANNAME column of this table contains the application plan name of the query. Currently, the DDL provided in V9 DSNTIJOS, DSNTIJOM, DSNTIJOC, and DSNTESC creates DSN_PGROUP_TABLE with the PLANNAME column as CHAR(8) rather than VARCHAR(24) as expected by OSC. The OSC client creates V9 DSN_PGROUP_TABLEs with the PLANNAME column as VARCHAR(24). . DSN_VIRTUAL_INDEX(ES) ===================== This table is used by optimization tools to test the effect of creating and dropping indexes on the performance of particular queries. Currently, the DDL in DSNTIJOS and DSNTESC for creating this table includes a CHECK clause with an IN option that contains numeric constants separated only by the comma character. This causes the statement to fail with SQLCODE -104 when it is processed on a DB2 that uses the DSNHDECP option DECIMAL=COMMA, because it is interpreted as a decimal number rather than a short chain of integers. . MIGRATION OF SAMPLE EXPLAIN TABLES ================================== As noted above, sample member DSNTESC contains DDL that shows how to create sample DB2 explain tables. Additional DDL in DSNTESC shows how to migrate sample explain tables from the previous release of DB2 to the current release. Currently, the V9 DSNTESC member is missing the DDL for migrating the following sample explain tables from V8 to V9: - DSN_PREDICAT_TABLE - DSN_STRUCT_TABLE - DSN_PGROUP_TABLE - DSN_PTASK_TABLE - DSN_FILTER_TABLE - DSN_DETCOST_TABLE - DSN_SORT_TABLE - DSN_SORTKEY_TABLE - DSN_PGRANGE_TABLE - DSN_VIEWREF_TABLE - DSN_QUERY_TABLE - DSN_VIRTUAL_INDEXES . SAMPLE OSC LE ENVIRONMENT OPTIONS DATA SET ========================================== DSNTIJOS and DSNTIJOM create a sample LE environment options data set for the WLM environment used to execute OSC Java stored procedures. Currently, the data set provided by DSNTIJOS uses the wrong separator character (the semicolon) to separate the HFS paths. The data set created by DSNTIJOM does not include the HFS path for the SYSPROC.EXPLAIN_SQL stored procedure. .
Problem conclusion
In response, the following changes are made: . The format of DSN_PTASK_TABLE in V8 and V9 is modified to ensure that all column name consist entirely of invariant characters. This change affect the following columns in DSN_PTASK_TABLE as indicated: Old name -> New name -------- -------- LPTLOPG# -> LPTLOPG LPTHIPG# -> LPTHIPG LPTLOPT# -> LPTLOPT LPTHIPT# -> LPTHIPT . Installation jobs DSNTIJOS, DSNTIJOM (V9 only), DSNTIJOC (V9 only), sample DDL member DSNTESC, and the OSC installation process, are all modified to create DSN_PTASK_TABLE with the new column names. . In V9 only, these same parts are modified to create DSN_PGROUP_TABLE with a PLANNAME column of VARCHAR(24) rather than CHAR(8). . In V8 and V9, DSNTIJOS and DSNTESC are modified to prevent the CREATE TABLE statement for DSN_VIRTUAL_INDEX(ES) from failing when DB2 uses the DSNHDECP option DECIMAL=COMMA. . In V9 only, DDL is added to DSNTESC so that it shows how to migrate all sample explain tables from V8 to V9. . The V9 DSNTIJOC is modified to facilitate converting DSN_PTASK_TABLEs and DSN_PGROUP_TABLEs provided by DSNTIJOS to the new format. Job DSNTIJOC is added to V8 to facilitate converting DSN_PTASK_TABLEs. . DB2 and OSC (V1R1 fixpack 6 and later) recognize DSN_PTASK_TABLE in both the old and the new format. However, the old format is deprecated, and support for it will be discontinued in DB2 by a future APAR, PK70423, and in OSC by a future version, release, or fixpack. . Therefore, two further reports are added to the DB2 Version 9.1 premigration checkout jobs DSNTIJPM (V9 only) and DSNTIJP9 (V8 only) to facilitate migration of DSN_PTASK_TABLEs to V9: - The first new report (REPORT11) identifies old-format DSN_PTASK_TABLEs that need to be converted prior to migration if you have applied the fix for PK70423 to DB2 Version 9.1. - The second new report (REPORT12) identifies new-format DSN_PTASK_TABLEs and reminds you that you need to apply the fix for this APAR prior to migration to DB2 Version 9.1. . IMPORTANT: If you use OSC then, before applying the fix for this APAR, you need to upgrade OSC V1R1 fixpack 6 (APAR PK75366). . After applying the fix for this APAR, and prior to applying the fix for PK70423, you need to identify existing DSN_PTASK_TABLEs and DSN_PGROUP_TABLEs and migrate them to the new formats. . OSC/OE customers migrating to OSC V1R1 fixpack 6 or OE V2R1 need to use job DSNTIJOC to convert the following tables after applying this PTF: Table name DSNTIJOC job step(s) --------------------------------- -------------------- DB2OSC.DSN_PTASK_TABLE DSNT2xx DB2OSC.DSN_PGROUP_TABLE DSNTALT SYSIBM.DSN_PTASK_TABLE (V9 only) DSNT1xx SYSIBM.DSN_PGROUP_TABLE (V9 only) DSNTALT See the ++HOLD actions for further guidance. . In V9 only, the sample LE environment options data set created by job DSNTIJOS for the WLM environment used to execute OSC Java stored procedures is corrected to use the colon (:) rather than the semicolon (;) as the HFS path separator character. . In V9 only, the sample LE environment options data set created by job DSNTIJOM for the WLM environment used to execute OSC Java stored procedures is modified to include the HFS path for the SYSPROC.EXPLAIN_SQL stored procedure. .
Temporary fix
Comments
APAR Information
APAR number
PK65772
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-05-08
Closed date
2008-12-01
Last modified date
2009-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK42083 UK42084
Modules/Macros
DSNTESC DSNTIJOC DSNTIJOM DSNTIJOS DSNTIJPM DSNTIJP9 DSNTINS1 DSNTINS2 DSNTINS3 DSNXOD0
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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 January 2009