_how_to_empty_table_more_fast?

Roger Miller

_how_to_empty_table_more_fast?
Both drop table and delete all of the rows (delete with no where clause) are
very fast for a segmented table, but there are restrictions. You should
probably make sure that you know the organization of the table space, the
referential constraints, ... The Administration Guide section on designing
a database has a discussion of the table space options.

Roger Miller, DB2 for OS/390



Michael Murley

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

If the data pages have not been re-used since the mass delete, RECOVER PLUS
BACKOUT can still work because the rows are still in the data pages. If they
have been reused, we can't back through the mass delete and we issue these
messages:

BMC40265E NO DATA IN PAGE X'%X' BUT SPACE MAP PAGE X'%X' INDICATES DATA PAGE
IS IN USE
BMC40266E THIS PROBABLY INDICATES A BACKOUT OF A MASS DELETE OF A PAGE WHICH
HAS BEEN REUSED. YOU MUST RUN A
FORWARD RECOVERY

There is a section in the RECOVER PLUS manual called 'When is BACKOUT
Recovery Allowed' (on p. 6-23 in the current manual) that discusses this
restriction of the feature.

Michael

-----Original Message-----
From: Greg Jonas [mailto:[login to unmask email]
Sent: Monday, January 08, 2001 2:48 PM
To: '[login to unmask email]'
Subject: FW: how to empty table more fast



Looks like DB2-L is backed up, since I haven't seen this come across, so I
thought I'd send it straight to you. Thanks for your first response.

-----Original Message-----
From: Greg Jonas
Sent: Monday, January 08, 2001 11:43 AM
To: '[login to unmask email]'
Subject: RE: how to empty table more fast


Michael,
This answer matches what we were guessing. But it brings up another
question dealing with BMC's Recovery Plus and the use of the BACKOUT option.
If you have the following scenario:

DELETE *
Commit
INSERT (overlaying pages used before the delete)
Commit

If you now want to use the BACKOUT option to prior to the DELETE, how could
it recreate the data that was overlayed by the INSERTs?

This isn't a major problem with us right now, but this thread got us
thinking about it.

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


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



Mark McCormack

how to empty table more fast
(in response to Michael Murley)
Whether you can quickly delete all rows from a table via an SQL stmt
(DELETE FROM creator.table) depends on the table structure.

If the table is segmented, then only the space map pages will be reset and
logged. The process will be quick and efficient. If the tablespace is not
segmented (partitioned or simple), then a mass delete via SQL will cause
each row to be deleted individually. This can take much time and involve
lots of logging for large tables.

In any case you may use the load utility with an empty input file (LOAD
... REPLACE LOG YES INTO TABLE creator.table).
The REPLACE option causes the entire tablespace to be cleared by resetting
all space map pages. Only the space map pages will be logged, so LOG YES is
efficient. If you are at a DB2 version that supports them, you might also
consider using additional options on the LOAD control stmt: REUSE to avoid
delete/define/reinitialize of VSAM data sets; KEEPDICTIONARY if compression
is used.

Mark McCormack
State Street Bank