470,826 Members | 2,363 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,826 developers. It's quick & easy.

truncate in db2

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
Feb 15 '06 #1
9 14414
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
Feb 15 '06 #2
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
da******@x.washington.edu
(replace x with u to respond)
Feb 15 '06 #3
>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

Feb 15 '06 #4
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" <sr*****@ca.ibm.com> wrote in message
news:45************@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

Feb 15 '06 #5
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
Feb 15 '06 #6
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
Feb 15 '06 #7
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
Feb 15 '06 #8
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" <sr*****@ca.ibm.com> wrote in message
news:45************@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
Feb 16 '06 #9
dear Sumanth

To Truncate a table in db2 i am using the below method

Unix: IMPORT FROM /dev/null OF DEL REPLACE INTO <table>
Windows: IMPORT FROM A.IXF OF IXF REPLACE INTO <table>
(just create an empty .ixf file)

thanks
Thirumaran
Feb 17 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by LineVoltageHalogen | last post: by
1 post views Thread by New MSSQL DBA | last post: by
5 posts views Thread by ronin 47th | last post: by
2 posts views Thread by rdraider | last post: by
14 posts views Thread by Sala | last post: by
10 posts views Thread by Troels Arvin | last post: by
5 posts views Thread by Timothy Madden | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.