PI48283: NO DYNAMIC STATEMENT CACHE HIT FOR SQL STATEMENTS USING TEMPORAL TABLES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQL statements may incur dynamic statement cache ( DSC ) misses
  when a table that is referenced in the query is using a
  "temporal construct". (The "temporal construct" can be related
  a table that uses system time (SYSTEM_TIME), business time
  (BUSINESS_TIME), or uses archive tables (ENABLE ARCHIVE)).
  As a result the statement has to go through full prepare each
  time it is executed and a new copy of the stmt is inserted
  into the DSC (resulting in multiple copies of the SQL stmt with
  the same characteristics in the DSC).
  The SQL itself does not need to have a clause with a temporal
  construct for the problem to occur.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of temporal        *
  *                 tables in combination with the Dynamic       *
  *                 Statement Cache (CACHEDYN=YES).              *
  ****************************************************************
  * PROBLEM DESCRIPTION: Users may experience high CPU           *
  *                      utilization attributed to               *
  *                      long prepares and duplicate             *
  *                      statements in the Dynamic               *
  *                      Statement Cache which increase          *
  *                      the EDM pool storage used.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Dynamic Statement Cache matching criteria was incorrect
  resulting in no cache match for statements that:
  1. Reference  a Business Time Temporal table; and do not
     have the BUSTIMESENSITIVE bind option specified on the
     package; and do not have the CURRENT TEMPORAL
     BUSINESS_TIME special register value set; Or
  2. Reference a System Time Temporal table; and do not
     have the SYSTIMESENSITIVE bind option specified on the
     package; and do not have the CURRENT TEMPORAL
     SYSTEM_TIME special register value set; Or
  3. Reference an archive enabled table; and do not
     have the ARCHIVESENSITIVE bind option specified on the
     package; and have the SYSIBMADM.GET_ARCHIVE global
     variable set to 'N';
  This causes those statements to incorrectly not match
  a copy in the dynamic statement cache and go through full
  prepares and insert a new copy into the Dynamic Statement
  Cache.
  

Problem conclusion

• Dynamic Statement Cache matching criteria is modified so
  statements that:
  1. Reference  a Business Time Temporal table; and do not
     have the BUSTIMESENSITIVE bind option specified on the
     package; and do not have the CURRENT TEMPORAL
     BUSINESS_TIME special register value set; Or
  2. Reference a System Time Temporal table; and do not
     have the SYSTIMESENSITIVE bind option specified on the
     package; and do not have the CURRENT TEMPORAL
     SYSTEM_TIME special register value set; Or
  3. Reference an archive enabled table; and do not
     have the ARCHIVESENSITIVE bind option specified on the
     package; and have the SYSIBMADM.GET_ARCHIVE global
     variable set to 'N';
  will now match a correct copy if it exists in the
  Dynamic Statement Cache.
  
  Additional Keywords:
  SQLDYNSTMTCACHE SQLAPTT SQLBITEMPORAL SQLSPTT
  

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:

  UI33566

Modules/Macros

• DSNGEPIS DSNGEPLC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI33566

     UP15/12/22 P F512

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.