DB2 FOR LUW / DB2FORLUW / A50 - DB2

1.  Problem description 
 
PL/SQL package receive -727 SQL error when "prepAnonBlock" EDU 
triggers a lock conflict and deadlock with the original EDU. 
 
For example, 
(1) There is two PL/SQL packages A and B. 
(2) Package B refers to package A in the definition. 
(3) Package A was re-compiled recently whereas package B is not. 
(4) An application with auto-commit disabled calls package A and 
then package B. 
(5) After finish calling package A, the application encounters 
the lock conflict and deadlock with "prepAnonBlock" EDU when 
calling package B. 
(6) Eventually the application terminates with SQL0727N error. 
 
 
2.  Diagnostic information 
 
(A) db2diag.log has the following message pattern. 
 
EDUID   : XXX                  EDUNAME: db2agent (DBNAME) 
FUNCTION: DB2 UDB, SW- common services, sqlnn_regen_function, 
probe:1 
DATA #1 : String, 0 bytes 
Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: 
Address is NULL 
DATA #2 : String, 0 bytes 
Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: 
Address is NULL 
... 
 
EDUID   : 3343                 EDUNAME: db2dlock (DBNAME) 
FUNCTION: DB2 UDB, base sys utilities, 
sqleCheckForceSuspendedConn, probe:280 
DATA #1 : String, 170 bytes 
Anonymous Block deadlock detected! 
Waiting application with appHandle 0-XXX will be rolled back. 
Suspended application with appHandle 0-XXX has deadlock state 
0x00000010. 
 
... 
EDUID   : XXX                  EDUNAME: db2agent (DBNAME) 
FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::update, 
probe:60 
RETCODE : ZRC=0x80040003=-2147221501=SQLD_INTRP "USER INTERRUPT 
DETECTED" 
          DIA8003C The interrupt  has been received. 
 
... 
EDUID   : XXX                  EDUNAME: db2agent (DBNAME) 
FUNCTION: DB2 UDB, access plan manager, 
sqlraProcessPrepAnonBlock, probe:314 
MESSAGE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from 
application" 
          DIA8003C The interrupt  has been received. 
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCAL    sqlcabc: 136   sqlcode: -727   sqlerrml: 13 
 
(B) Lock information 
 
  -  Lock waiter is the "prepAnonBlock" EDU requesting 'X' lock. 
     Lock holder is the original application and in the 
"Anonymous Block Wait" status.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 7                       * 
****************************************************************

Revalidate the invalid object referenced in the anonymous block 
and retry the statement.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

25.05.2015
28.01.2016
28.01.2016