473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 14659
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: martin | last post by:
Hi, We have a heavily used production server and a table which logs every hit on a web site. This table has grown large over time and we want to clear it down as efficiently as possible. We would...
3
by: LineVoltageHalogen | last post by:
Greeting All, I have a stored proc that dynamically truncates all the tables in my databases. I use a cursor and some dynamic sql for this: ...... create cursor Loop through sysobjects and...
1
by: New MSSQL DBA | last post by:
I have recently been assigned to take over several MSSQL environments and found some of the existing practice confusing. As most of my previous experiences are on Oracle and Unix platform so would...
5
by: ronin 47th | last post by:
Hi group, In one of the books 'Gurus Guide to Transact SQL' i found this info: ------------------------------------------------------------ TRUNCATE TABLE empties a table without logging row...
2
by: rdraider | last post by:
Hi, I am trying to create a script that deletes transaction tables and leaves master data like customer, vendors, inventory items, etc. How can I use TRUNCATE TABLE with an Exists? My problem is...
14
by: Sala | last post by:
Hi I want to truncate all data in database ... pls help me how i ll truncate?
10
by: Troels Arvin | last post by:
Hello, Until this date, I believed that DB2 has no TRUNCATE TABLE command. But then I came across...
5
by: Timothy Madden | last post by:
Hello I see there is now why to truncate a file (in C or C++) and that I have to use platform-specific functions for truncating files. Anyone knows why ? I mean C/C++ evolved over many years...
8
by: ananthaisin | last post by:
How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not .... I have given truncate statement in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.