Betr.: [DB2-L] Drop Tablespace Makes IRLM Run out of memory

Hessel Rus

Betr.: [DB2-L] Drop Tablespace Makes IRLM Run out of memory
Since it's a PARTITONED tablespace, it's also a SIMPLE tablespace which
implies heavy logging and locking in case of delete (or drop).
Solutiuon: empty the tablespace first; issue a LOAD LOG NO with a dummy
inputdataset. With no rows in the table, dropping should result in minimal
overhead.

With kind regards
Hessel Rus
Sonepar Nederland Information Services




Ramachandran Subramanian <[login to unmask email]>
Verzonden door: DB2 Data Base Discussion List <[login to unmask email]>
12-12-2005 10:04
Antwoord a.u.b. aan
DB2 Database Discussion list at IDUG <[login to unmask email]>


Aan
[login to unmask email]
Cc

Onderwerp
[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



=======
The information contained in this email is confidential and privileged. It may be read, copied and used only by the intended recipient. If you have received it in error, please contact the sender immediately by return email; please delete in this case the email and do not disclose its contents to any person. We don't accept liability for any errors, omissions, delays of receipt or viruses in the contents of this message which arise as a result of email transmission.
=======


---------------------------------------------------------------------------------
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

Michael Ebert

Re: Drop Tablespace Makes IRLM Run out of memory
(in response to Hessel Rus)
DB2 doesn't do a DELETE first before dropping a TS. The number of rows in
a TS does not play a role.
As for the original IRLM problem, I'd suspect the DROP was just revealing
the symptoms of another hidden problem. Normally an IRLM should be able to
handle millions of locks, unless you run the proc with a low REGION parm.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany








Hessel Rus <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12-12-05 14:15

To
[login to unmask email]
cc



Subject
[DB2-L] Betr.: [DB2-L] Drop Tablespace Makes IRLM Run out of memory







Since it's a PARTITONED tablespace, it's also a SIMPLE tablespace which
implies heavy logging and locking in case of delete (or drop).
Solutiuon: empty the tablespace first; issue a LOAD LOG NO with a dummy
inputdataset. With no rows in the table, dropping should result in minimal
overhead.

With kind regards
Hessel Rus
Sonepar Nederland Information Services

---------------------------------------------------------------------------------
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

Max Scarpa

Re: Drop Tablespace Makes IRLM Run out of memory
(in response to Michael Ebert)
I agree with dr E. I think to remember that (BTW which DB2 version ?) a
version of DB2 before V7 had a similar problem. Have to check if there's
any APAR .

Max Scarpa

---------------------------------------------------------------------------------
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