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

Hessel Rus

Betr.: Re: [DB2-L] Drop Tablespace Makes IRLM Run out of memory
Agree, there is no delete before the drop. But in the drop proces, my
assumption is that all pages are logged since it's behaviour is like a
simple tablespace.
A few years (and versions) ago we have some experience with dropping a
partitioned tablespace and as i remember well we have benefit of empty the
tablespace.

Met vriendelijke groeten,
Hessel Rus

Sonepar Nederland Information Services
Afd. : DMA/IC
Tel. : +31 20 5450874
Fax : +31 20 5450885
Email: [login to unmask email]



Michael Ebert <[login to unmask email]>
Verzonden door: DB2 Data Base Discussion List <[login to unmask email]>
12-12-2005 15:11
Antwoord a.u.b. aan
DB2 Database Discussion list at IDUG <[login to unmask email]>


Aan
[login to unmask email]
Cc

Onderwerp
Re: [DB2-L] Drop Tablespace Makes IRLM Run out of memory







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


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

Walter Jani&#223;en

Re: Betr.: [DB2-L] Drop Tablespace Makes IRLM Run out of memory
(in response to Hessel Rus)
On Mon, 12 Dec 2005 14:15:45 +0100, Hessel Rus <[login to unmask email]>
wrote:

I think this reply cannot be unanswered, because it is wrong. There will be
no logging difference, when a table is dropped, whether this table has rows
or not.

As Phil already elaborated, the number of objects depending on that drop,
matters nothing else.

May be, is there are many extents of the pagesets and many indexes, the
VSAM-delete can last.

>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 DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>

Hi

I think

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

Mike Bell

Re: Betr.: Re: [DB2-L] Drop Tablespace Makes IRLM Run out of memory
(in response to Walter Janißen)
No, the only things logged during drop are the updated pages for the
catalog. None of the tablespace pages or indexspace pages are logged during
a drop.

Best practices for a fast drop.
1. free all packages
2. modify recovery age(*) (this is the biggest source of locks during the
drop).
3. IDCAMS delete the datasets (this is the biggest component of the elapsed
time).
4. drop the tablespace

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Hessel Rus
Sent: Monday, December 12, 2005 8:43 AM
To: [login to unmask email]
Subject: [DB2-L] Betr.: Re: [DB2-L] Drop Tablespace Makes IRLM Run out of
memory


Agree, there is no delete before the drop. But in the drop proces, my
assumption is that all pages are logged since it's behaviour is like a
simple tablespace.
A few years (and versions) ago we have some experience with dropping a
partitioned tablespace and as i remember well we have benefit of empty the
tablespace.

Met vriendelijke groeten,
Hessel Rus

Sonepar Nederland Information Services
Afd. : DMA/IC
Tel. : +31 20 5450874
Fax : +31 20 5450885
Email: [login to unmask email]



Michael Ebert <[login to unmask email]>
Verzonden door: DB2 Data Base Discussion List <[login to unmask email]>

12-12-2005 15:11
Antwoord a.u.b. aan
DB2 Database Discussion list at IDUG <[login to unmask email]>

Aan
[login to unmask email]
Cc
Onderwerp
Re: [DB2-L] Drop Tablespace Makes IRLM Run out of memory







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


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


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


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