PI55555: DSN_STATEMNT_TABLE PROCMS AND PROCSU VALUES FOR EXPLAIN OF CGTT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Very large PROCMS and PROCSU values are found in the
  DSN_STATEMNT_TABLE with the explain of a query referencing a
  CGTT.
  
  Additional Keywords:
  SQLCGTT
  DB2RLF
  DB2INCORR/K
  DB2OVRLAY/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the  *
  *                 DSN_STATEMNT_TABLE EXPLAIN Table.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: Very large and inaccurate values are    *
  *                      found for columns PROCMS and PROCSU in  *
  *                      the DSN_STATEMNT_TABLE resulting from   *
  *                      the EXPLAIN of a query referencing a    *
  *                      created global temporary table (CGTT).  *
  *                      These values can lead to an unexpected  *
  *                      SQLCODE495 when using the Resource      *
  *                      Limit Facility (RLF).                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Very large (and inaccurate) values are computed for the columns
  PROCMS and PROCSU in the DSN_STATEMNT_TABLE with the EXPLAIN of
  a query referencing a CGTT.  These large values can lead to an
  SQLCODE495 (SQLCODE -495) when using RLF or an overlay of DB2
  storage.
  
  
  The following simple example helps to illustrate the problem.
  
  STEP 1. Create a Global Temporary Table.
  
    CREATE GLOBAL TEMPORARY TABLE CGTT1 (
       TEXT CHAR(133) ) ;
  
  
  STEP 2. Populate Table CGTT1.
  
     INSERT INTO CGTT1 VALUES(
       CHAR('               ',133) );
  
  
  STEP 3. Explain the statement which selects from CGTT1.
  
      EXPLAIN ALL SET QUERYNO=61218 FOR
      SELECT * FROM CGTT1 ;
  
  
  STEP 4. Check the results from the EXPLAIN for the DSN_STATEMNT
  Table.
  
    SELECT * FROM DSN_STATEMNT_TABLE;
  
  
  In DB2 10, for the above query, the values for columns
  PROCMS and PROCSU are as follows.
  
    ------------------------------------
     |     PROCMS     |     PROCSU     |
    ------------------------------------
     |             15 |            39  |
    ------------------------------------
  
  
  In DB2 11, for the same query, the values for
  PROCMS and PROCSU are more alarming, they are.
  
    ------------------------------------
     |     PROCMS     |     PROCSU     |
    ------------------------------------
     |       32312512 |       85032960 |
    ------------------------------------
  
  
  The expected values for both DB2 10 and DB2 11 are as follows.
  
    ------------------------------------
     |     PROCMS     |     PROCSU     |
    ------------------------------------
     |              1 |              1 |
    ------------------------------------
  
  
  However, in some cases, the values can be even more skewed.
  For example:
  
    ------------------------------------
     |     PROCMS     |     PROCSU     |
    ------------------------------------
     |        6887669 |      255098880 |
    ------------------------------------
  
  when they should be this.
  
    ------------------------------------
     |     PROCMS     |     PROCSU     |
    ------------------------------------
     |              1 |              2 |
    ------------------------------------
  

Problem conclusion

• The code in DB2 is modified to compute the proper values for the
  PROCMS and PROCSU columns which represent the number of rows
  being inserted into the CGTT.  This will prevent the SQLCODE495,
  the large inaccurate reported values, and the possible storage
  overlays from occurring.
  
  Additional Keywords: SQLCODE495 SQLCGTT DB2RLF
             DB2INCORR/K DB2OVRLAY/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI35633 UI35634

Modules/Macros

• DSNXRIGT DSNXRIG9 DSNXRINS DSNXRIN9 DSNXRIWS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI35633

     UP16/03/11 P F603 Ž

• RB10 PSY UI35634

     UP16/03/11 P F603 Ž

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.