TRUNCATE stored procedure

[Frank Swarbrick]

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/infoce...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

[Troels Arvin]

Frank Swarbrick wrote:

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

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

[Frank Swarbrick]

>>On 5/22/2008 at 3:51 PM, in message <g1**********@news.net.uni-c.dk>,
Troels Arvin<tr****@arvin.dkwrote:

Frank Swarbrick wrote:

>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....se_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
<tablestatement?

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

[Ian]

Frank Swarbrick wrote:

>>>On 5/22/2008 at 3:51 PM, in message <g1**********@news.net.uni-c.dk>,

Troels Arvin<tr****@arvin.dkwrote:

>Frank Swarbrick wrote:

>>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....se_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
<tablestatement?

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.