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

truncate table for DB2 - differences

P: n/a
Hello all,

what is the internal difference between the following two truncate
table commands:
db2 import from /dev/null of del replace into <table_name>

and

alter table <table_nameactivate not logged initially with empty
table
Thank you very much in advance.

Kind regards,

Stefan

--
Stefan Mueller
Hamburg, Germany

Jun 20 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
The IMPORT can be handled by roll-forward recovery.
The ALTER TABLE cannot.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 20 '07 #2

P: n/a
Hi Serge,

thank you very much for your fast reply.

Is it more likely to get a dead lock by using the IMPORT command
instead using the ALTER TABLE?

Kind regards,

Stefan

Jun 20 '07 #3

P: n/a
Stefan Mueller wrote:
Is it more likely to get a dead lock by using the IMPORT command
instead using the ALTER TABLE?
In general I would assume that table truncation is at the beginning of a
transaction (or on its own). I fail to see how that could cause a deadlock.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 20 '07 #4

P: n/a
You may consider using LOAD instead of import.

@Ekta

Jun 23 '07 #5

P: n/a
On Sat, 23 Jun 2007 19:28:25 +0000, Ekta wrote:
You may consider using LOAD instead of import.
I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that failed.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Jun 24 '07 #6

P: n/a
On Sun, 24 Jun 2007 19:33:39 +0000, I wrote:
I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that failed.
I meant to write "IMPORT" (not INSERT).

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Jun 25 '07 #7

P: n/a
On Mon, 25 Jun 2007 17:58:10 +0000, Troels Arvin scribbled:
On Sun, 24 Jun 2007 19:33:39 +0000, I wrote:
>I generally try to use INSERT instead of LOAD whenever feasible. I've
seen LOAD result in rather nasty need-to-involve-the-DBA-and-even-
sometimes-IBM-support situations because of LOAD operations that
failed.

I meant to write "IMPORT" (not INSERT).
FYI - I've only seen what you describe with LOAD in situations where the
database is set up to use archived logging. With circular logging, I've
always found LOAD to be perfectly reliable (although a few SET INTEGRITY
calls may be required afterward, if foreign keys are defined).

Admittedly, circular logging has several limitations (no online backup,
no rollforward recovery, etc.) and certainly isn't suitable for many
installations - but if it is used, LOAD should be seriously considered as
an alternative to IMPORT.

Cheers,

Dave.

Jul 4 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.