truncate in db2

[Sumanth]

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

[Serge Rielau]

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]

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.wash ington.edu
(replace x with u to respond)

[peteh]

>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....Thi s 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

[Sumanth]

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

[Serge Rielau]

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

[Serge Rielau]

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

[Serge Rielau]

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_ROU TINE_OPTS('DYNA MICRULES 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_ROU TINE_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('SRIEL AU', '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

[Bob [IBM]]

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

[th_maran]

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