PM46707: ADD A NEW SUBSYSTEM PARAMETER (ZPARM) AS TIME OUT FACTOR FOR DDL AND DCL WHEN USING DYNAMIC STATEMENT CACHING

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• During the processing of a DDL statement, DB2 uses the standard
  IRLM time out value for obtaining locks. This limits user
  flexibility on controlling how long DDL statements will wait for
  a resource before timing out.
  To allow user more control on the wait time for a resource
  during the processing of a DDL statement before a time out is
  encountered, we would like to introduce a separate time out
  factor for DDL and DCL (GRANT, REVOKE, and LOCK) statements.
  New subsystem parameter (ZPARM) will be added in order to
  influence the timeout value.
  The actual time out value will be the product of new parameter
  and the standard IRLM time out value that is in effect as
  specified by IRLMRWT.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 and 10 z/OS users of dynamic statement *
  *                 caching and lock timeout.                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: ALTER TABLE ROTATE PARTITION does not   *
  *                      wait for the expected amount of time    *
  *                      and receives SQLCODE904 RC00E70081.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a DDL statement is run concurrently with DML statements and
  dynamic statement caching is in effect, the DDL statement does
  not honor the timeout multiplier value set in DDLTOX if the
  value is set to a valid number higher than 1, indicating a
  longer wait for the resource. Users may see SQLCODE -904
  earlier than expected for DDL statements.
  

Problem conclusion

Temporary fix

Comments

• The timeout factor for DDL statements has been taken into
  account when they are run concurrently with DML statements and
  dynamic statement caching is on, when users specify a valid
  value higher than 1 for ZPARM SPRMDDLTO.
  
  Additional Keywords:
  SQLCODE904
  SQLALTER
  SQLCREATE
  SQLDROP
  SQLDYNSTMTCACHE
  

APAR Information

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

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

  UK77336 UK77337

Modules/Macros

• DSNXEIST
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK77336

     UP12/04/10 P F204

• R910 PSY UK77337

     UP12/04/10 P F204

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.