PH09796: DSNT500I RC00E30083 TYPE 00000801 or SQLCODE904 RC00E70010 when a DDL or REBIND PACKAGE fails due to package lock contention

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR addresses a problem when a DDL or REBIND PACKAGE
  command can not complete successfully due to package
  lock contention.
  This problem is applicable to the packages with the
  RELEASE ( COMMIT ) option only.
  The following symptoms can be observed:
  - REBIND PACKAGE fails with message DSNT500I REASON 00E30083
    TYPE 00000801
  - A DDL statement fails with SQLCODE904 REASON 00E70010
    TYPE 00000801
  
  This APAR also addresses a possible infinite loop
  on DSNGEFLC 08/29/18 UI58169 AT OFFSET63C2 OFFSET63D8 when
  executing a package on one member of the datasharing
  environment, after a REBIND or DDL.
  Db2 has incorrect checking code when searching for the package.
  
  This APAR also contains preconditioning code for a future BIND
  enhancement
  
  Additional keywords: MSGDSNT500I RC00E30083 RC00E70010
                       SQLCREATE SQLALTER SQLDROP LOOP DSNGEFLC
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * This APAR changes the                                        *
  * PLANTABLE.HINT_USED column value                             *
  * returned on the EXPLAIN PACKAGE SQL                          *
  * statement. Prior to this APAR, the                           *
  * PLANTABLE.HINT_USED column is                                *
  * 'EXPLAIN PACKAGE: COPY 0' or                                 *
  * 'EXPLAIN PACKAGE: COPY 1' or                                 *
  * 'EXPLAIN PACKAGE: COPY 2' for a                              *
  * current, previous or original copy of                        *
  * the package respectively.                                    *
  *                                                              *
  * This APAR also changes the                                   *
  * PLANTABLE.HINT_USED column value                             *
  * returned on the EXPLAIN STABILIZED                           *
  * DYNAMIC SQL statement. Prior to this                         *
  * APAR, the PLANTABLE.HINT_USED column                         *
  * is "EXPLAIN SDQ: COPY 1" or                                  *
  * ""EXPLAIN SDQ: COPY 4" for a current                         *
  * or invalid copy of the stabilized                            *
  * dynamic statement.                                           *
  *                                                              *
  * This APAR also addresses a problem                           *
  * when a DDL or REBIND PACKAGE command                         *
  * can not complete successfully due to                         *
  * package lock contention. When a                              *
  * thread issues a COMMIT request, Db2                          *
  * can keep a package and its package                           *
  * lock is also held. An example is when                        *
  * the thread has opened a cursor with                          *
  * the WITH HOLD attribute on this                              *
  * package.  Then when the thread is                            *
  * reused for another user, Db2 releases                        *
  * the package without releasing the                            *
  * package lock. This can cause                                 *
  * unsuccessful DDL statement or REBIND                         *
  * of the same package later when these                         *
  * processes attempt to obtain the                              *
  * package lock in an incompatible                              *
  * state. This problem is applicable to                         *
  * the packages with the RELEASE(COMMIT)                        *
  * option only.                                                 *
  *                                                              *
  * This APAR also addresses a possible                          *
  * infinite loop when executing a                               *
  * package after a REBIND or DDL is                             *
  * executed on another member of the                            *
  * data sharing group. Db2 has incorrect                        *
  * checking code when searching for the                         *
  * package.                                                     *
  *                                                              *
  * This APAR also has the precondition                          *
  * code for a new function.                                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 code has been changed to set the PLANTABLE.HINT_USED
  column value on the EXPLAIN PACKAGE SQL statement to
  different strings. The PLANTABLE.HINT_USED column will
  be "EXPLAIN PACKAGE: CURRENT" or "EXPLAIN PACKAGE: PREVIOUS"
  or "EXPLAIN PACKAGE: ORIGINAL" for a current, previous or
  original copy of the package respectively.
  
  Similarly, on the EXPLAIN STABILIZED DYNAMIC SQL statement,
  Db2 sets the PLANTABLE.HINT_USED column to the string
  "EXPLAIN SDQ: CURRENT" or "EXPLAIN SDQ: INVALID" for a
  current or invalid copy of the stabilized dynamic statement
  respectively.
  
  Db2 code has also been changed to release the package lock
  when a package is released at the time the thread is reused
  for a new user.
  
  Db2 code has also been changed to remove the incorrect
  checking code when searching for a package to execute.
  
  This APAR also has the precondition code for a new function.
  

Problem conclusion

• Db2 code has been changed to set the PLANTABLE.HINT_USED
  column value on the EXPLAIN PACKAGE SQL statement to
  different strings. The PLANTABLE.HINT_USED column will
  be "EXPLAIN PACKAGE: CURRENT" or "EXPLAIN PACKAGE: PREVIOUS"
  or "EXPLAIN PACKAGE: ORIGINAL" for a current, previous or
  original copy of the package respectively.
  
  Similarly, on the EXPLAIN STABILIZED DYNAMIC SQL statement,
  Db2 sets the PLANTABLE.HINT_USED column to the string
  "EXPLAIN SDQ: CURRENT" or "EXPLAIN SDQ: INVALID" for a
  current or invalid copy of the stabilized dynamic statement
  respectively.
  
  Db2 code has also been changed to release the package lock
  when a package is released at the time the thread is reused
  for a new user.
  
  Db2 code has also been changed to remove the incorrect
  checking code when searching for a package to execute.
  

Temporary fix

Comments

• ×**** PE19/06/07 FIX IN ERROR. SEE APAR PH13112  FOR DESCRIPTION
  ×**** PE19/10/31 FIX IN ERROR. SEE APAR PH18731  FOR DESCRIPTION
  

APAR Information

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

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

  UI62576

Modules/Macros

• DSNTBFO2 DSNTBFO1 DSNUELTB DSNXIDPM DSNGERTS DSNTGEPL DSNTBRB2
  DSNGESFR DSNTLBAC DSNWVINT DSNXOD3  DSNGEPER DSNLXRSS DSNXEAAL
  DSNXECWU DSNGEPIS DSNXEPP  DSNXEPM  DSNXOIU1 DSNUEDRM DSNXECWA
  DSNDQWHS DSNDQW05 DSNXEBPG DSNXELX  DSNGENER DSNXECW  DSNXOD15
  DSNWAACT DSNGEDYI DSNGEFLC DSNUEDSP DSNTBSPM DSNGEDLC DSNGEDYC
  DSNGEPLC DSNTAAL  DSNGEPDL DSNGERBK DSNGEFSP DSNWDFSM DSNWCBDZ
  DSNGEINT DSNLTEXC DSNGEXLC DSNGEFBK DSNUECM5 DSNTBAB2 DSNXAAB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI62576

     UP19/05/07 P F905

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.