TRUNCATE stored procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Swarbrick - 22 May 2008 20:33 GMT

The following link includes an example of a stored procedure that can be
used to truncate a table without logging:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.
db2.udb.apdv.sample.doc/doc/admin_scripts/s-truncate-db2.htm

It uses "IMPORT FROM NUL OF DEL REPLACE INTO ...", where other examples of
this type of thing seem to use LOAD instead of IMPORT.

Any thoughts as to why one method might be prefered over the other?

Frank

Reply to this Message

Troels Arvin - 22 May 2008 22:51 GMT

> It uses "IMPORT FROM NUL OF DEL REPLACE INTO ...", where other examples
> of this type of thing seem to use LOAD instead of IMPORT.

The IMPORT-based method
- requires less privileges than other methods
- if fully recoverable

See also http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/cc36736a
a9b81c6c/a0ba85f541175295

Signature

Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

Reply to this Message

Frank Swarbrick - 23 May 2008 18:43 GMT

>>> On 5/22/2008 at 3:51 PM, in message <g14psm$d6b$1@news.net.uni-c.dk>,
>> It uses "IMPORT FROM NUL OF DEL REPLACE INTO ...", where other examples
[quoted text clipped - 7 lines]
> http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/
> cc36736aa9b81c6c/a0ba85f541175295

Does this mean that IMPORT does logging?
If so, why would this method be preferred over just a plain DELETE FROM
<table> statement?

In the case I'm looking for it's simply for truncating a table for testing,
so I can rerun the test and insert the data again. I assume in this case I
would not care about it being recoverable.

Frank

Reply to this Message

Ian - 28 May 2008 06:07 GMT

>>>> On 5/22/2008 at 3:51 PM, in message <g14psm$d6b$1@news.net.uni-c.dk>,
>>> It uses "IMPORT FROM NUL OF DEL REPLACE INTO ...", where other examples
[quoted text clipped - 10 lines]
> If so, why would this method be preferred over just a plain DELETE FROM
> <table> statement?

Yes, but it just logs a small record indicating that the table was
replaced (truncated).

Obviously this is much more efficient than logging each record that is
deleted.

> In the case I'm looking for it's simply for truncating a table for testing,
> so I can rerun the test and insert the data again. I assume in this case I
> would not care about it being recoverable.

That could certainly be the case.

Remember, "recoverable" only applies if your database is enabled for
archive logging.

Reply to this Message