By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

Deleting a table: Why isn't db2 taking an obvious shortcut?

P: n/a
Hello,

When doing "DELETE FROM tablename" on a large table, it may take a while
if the table is large.

On the other hand, the "IMPORT FROM /dev/null OF DEL REPLACE INTO
tablename" table runs rather quickly.

Why doesn't the db2 optimizer automatically change unrestricted DELETEs
into the equivalent of "IMPORT FROM /dev/null OF DEL ..."? - As far as I
know, the "IMPORT FROM /dev/null OF DEL ..." trick is fully recoverable.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Nov 26 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Troels Arvin wrote:
Hello,

When doing "DELETE FROM tablename" on a large table, it may take a while
if the table is large.

On the other hand, the "IMPORT FROM /dev/null OF DEL REPLACE INTO
tablename" table runs rather quickly.

Why doesn't the db2 optimizer automatically change unrestricted DELETEs
into the equivalent of "IMPORT FROM /dev/null OF DEL ..."? - As far as I
know, the "IMPORT FROM /dev/null OF DEL ..." trick is fully recoverable.
Ah.. TRUNCATE TABLE... most punted item..... not ethat IMPORT does an
implicit COMMIT IIRC, DELETE doesn't. So just using DELETE isn't going
to cut it.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 26 '07 #2

P: n/a
Hey,

On Mon, 26 Nov 2007 09:24:53 -0500, Serge Rielau wrote:
>Why doesn't the db2 optimizer automatically change unrestricted DELETEs
into the equivalent of "IMPORT FROM /dev/null OF DEL ..."? - As far as
I know, the "IMPORT FROM /dev/null OF DEL ..." trick is fully
recoverable.
IMPORT does an implicit COMMIT IIRC, DELETE doesn't
OK, so there _is_ a (rather important) difference. Thanks for making it
make sense.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Nov 26 '07 #3

P: n/a
>>On 11/26/2007 at 7:24 AM, in message
<5r*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Troels Arvin wrote:
>Hello,

When doing "DELETE FROM tablename" on a large table, it may take a while
>
>if the table is large.

On the other hand, the "IMPORT FROM /dev/null OF DEL REPLACE INTO
tablename" table runs rather quickly.

Why doesn't the db2 optimizer automatically change unrestricted DELETEs
into the equivalent of "IMPORT FROM /dev/null OF DEL ..."? - As far as I
>know, the "IMPORT FROM /dev/null OF DEL ..." trick is fully recoverable.
Ah.. TRUNCATE TABLE... most punted item..... not ethat IMPORT does an
implicit COMMIT IIRC, DELETE doesn't. So just using DELETE isn't going
to cut it.
Hmm, is z/OS beating out LUW on this issue?

http://publib.boulder.ibm.com/infoce...jsp?topic=/com.
ibm.db29.doc.wnew/db2z_sqlleadtruncatewnew.htm

:-)
Nov 27 '07 #4

P: n/a
Frank Swarbrick wrote:
>>>On 11/26/2007 at 7:24 AM, in message
<5r*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
>Troels Arvin wrote:
>>Hello,

When doing "DELETE FROM tablename" on a large table, it may take a while
>>if the table is large.

On the other hand, the "IMPORT FROM /dev/null OF DEL REPLACE INTO
tablename" table runs rather quickly.

Why doesn't the db2 optimizer automatically change unrestricted DELETEs
into the equivalent of "IMPORT FROM /dev/null OF DEL ..."? - As far as I
>>know, the "IMPORT FROM /dev/null OF DEL ..." trick is fully recoverable.
Ah.. TRUNCATE TABLE... most punted item..... not ethat IMPORT does an
implicit COMMIT IIRC, DELETE doesn't. So just using DELETE isn't going
to cut it.

Hmm, is z/OS beating out LUW on this issue?

http://publib.boulder.ibm.com/infoce...jsp?topic=/com.
ibm.db29.doc.wnew/db2z_sqlleadtruncatewnew.htm
Twice actually. DB2 for zOS also has fast DELETE capabilities which
allow it to delete all records by simply marking the extents (or
whatever it's called over there) as unused.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 27 '07 #5

P: n/a
>>>When doing "DELETE FROM tablename" on a large table, it may take a
>>>while
if the table is large.
>>Ah.. TRUNCATE TABLE... most punted item..... not ethat IMPORT does an
implicit COMMIT IIRC, DELETE doesn't. So just using DELETE isn't
going to cut it.
>Hmm, is z/OS beating out LUW on this issue?
In i5/OS use the command CLRPFM :-)

Twice actually. DB2 for zOS also has fast DELETE capabilities which
allow it to delete all records by simply marking the extents (or
whatever it's called over there) as unused.
It's the same on DB2 for i5/OS (aka AS/400)... ;-)
After that the record may be or is not reused depending on the REUSEDLT
property of the file.

Cheers
Serge
Regards
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere" dall'indirizzo email ;-)
Nov 28 '07 #6

P: n/a
DB2 for i5/OS does also have a "fast delete" which under isolation,
is effectively an ALTER creating the new table and copying the EXCEPT of
the WHERE for the DELETE for high volume deletions, and leaving the
prior copy available to be reinstated in a ROLLBACK. Without isolation
it is the same, but no copy maintained. Without a WHERE clause, all of
the extents can be freed versus any ALTER-like activity; i.e. what is
known outside of DB2 for i5/OS SQL, as the i5/OS command CLRPFM. In any
case, there will be no deleted rows. Obviously certain RI delete rules,
concurrent activity, optimization [that deems quicker for normal path],
and DELETE triggers could preclude such activity; i.e. although it can
occur, it would not always be possible.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Stefano P. wrote:
Frank Swarbrick wrote:
>Serge Rielau<sr*****@ca.ibm.comwrote:
>>Troels Arvin wrote:
When doing "DELETE FROM tablename" on a large table, it may take a
while if the table is large.
>>>Ah.. TRUNCATE TABLE... most punted item..... note that IMPORT does
an implicit COMMIT IIRC, DELETE doesn't. So just using DELETE isn't
going to cut it.
>>Hmm, is z/OS beating out LUW on this issue?

In i5/OS use the command CLRPFM :-)
>Twice actually. DB2 for zOS also has fast DELETE capabilities which
allow it to delete all records by simply marking the extents (or
whatever it's called over there) as unused.

It's the same on DB2 for i5/OS (aka AS/400)... ;-)
After that the record may be or is not reused depending on the REUSEDLT
property of the file.
Dec 2 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.