Start of change

Collecting data to find a statement in the dynamic statement cache that caused another statement to fail with reason code 00E70081

A data definition statement can fail because a statement in the dynamic statement cache is also using an object that the data definition statement is modifying. You can collect trace data to determine the cached statement that is causing the problem.

When the problem occurs, Db2 issues SQLCODE -904 with reason code 00E70081, resource type 00001202 (dynamic statement cache), and resource name statement-ID or member-name.statement-ID. statement-ID is the statement identifier for the prepared statement in the dynamic statement cache. member-name is the data sharing member name, which is included when the application is running in a data sharing environment.

Restriction: The EXPLAIN STMTCACHE ALL statement does not provide the information that you need to find the cached statement.

You can use instrumentation facility interface (IFI) READS calls, or you can use the DSN1SDMP utility to determine which cached statement is in use.

Using IFI READS calls to obtain information about the cached statement that causes SQLCODE -904 with reason code 00E70081

You can use an application or tool that does IFI READS calls for IFCID 0316 to do real-time monitoring of statements in the dynamic statement cache. Using a monitoring application or tool is the better option, especially if cached statements that are causing SQLCODE -904 are in the cache for a short time. The output from the monitoring application or tool is a record for each SQL statement in the dynamic statement cache. Those records contain information such as the statement identifier and the number of users that are using the statement. For a complete description of the fields in an IFCID 0316 record, see member DSNWMSGS in data set prefix.SDSNIVPD.

You can write a monitoring application, or you can use a monitoring tool, such as Tivoli® OMEGAMON® XE for Db2 Performance Expert on z/OS® .

Using DSN1SDMP to obtain information about the cached statement that causes SQLCODE -904 with reason code 00E70081

Under the guidance of IBM® Support, you can run the DSN1SDMP utility to generate a dump when SQLCODE -904 with reason code 00E70081 occurs.

The following example uses DSN1SDMP to generate a dump when SQLCODE -904 with reason code 00E70081 occurs.

//job-statement
//*
//STEP1    EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SDMPPRNT DD SYSOUT=*
//SDMPIN DD *
* START A TRACE TO COLLECT INFORMATION FOR THE DSN1SDMP UTILITY.
START TRACE=P CLASS(32) IFCID(340) DEST(SMF) TDATA(TRA) 
* ABEND AND TERMINATE WITH REASON CODE 00E60194
* AFTER THE SELECTION CRITERIA ARE MET ONCE.
AFTER(1) 
FOR(1) 
ACTION(ABENDTER(00E60194))
* SELECTION CRITERIA FOLLOW. 
SELECT P4,00 
* COMPARE IFCID TO 340 
IFCID DR,04,X'0154' 
* POSITION TO SECOND SECTION (1ST DATA SECTION) 
P4,08 
* COMPARE QW0340_MODNAME to DSNXIDMH (MODULE IN WHICH SQL ERROR OCCURS)
DR,6,C'DSNXIDMH' 
* COMPARE SQLCODE TO -904 
DR,16,X'FFFFFC78' 
/*
//SYSUDUMP DD   SYSOUT=*
//SYSTSIN  DD   *
DSN SYSTEM(DB2A)
RUN PROG(DSN1SDMP) PLAN(DSNEDCL)
/*
//

In a data sharing environment, the -904 error might occur in an application on a member other than the one on which you run DSN1SDMP. Therefore, you also need to set a SLIP trap to create dumps on all other members of the data sharing group that are not quiesced. The following example shows how to do that.

Suppose that data sharing group DBP_PROD has four members: DBPA, DBPB, DBPC, and DBPD. Issue the following SLIP SET command to trigger a dump of all members of the data sharing group when abend 04E with reason code 00E60194 occurs.

SLIP SET,ID=IDMH,C=04E,DATA=(15R,EQ,00E60194),ACTION=SVCD,
JOBLIST=(XCFAS,DBP*IRLM,DBP*MSTR,DBP*DBM1),
SDATA=(XESDATA,COUPLE,PSA,LSQA,LPA,GRSQ,SWA,RGN,CSA,SQA,SUM,TRT,ALLNUC),
ML=1,REMOTE=(JOBLIST,SDATA,DSPNAME),END
End of change