PQ56332: SQLCODE904 RC00C9008F FOR DECLARE GLOBAL TEMP TABLE SQLCODE497 SQLCODE 904 -497 TEMPORARY. FORWARD FIT OF PQ37880.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Forward Fit of PQ37880 to DB2 V7. R710.
  SQLCODE904 RC00C9008F FOR DECLARE GLOBAL TEMP TABLE
  SQLCODE497 SQLCODE 904 -497 TEMPORARY erqual5011
  Additional symptoms RC00C90101 DSNISEGR vrace5011
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of Declared Global Temporary   *
  *                 Tables or scrollable cursors.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -497.                           *
  *                                                              *
  *                      DECLARE GLOBAL TEMPORARY TABLE results  *
  *                      in SQLCODE -904, Reason 00C9008F,       *
  *                      Resource 00000100, indicating that DBD  *
  *                      is too large.                           *
  *                                                              *
  *                      ABEND04E RC00C90110,                    *
  *                      LOC=DSNGD001.DSNGDVO1:5001              *
  *                                                              *
  *                      ABEND0C4 RC00000004,                    *
  *                      LOC=DSNGD001.DSNGDDRT+18EC              *
  *                                                              *
  *                      ABEND04E RC00C90110,                    *
  *                      LOC=DSNGD001.DSNGDDRT:500C              *
  *                                                              *
  *                      ABEND04E RC00C90101,                    *
  *                      LOC=DSNGEDM.DSNGEDLC:5009               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  There was a 24x7 restriction on usage of declared temporary
  tables in a thread reuse situation where the user does not
  explicitly drop the temp tables before commit.  In this case,
  DBD storage in the EDM pool would not be freed, growing in
  storage as more temp tables are defined.  Eventually, the DBD
  size exceeds the maximum of 25% of the EDM pool, resulting in
  SQLCODE 904, Reason 00C9008F, Resource 00000100.
  

Problem conclusion

• The fixes in APAR PQ37880 (the V6 APAR to address the same
  problem) have been forward fit to DB2 V7, so that DB2 does not
  store the OBDs for the temp tables in the DBD.  Instead, the
  temp table OBDs are now stored in the ADMF AGENT Local Pool, and
  the OBD storage for dropped temp tables is now freed at COMMIT.
  Therefore, the 25% limit for DBD storage is not encountered.
  
  Similar changes have also been made for internal temp tables
  that are created for scrollable cursors.
  
  This APAR also includes forward fits for PQ58644 and PQ60665.
  Please refer to those two APARs for more information.
  
  In addition, the installation parameter (zparm) SPRMLTD now no
  longer applies and is not used after PQ56332.  SPRMLTD was
  originally used to determine a size threshold for compression of
  the in-memory only copy of the Database Descriptor (DBD) for the
  TEMP database when the database is no longer in use, as a way of
  relieving continued DBD growth.  With PQ56332, the DBD does not
  continue to grow so SPRMLTD and compression no longer apply and
  are no longer used.
  
  ADDITIONAL RECOMMENDATIONS:
  
  1. If you have large temp tables, you might want to consider
     creating few large temp tablespaces vs. creating many smaller
     temp tablespaces.  If you have many small temp tables, it is
     better to create many smaller temp tablespaces to increase
     I/O efficiency.
  2. It is a good programming practice to always drop a temp table
     prior to COMMIT, if it is no longer needed.
  
  ADDITIONAL KEYWORDS:
  
  SQLCODE497 SQLCODE904 DGTT
  DSNGEDLC ERQUAL5009
  

Temporary fix

Comments

APAR Information

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

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

  UQ68803

Modules/Macros

•    DSNDDSI  DSNDDT   DSNDIFC  DSNDOBD  DSNGDBRT
  DSNGDCLN DSNGDCMT DSNGDCOB DSNGDCOS DSNGDDIX DSNGDDRT DSNGDIE1
  DSNGDLOG DSNGROU2 DSNICINV DSNICMT2 DSNIRTSX DSNISEGD DSNTAC1
  DSNTADL  DSNXDTTL DSNXECW  DSNXECWA DSNXECWC DSNXECWU DSNXEINV
  DSNXEPLG DSNXESCL DSNXESTR DSNXEUF0 DSNXEWS  DSNXEWST DSNXICIX
  DSNXICTB DSNXICVT DSNXICX2 DSNXIDCL DSNXIDIX DSNXIDTB DSNXIDTS
  DSNXIDTT DSNXISCR DSNXIXPM DSNXMIT2 DSNXRDA  HDB7710J
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R710 PSY UQ68803

     UP02/08/16 P F208

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.