DB2 z/OS v8 DROP INDEX inhibited

Kirk Hampton

DB2 z/OS v8 DROP INDEX inhibited
Greetings all,

Long time since I posted last...



I am RTFM-ing for the reason behind this issue we had today, and I
thought I would drop this on the group as well and see if someone can
'splain it to me.



We have a simple tablespace with one index, no RI. The DBA was
attempting to DROP and recreate the index using IBM Admin Tool. I did
not see the message he got from Admin tool, but a code 00E70081 with
type A00 on the underlying table was logged in the ssidMSTR region when
he did it:



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.





We have had dynamic statement cache enabled for 8-10 years and I have
never before encountered any DDL being inhibited. There were no
outstanding INDOUBT threads. This is a testing environment, so I would
not put anything past them, but I did check for that. The only other
active thread was a QMF user accessing unrelated tables. The DBA was
ultimately able to drop the index after doing a STOP/START on the
tablespace. I was going to suggest he run a RUNSTATS with UPDATE NONE to
clear the cache, but he was ahead of me with the STOP. I'm suspecting
someone may have run some dynamic SQL other than a SELECT, that was
cached, but I'm stumped about the "in use by an application" bit, since
a DIS USERS on the tablespace showed nothing.



Anyone have a clue on the reason for this? I'm afraid any other
pertinent evidence may already be gone...



Thanks,



J Kirk Hampton

Sr. Specialist - Mainframe

HCL Technologies America

Mesquite Data Center

972-216-3119




Confidentiality Notice: This email message, including any attachments,
contains or may contain confidential information intended only for the
addressee. If you are not an intended recipient of this message, be
advised that any reading, dissemination, forwarding, printing, copying
or other use of this message or its attachments is strictly prohibited. If
you have received this message in error, please notify the sender
immediately by reply message and delete this email message and any
attachments from your system.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Todd Burch

Re: DB2 z/OS v8 DROP INDEX inhibited
(in response to Kirk Hampton)
Hi J Kirk.

We (Level 2 support) see this occasionally. Sometimes it's normal
behavior, and sometimes not.

What is happening is that the statement use-count in the DSC (dynamic
statement cache) is not at zero for an SQL statement that references
(depends on) the object attempting to be altered (or dropped).

We have a few available APARS that address this situation. One APAR
in particular, PK92910, closed a timing hole, where, if a thread's use
of cached SQL had bumped the use-count, and then that thread went
through abort processing, we forgot to decrement the use-count.

It's common for people to suggest that RUNSTATS be run to clear the
cache and circumvent this (essential) lock-out. However, if a
statement's use count is not zero, that cache entry will not be
cleared by running RUNSTATS. Therefore, for a hard use-count problem,
RUNSTATS won't fix it.

Todd



On Nov 17, 2010, at 4:32 PM, Kirk Hampton wrote:

Greetings all,
Long time since I posted last…

I am RTFM-ing for the reason behind this issue we had today, and I
thought I would drop this on the group as well and see if someone can
‘splain it to me.

We have a simple tablespace with one index, no RI. The DBA was
attempting to DROP and recreate the index using IBM Admin Tool. I did
not see the message he got from Admin tool, but a code 00E70081 with
type A00 on the underlying table was logged in the ssidMSTR region
when he did it:

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.

We have had dynamic statement cache enabled for 8-10 years and I have
never before encountered any DDL being inhibited. There were no
outstanding INDOUBT threads. This is a testing environment, so I would
not put anything past them, but I did check for that. The only other
active thread was a QMF user accessing unrelated tables. The DBA was
ultimately able to drop the index after doing a STOP/START on the
tablespace. I was going to suggest he run a RUNSTATS with UPDATE NONE
to clear the cache, but he was ahead of me with the STOP. I’m
suspecting someone may have run some dynamic SQL other than a SELECT,
that was cached, but I’m stumped about the “in use by an application”
bit, since a DIS USERS on the tablespace showed nothing.

Anyone have a clue on the reason for this? I’m afraid any other
pertinent evidence may already be gone…

Thanks,

J Kirk Hampton
Sr. Specialist - Mainframe
HCL Technologies America
Mesquite Data Center
972-216-3119

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv