truncate table for DB2 - differences

[Stefan Mueller]

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

[Serge Rielau]

The IMPORT can be handled by roll-forward recovery.
The ALTER TABLE cannot.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Stefan Mueller]

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

[Serge Rielau]

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

[Ekta]

You may consider using LOAD instead of import.

@Ekta

[Troels Arvin]

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/

[Troels Arvin]

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/

[Dave Hughes]

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.