PK64783: APPLICATION FAILED WITH -904 SQLCODE AND REASON CODE 00C90096

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Action taken/plan:
  Application failed with SQLCODE -904 and DB2 reason code
  00C90096 due to X-Locks mistakenly acquired by index manager and
  were notreleased properly. This is a V9 only problem.
  

Local fix

• Enable Lock escalation on this tableSpace by "Alter TableSpace"
  and set its LockMax to a non-zero integer. This will allow the
  lock escalation to happen and let the application completes its
  work, instead failing with SQLCODE of -904 and Db2 reason code
  00C90096.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All db2 v9 user when running a               *
  *                 UPDATE/DELETE/MERGE statement with target    *
  *                 table is accessed via index.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: When LOCKSIZE is not table space,       *
  *                      UPDATE/DELETE/MERGE statement(s) may be *
  *                      failed with SQLCODE -904 if target      *
  *                      table is accessed with index and there  *
  *                      is residual predicates existed.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When LOCKSIZE is not table space, UPDATE/DELETE/MERGE
  statement(s) may be failed with SQLCODE -904 if target table is
  accessed with index and there is residual predicates existed.
  
  The following query is an example of a failing case.
  
  CREATE INDEX I1 ON T1(C2);
  
  UPDATE T1 SET C1=1
  WHERE C2=2 AND
        C3 IN (SELECT * FROM T2);
  

Problem conclusion

• The bindtime code in DB2 is modified to correct the failing
  condition.
  
  Additional keyword: SQLLOCK SQLUPDATE SQLDELETE
  

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:

  UK36579

Modules/Macros

• DSNXOMS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK36579

     UP08/06/04 P F806

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.