how to empty table more fast

elibs

how to empty table more fast
Hello,list:
I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance?

Thanks

Larry

Jeff Jerry

Re: how to empty table more fast
(in response to elibs)
If the tablespace it is defined in only contains this one table run load
replace with an empty file as input. It basically deletes and redefines the
underlying VSAM data set.

-----Original Message-----
From: elibs [SMTP:[login to unmask email]
Sent: Friday, January 05, 2001 10:15 PM
To: [login to unmask email]
Subject: how to empty table more fast

Hello,list:
I have to a batch job to delete the whole on large table which
takes 10 minutes to complete the operation.Could someone tell me how to
improve the performance?

Thanks

Larry



David Seibert

Re: how to empty table more fast
(in response to Jeff Jerry)
Hi Larry,

Under normal circumstances, the fastest way to delete all rows from a table
is using the LOAD utility with an empty input dataset. It's late here and
it's been awhile, but if I recall, if the tablespace is partitioned you need
to LOAD each partition with nothing.

Check the Util manual.

Good luck

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]



Thomas E. Faglon

Re: how to empty table more fast
(in response to David Seibert)


Larry,
How large is the table?
I use the following sql to delete plan tables
DELETE
FROM &CREATOR.PLAN_TABLE ;
very fast for 250,000 rows.
What does your sql look like?
HTH Tom F





elibs <[login to unmask email]> on 01/05/2001 08:53:32 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Thomas E. Faglon/Telcordia)
Subject:

Murari Selvakesavan

Re: how to empty table more fast
(in response to Thomas E. Faglon)
Hi Larry,

If the Tablespace contains only the table in question you may try Loading an empty file (SYSREC00 DUMMY) using the Load utility with REPLACE(YES) RESUME(NO) option. This essentially empties the table and redefines the dataset.


Murari Selvakesavan.
DB2 DBA Group
First Health Services Corp.
804.965.7601


>>> [login to unmask email] 01/05/01 23:16 PM >>>
Hello,list:
I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance?

Thanks

Larry



Murari Selvakesavan

Re: how to empty table more fast
(in response to Murari Selvakesavan)
Hi Larry,

If the Tablespace contains only the table in question you may try Loading an empty input file (SYSREC00 DUMMY) using the Load utility with REPLACE(YES) RESUME(NO) option. This essentially empties the table and redefines the dataset.


Murari Selvakesavan.
DB2 DBA Group
First Health Services Corp.
804.965.7601


>>> [login to unmask email] 01/05/01 23:16 PM >>>
Hello,list:
I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance?

Thanks

Larry



Jose' Gomez

Re: how to empty table more fast
(in response to Murari Selvakesavan)
Hello:

You can also run a LOAD utility using load REPLACE, and use an empty
datadase...

[login to unmask email]

Re: how to empty table more fast
(in response to Jose' Gomez)
Dummy load the table - LOAD REPLACE with no input dataset...

George



Aratos Sicyon

Re: how to empty table more fast
(in response to truman.g.brown@VERIZON.COM)
Please could everybody to try and pay attention. Now we all know, thanks to
many many contributions, that you can empty a tablespace by loading empty
file. Please nobody tell us this again. Thank you!

Dummy load the table - LOAD REPLACE with no input dataset...

George






_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.



Mike (CC-Database Services) Enarson

Re: how to empty table more fast
(in response to Aratos Sicyon)
Hi Larry. When I want a fast way to delete all of the rows in a table and
it is not partitioned and has only 1 table in it then I make the tablespace
segmented and just do a DELETE FROM owner.tablename.

This marks the tablespace as empty and takes only a few seconds no matter
how many rows are in the table.


Regards
Mike Enarson



Riyaz

Re: how to empty table more fast
(in response to Mike (CC-Database Services) Enarson)
Hi,

Use REPLACE option on a LOAD utility after DUMMYing SYSREC file. This will erase entire TableSpace, so be careful. This works faster than DELETE FROM as this process doesn't create log records.

Riyaz.
FHSC, Richmond.

>>> [login to unmask email] 01/08/01 10:17AM >>>
Hi Larry. When I want a fast way to delete all of the rows in a table and
it is not partitioned and has only 1 table in it then I make the tablespace
segmented and just do a DELETE FROM owner.tablename.

This marks the tablespace as empty and takes only a few seconds no matter
how many rows are in the table.


Regards
Mike Enarson



John Hardy

Re: how to empty table more fast
(in response to Riyaz)
Larry,

Now that we're all agreed that the Dummy Load Replace is a good thing,
assuming DB2 for OS/390 (which is what I infer from your recent series of
questions) : What kind of Tablespace is your table on?

Segmented T/S: Your Mass Delete involves setting a flag on the Spacemap.
This will not take 10 minutes! One big exception I can see: If you have an
RI Relationship from another table dependant on columns in your table,
then I am sure that this must result in the rows being deleted one at a
time, even in a segmented T/S - Does anybody here know for sure about this
one? - Of course, in this situation, the use of our super-duper Dummy Load
Replace would set our hypothetical dependant RI table(s) to Check
Pending.

Partitioned T/S: Only a very odd requirement would require a need to
regularly wipe out an entire Partitioned Tablespace.

Simple T/S: Easy. Drop your tablespace and recreate it as Segmented right
away.



On Mon, 8 Jan 2001 17:12:25 +0200, Aratos Sicyon
<[login to unmask email]> wrote:

>Please could everybody to try and pay attention. Now we all know, thanks to
>many many contributions, that you can empty a tablespace by loading empty
>file. Please nobody tell us this again. Thank you!
>
>Dummy load the table - LOAD REPLACE with no input dataset...
>
>George
>
>
>
the
>
>
>
>_________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
>





Greg Jonas

Re: how to empty table more fast
(in response to John Hardy)
If you do a mass delete on a segmented tablespace, what gets logged? Just
the spacemap pages, or does each deleted row still get logged?

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, January 08, 2001 9:40 AM
To: [login to unmask email]
Subject: Re: how to empty table more fast


Larry,

Now that we're all agreed that the Dummy Load Replace is a good thing,
assuming DB2 for OS/390 (which is what I infer from your recent series of
questions) : What kind of Tablespace is your table on?

Segmented T/S: Your Mass Delete involves setting a flag on the Spacemap.
This will not take 10 minutes! One big exception I can see: If you have an
RI Relationship from another table dependant on columns in your table,
then I am sure that this must result in the rows being deleted one at a
time, even in a segmented T/S - Does anybody here know for sure about this
one? - Of course, in this situation, the use of our super-duper Dummy Load
Replace would set our hypothetical dependant RI table(s) to Check
Pending.

Partitioned T/S: Only a very odd requirement would require a need to
regularly wipe out an entire Partitioned Tablespace.

Simple T/S: Easy. Drop your tablespace and recreate it as Segmented right
away.



On Mon, 8 Jan 2001 17:12:25 +0200, Aratos Sicyon
<[login to unmask email]> wrote:

>Please could everybody to try and pay attention. Now we all know, thanks to
>many many contributions, that you can empty a tablespace by loading empty
>file. Please nobody tell us this again. Thank you!
>
>Dummy load the table - LOAD REPLACE with no input dataset...
>
>George
>
>
>
the
>
>
>
>_________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
>







John Hardy

Re: how to empty table more fast
(in response to Michael Murley)
The manual (Admin Guide) just says:

"For segmented table spaces, the volume of log data written for mass
DELETE statements is much less than for nonsegmented table spaces.".

I'd be interested to know what the UNDO/REDO records look like for a Mass
Delete in a Segmented T/S (but not interested enough to find out by doing
it!).

In this case, Larry has indicated that my guess about the dependant RI
relationship was correct, and the manual does in fact state:

"....if you have a validation procedure, a parent table in a referential
constraint, or DATA CAPTURE CHANGES defined on a table in a segmented table
space, each row has to be processed; thus, you lose the advantage of the
quick mass delete. "


On Mon, 8 Jan 2001 10:10:03 -0600, Greg Jonas
<[login to unmask email]> wrote:

>If you do a mass delete on a segmented tablespace, what gets logged? Just
>the spacemap pages, or does each deleted row still get logged?
>
>-----Original Message-----
>From: [login to unmask email] [mailto:[login to unmask email]
>Sent: Monday, January 08, 2001 9:40 AM
>To: [login to unmask email]
>Subject: Re: how to empty table more fast
>
>
>Larry,
>
>Now that we're all agreed that the Dummy Load Replace is a good thing,
>assuming DB2 for OS/390 (which is what I infer from your recent series of
>questions) : What kind of Tablespace is your table on?
>
>Segmented T/S: Your Mass Delete involves setting a flag on the Spacemap.
>This will not take 10 minutes! One big exception I can see: If you have an
>RI Relationship from another table dependant on columns in your table,
>then I am sure that this must result in the rows being deleted one at a
>time, even in a segmented T/S - Does anybody here know for sure about this
>one? - Of course, in this situation, the use of our super-duper Dummy Load
>Replace would set our hypothetical dependant RI table(s) to Check
>Pending.
>
>Partitioned T/S: Only a very odd requirement would require a need to
>regularly wipe out an entire Partitioned Tablespace.
>
>Simple T/S: Easy. Drop your tablespace and recreate it as Segmented right
>away.
>
>
>
>On Mon, 8 Jan 2001 17:12:25 +0200, Aratos Sicyon
><[login to unmask email]> wrote:
>
>>Please could everybody to try and pay attention. Now we all know, thanks
to
>>many many contributions, that you can empty a tablespace by loading empty
>>file. Please nobody tell us this again. Thank you!
>>
>>Dummy load the table - LOAD REPLACE with no input dataset...
>>
>>George
>>
>>
>>
>the
>>
>>
>>
>>_________________________________________________________________________
>>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>>
>>
>>
>
>
>
>
>
the
>
>
>



Michael Murley

Re: how to empty table more fast
(in response to Greg Jonas)
Greg,

If the table is defined with DATA CAPTURE CHANGES, every deleted row is
logged. Otherwise, only the spacemap updates are logged.

Michael Murley
BMC

-----Original Message-----
From: Greg Jonas [mailto:[login to unmask email]
Sent: Monday, January 08, 2001 10:10 AM
To: [login to unmask email]
Subject: Re: how to empty table more fast



If you do a mass delete on a segmented tablespace, what gets logged? Just
the spacemap pages, or does each deleted row still get logged?

-----Original Message-----
From: [login to unmask email] [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Monday, January 08, 2001 9:40 AM
To: [login to unmask email]
Subject: Re: how to empty table more fast


Larry,

Now that we're all agreed that the Dummy Load Replace is a good thing,
assuming DB2 for OS/390 (which is what I infer from your recent series of
questions) : What kind of Tablespace is your table on?

Segmented T/S: Your Mass Delete involves setting a flag on the Spacemap.
This will not take 10 minutes! One big exception I can see: If you have an
RI Relationship from another table dependant on columns in your table,
then I am sure that this must result in the rows being deleted one at a
time, even in a segmented T/S - Does anybody here know for sure about this
one? - Of course, in this situation, the use of our super-duper Dummy Load
Replace would set our hypothetical dependant RI table(s) to Check
Pending.

Partitioned T/S: Only a very odd requirement would require a need to
regularly wipe out an entire Partitioned Tablespace.

Simple T/S: Easy. Drop your tablespace and recreate it as Segmented right
away.



On Mon, 8 Jan 2001 17:12:25 +0200, Aratos Sicyon
<[login to unmask email]> wrote:

>Please could everybody to try and pay attention. Now we all know, thanks to

>many many contributions, that you can empty a tablespace by loading empty
>file. Please nobody tell us this again. Thank you!
>
>Dummy load the table - LOAD REPLACE with no input dataset...
>
>George
>
>
>
the
>DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > .
The owners of the list can be
>
>
>_________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
< http://www.hotmail.com > .
>
>
>
the DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > .
The owners of the list can




DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > . The
owners of the list can