Drop Tablespace Makes IRLM Run out of memory

Ramachandran Subramanian

Drop Tablespace Makes IRLM Run out of memory
Hi,

One of my customer DBAs issued a DROP TABLESPACE on a PARTITIONED
TABLESPACE as he wanted to drop the table and the tablespace. The
tablespace has over 2 million pages. It has LOCKMAX as 0

For a Partitioned Tablespace, one cannot drop a partitioned table with
the DROP TABLE statement. A drop TABLESPACE was issued insteAD


When the command was issued , it returned error -904 reason 00c90092.

The IRLM has a region size of 5000K. The customer does not want to
change that.


CORRELATION-ID=QCPD0062
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90092
TYPE 00000905
NAME IRLM


My question is, Does DB2 try to acquire a lock for every single page
before it can drop the the tablespace?

Can some one shed some light on this?

I am thinking of doing a

LOCK TABLESPACE XXXX IN EXCLUSIVE MODE;
DROP TABLESPACE XXXX;



Ram
www.db2-dba.net

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ramachandran Subramanian

Re: Drop Tablespace Makes IRLM Run out of memory
(in response to Phil Grainger)
Sir,

when I scan the MSTR , I see a lot of lock escalations for other
transactions around this time , but nothing matches the exact timestamp.
The lock escaltions are 5-10 minutes before and after.

When I also looked (using INSIGHT FOR DB2) at the number of Lock requests
done by the thread that ran the drop tablespace, I see 32,000 + lock
requests!!!

ram
www.db2-dba.net

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Drop Tablespace Makes IRLM Run out of memory
(in response to Ramachandran Subramanian)
Nope

When you drop a tablespace you are just dripping DB2 objects - no locks
are taken on the data

HOWEVER, Db2 does take locks when deleting the dependent objects, so
when you drop a tablespace, Db2 will take locks for

- deleting the tablespace image copies from SYSCOPY
- dropping all of the indexes defined on the table
- deleting all the index image copies (if any) from SYSCOPY
- dropping all of the views dependent on the table
- dropping all of the aliases/synonyms (if any) on the table
- invalidation all plans/packages dependent on the table (and view,
aliases, synonyms etc)
- revoking all authorities held on the table (and views & aliases)

You can see that the number of locks CAN start to mount up

You can alleviate the problem (somewhat) by doing some of the drops and
revokes (and modify recoveries) in advance, but I am still surprised you
can break IRLM by dropping a single tablespace

SO

This might not actually BE the cause of the IRLM problem.

Let's assume your IRLM has room for 10,000 locks. "Someone" (ie another
transaction) takes 9,999 locks. All is OK. You now take 1 lock - all is
STILL OK, but when you try and get another lock you get 00c90092, but it
wasn't YOU that used all the storage.

I know it is worrying, but trying the drop again (with a different mix
of transactions) may well work just fine (or it may not)


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ramachandran Subramanian
Sent: 12 December 2005 09:04
To: [login to unmask email]
Subject: [DB2-L] Drop Tablespace Makes IRLM Run out of memory

Hi,

One of my customer DBAs issued a DROP TABLESPACE on a PARTITIONED
TABLESPACE as he wanted to drop the table and the tablespace. The
tablespace has over 2 million pages. It has LOCKMAX as 0

For a Partitioned Tablespace, one cannot drop a partitioned table with
the DROP TABLE statement. A drop TABLESPACE was issued insteAD


When the command was issued , it returned error -904 reason 00c90092.

The IRLM has a region size of 5000K. The customer does not want to
change that.


CORRELATION-ID=QCPD0062
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90092
TYPE 00000905
NAME IRLM


My question is, Does DB2 try to acquire a lock for every single page
before it can drop the the tablespace?

Can some one shed some light on this?

I am thinking of doing a

LOCK TABLESPACE XXXX IN EXCLUSIVE MODE; DROP TABLESPACE XXXX;



Ram
www.db2-dba.net

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Drop Tablespace Makes IRLM Run out of memory
(in response to Ramachandran Subramanian)
Then it looks like either there are a LOT of related objects to the
tablespace being drop and/or your catalog is VERY disorganised

If you are seeing lock escalations though, it does point to other
transactions taking a lot of locks. Remember, an escalation occurs AFTER
a "lot" of locks are taken, so an escalation shortly AFTER your failure
may well have been holding on to many locks at the time if your 00c90092

Mind you, that 32,000+ lock requests would worry me too!

I would do the following

1. Free ALL plans/packages related to the table you are dropping 2. Use
MODIFY RECOVERY to delete ALL image copies for the tablespace (and any
indexes that are COPY YES)

Provided you don't have hundreds of indexes or aliases/synonyms or views
that should cut down the work of the drop significantly


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Ramachandran Subramanian [mailto:[login to unmask email]
Sent: 12 December 2005 11:41
To: [login to unmask email]; Grainger, Phil
Subject: Re: Drop Tablespace Makes IRLM Run out of memory

Sir,

when I scan the MSTR , I see a lot of lock escalations for other
transactions around this time , but nothing matches the exact timestamp.
The lock escaltions are 5-10 minutes before and after.

When I also looked (using INSIGHT FOR DB2) at the number of Lock
requests done by the thread that ran the drop tablespace, I see 32,000 +
lock requests!!!

ram
www.db2-dba.net

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm