Tuesday, May 8, 2012

UNAVAILABLE RESOURCE REASON 00E70081

Hi,

Here I come again!!!

Problem:

You want to drop or alter a Table. But you are getting "DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E70081, TYPE OF RESOURCE 00000A00, AND RESOURCE NAME

Let's see what is reason code: 00E70081

Explanation: A DROP or ALTER statement was issued but the object cannot be dropped or altered. The object is referenced by a prepared dynamic SQL statement that is currently stored in the prepared statement cache and is in use by an application.

System action: The requested operation is not performed.

Programmer response: The requested operation cannot be performed until there are no applications that reference the object using dynamic SQL. Ensure that other applications have quiesced or performed a commit operation. Then try the request again.

Problem determination: An SQLCODE -904 is issued. The message tokens for that message report the name and type of the object that was being referenced by another application.

Solution:

On quering the resource type:

TYPE Code            Type of Resource       Name, Content,Format

00000A00             Table                  RD.CR.TB

where
RD is a DB2 Release dependency mark


CR the object creator and

TB the Table name
 
 
Runstat utility will be handy to resolve this problem. Runstat with option "update none report none" will clear the dynamic statement cache so that the resource is released to be used by the other application.

Here is the control statement you need to use:

//DSNUPROC.SYSIN DD *

RUNSTATS TABLESPACE . TABLE ALL INDEX ALL

UPDATE NONE REPORT NO



Regards,
Prakash C Singh
IBM Certified DBA.


1 comment:

Raja Rao Paritala said...

this blog is very usefull, keep posting