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

[Serge Rielau]

Re: Truncate Table

pramod wrote:[color=blue]
> 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.[/color]
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

[Knut Stolze]

Re: Truncate Table

pramod wrote:
[color=blue]
> Hi
>
> I know we can truncate a table in DB2 by first creating it with NOT
> LOGGED INITIALLY option.[/color]

It is not necessary any longer to specify the NLI option at create table
time.
[color=blue]
> 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?[/color]

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

[pramod]

Re: Truncate Table

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

[Serge Rielau]

Re: Truncate Table

pramod wrote:[color=blue]
> 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).
>[/color]
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

[Serge Rielau]

Re: Truncate Table

pramod wrote:[color=blue]
> 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).
>[/color]
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

[Bob [IBM]]

Re: Truncate Table

Serge Rielau wrote:[color=blue]
> pramod wrote:[color=green]
>> 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).
>>[/color]
> 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[/color]
Also if I am not mistaken a roll forward of the logs through this
statement will do the same.

Bob