SEO

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?

Thanks,
Sumanth

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

>What happens if someone else is using the table at the same time?
In a data warehouse environment, we force the lockholder off to do the
Alter Table Activate....This is a standard part of our daily and weekly
data maintenance.

I would defer to Serge on this, but I THINK you could also do an online
(allow read access) load of an empty cursor for a slightly more elegant
solution.

Pete H

Thanks Serge.

For this to be executed within the application code would require the
application-db2-user to have alter privileges.. is it a good practice?

Also is truncate being planned for future DB2 implementations.

Thanks for your time and help.

Thanks,
Sumanth

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:45hhd3F6pvlcU1@individual.net...
> 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
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

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

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

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

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

Sumanth wrote:
> Thanks Serge.
>
> For this to be executed within the application code would require the
> application-db2-user to have alter privileges.. is it a good practice?
>
> Also is truncate being planned for future DB2 implementations.
Eventually. I'm not sure whether a hypothetical TRUNCATE will only
require DELETE privileges.
It's a pretty big gun to unrecoverably wipe a table, and ignore triggers..
After all I assume you do not want to simple have TRUNCATE be a
"DELETE FROM T", right?

Interstingly I tried to see if you can work around it, but DB2 is
quite paranoid at this particluar point:

db2 => --#SET TERMINATOR $
db2 => DROP SPECIFIC PROCEDURE TRUNCATE
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS('DYNAMICRULES BIND')
db2 (cont.) => $

Return Status = 0

db2 => CREATE PROCEDURE TRUNCATE(IN tabschema VARCHAR(128),
db2 (cont.) => IN tabname VARCHAR(128))
db2 (cont.) => SPECIFIC TRUNCATE
db2 (cont.) => BEGIN
db2 (cont.) => DECLARE txt VARCHAR(1000);
db2 (cont.) => SET txt = 'ALTER TABLE "' || tabschema || '"."'
db2 (cont.) => || tabname || '" ACTIVATE NOT LOGGED'
db2 (cont.) => || ' INITIALLY WITH EMPTY TABLE';
db2 (cont.) => EXECUTE IMMEDIATE txt;
db2 (cont.) => COMMIT;
db2 (cont.) => END
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS(CAST(NULL AS VARCHAR(1)))
db2 (cont.) => $

Return Status = 0

db2 => GRANT EXECUTE ON PROCEDURE TRUNCATE TO JOE
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => --#SET TERMINATOR ;
db2 => call truncate('SRIELAU', 'A3');
SQL0549N The "ALTER" statement is not allowed for "package" "P7300390"
because the bind option DYNAMICRULES RUN is not in effect for the "package".
SQLSTATE=42509

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

Just a passing comment ... using ALTER TABLE ... in this manner will make the
table non-recoverable in the event you are using log retention if I am not
mistaken. Something to consider.

Bob
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:45hhd3F6pvlcU1@individual.net...
| 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
|
| --
| Serge Rielau
| DB2 Solutions Development
| IBM Toronto Lab