DA Morgan wrote:
> Serge Rielau wrote:
>
>> Sumanth wrote:
>>
>>> Are there any implementations of truncate in db2. Is it going to be
>>> implemented in the future?
>>>
>>> Is there an alternate way of doing a truncate of a table that has a
>>> high record count without using "load" and is fast?
>>
>>
>> ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
>> COMMIT;
>>
>> Cheers
>> Serge
>
>
> What happens if someone else is using the table at the same time?
Good question. I guessed the answer, but still felt compelled to test.
It's just a regular DDL statement.
ALTER TABLE will have to wait until that someone else is done.

Session 1:
db2 => connect to test;

Database Connection Information

Database server = DB2/NT Viper
SQL authorization ID = SRIELAU
Local database alias = TEST

-- turn of auto commit
db2 => update command options using c off;
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
db2 => declare cur1 cursor for select * from a3;
DB20000I The SQL command completed successfully.
db2 => open cur1;
DB20000I The SQL command completed successfully.
--- Run session 2 here
db2 => close cur1;
DB20000I The SQL command completed successfully.
db2 => open cur1;
-- Wait for session 2 to commit
DB20000I The SQL command completed successfully.

session 2:
db2 => connect to test;

Database Connection Information

Database server = DB2/NT Viper
SQL authorization ID = SRIELAU
Local database alias = TEST

-- turn of auto commit
db2 => update command options using c off;
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
db2 => alter table a3 activate not logged initially with empty table;
-- Waits for session 1
-- returns when cursor is closed
DB20000I The SQL command completed successfully.
db2 => commit;


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab