Truncate Table

pramod

Hi

I know we can truncate a table in DB2 by first creating it with NOT
LOGGED INITIALLY option. and when we need to truncate it, run the
following command

alter table <table name> activate not logged initially with empty table

My question is:
1. Do we have to connect to the database with auto-commit off every
time we have to run this.
If yes, then why?

thanks

Apr 11 '06 #1

Serge Rielau

pramod wrote:

Hi

I know we can truncate a table in DB2 by first creating it with NOT
LOGGED INITIALLY option. and when we need to truncate it, run the
following command

alter table <table name> activate not logged initially with empty table

My question is:
1. Do we have to connect to the database with auto-commit off every
time we have to run this.

First, there is no need anymore to define the table as NLI up front.
If all you want is to truncate the table then it doesn't matter whether
you do commit explicitly or you let the client do auto-commit for you.
If, however you want to follow the truncate up with e.g. a mass insert
it may make sense to share the transaction between the alter and the
inserts.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 11 '06 #2

Knut Stolze

pramod wrote:

Hi

I know we can truncate a table in DB2 by first creating it with NOT
LOGGED INITIALLY option.
It is not necessary any longer to specify the NLI option at create table
time.
and when we need to truncate it, run the
following command

alter table <table name> activate not logged initially with empty table

My question is:
1. Do we have to connect to the database with auto-commit off every
time we have to run this.
If yes, then why?

No, you don't. The ALTER TABLE will truncate the table. If this is all you
want to do, you can commit right away or let auto-commit do this for you.
But if you want to do other things on the table that are not logged, you
have to do this in the same transaction as the ALTER TABLE and /then/ you
need to ensure that no implicit commit is run, i.e. turn auto-commit off.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Apr 11 '06 #3

pramod

Correct me if anything wrong
I create a table without NOT LOGGED INITIALLY OPTION. Now when i have
to truncate it, then i execute the SQL
alter table <table name> activate NOT LOGGED INITIALLY with empty table

This will truncate the table. Now my question is:
1. Will it log the tansaction while SQL statement is truncating the
table (even if we don't turn off the auto commit).

Apr 14 '06 #4

Serge Rielau

pramod wrote:

Correct me if anything wrong
I create a table without NOT LOGGED INITIALLY OPTION. Now when i have
to truncate it, then i execute the SQL
alter table <table name> activate NOT LOGGED INITIALLY with empty table

This will truncate the table. Now my question is:
1. Will it log the tansaction while SQL statement is truncating the
table (even if we don't turn off the auto commit).

Not it will not log.
Keep in mind that any error between the ALTER and the next COMMIT will
cause the table to be places in DROP PENDING.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 14 '06 #5

Serge Rielau

pramod wrote:

Correct me if anything wrong
I create a table without NOT LOGGED INITIALLY OPTION. Now when i have
to truncate it, then i execute the SQL
alter table <table name> activate NOT LOGGED INITIALLY with empty table

This will truncate the table. Now my question is:
1. Will it log the tansaction while SQL statement is truncating the
table (even if we don't turn off the auto commit).

No it will not log.
Keep in mind that any error between the ALTER and the next COMMIT will
cause the table to be placed in DROP PENDING.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 14 '06 #6

Bob [IBM]

Serge Rielau wrote:

pramod wrote:

Correct me if anything wrong
I create a table without NOT LOGGED INITIALLY OPTION. Now when i have
to truncate it, then i execute the SQL
alter table <table name> activate NOT LOGGED INITIALLY with empty table

This will truncate the table. Now my question is:
1. Will it log the tansaction while SQL statement is truncating the
table (even if we don't turn off the auto commit).

No it will not log.
Keep in mind that any error between the ALTER and the next COMMIT will
cause the table to be placed in DROP PENDING.

Cheers
Serge

Also if I am not mistaken a roll forward of the logs through this
statement will do the same.

Bob

Apr 14 '06 #7