By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

TRUNCATE TABLE really exists, or?

P: n/a
Hello,

Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came across
http://publib.boulder.ibm.com/infoce...c/c0023297.htm
where it says:

After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE

So there _is_ a TRUNCATE TABLE command, or?
My DB2 Express-C v. 9.1 will NOT accept a "TRUNCATE TABLE foo" command.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Jan 31 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Troels Arvin wrote:
Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came across
http://publib.boulder.ibm.com/infoce...c/c0023297.htm
where it says:

After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE
Doc defect... don't loose your faith just yet.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 1 '07 #2

P: n/a
On Jan 31, 3:13 pm, Troels Arvin <tro...@arvin.dkwrote:
Hello,

Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came acrosshttp://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
where it says:

After an ALTER TABLE statement containing
REORG-recommended operations, you can execute
only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
TRUNCATE TABLE

So there _is_ a TRUNCATE TABLE command, or?
My DB2 Express-C v. 9.1 will NOT accept a "TRUNCATE TABLE foo" command.

--
Regards,
Troels Arvin <tro...@arvin.dk>http://troels.arvin.dk/
Hi, Troels:

This is somewhat well-known, so forgive me if you already know it, but
if it happens that you're looking not for the TRUNCATE command per se
but rather in the meantime a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:

1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null

Regards,

--Jeff

Feb 1 '07 #3

P: n/a
Hello,

On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:

1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Which one requires the least privileges?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 1 '07 #4

P: n/a
On Feb 1, 11:42 am, Troels Arvin <tro...@arvin.dkwrote:
Hello,

On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
a way to accomplish the same thing in the
absence of an explicit TRUNCATE command, in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:
1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null

Which one requires the least privileges?

--
Regards,
Troels Arvin <tro...@arvin.dk>http://troels.arvin.dk/
According to the SQL Reference Volume 2 and the Command Reference,
respectively:

ALTER needs at least one of the following:

ALTER on the table to be altered
CONTROL on the table to be altered
ALTERIN on the schema of the table
SYSADM
DBADM

IMPORT (to an existing table using the REPLACE or REPLACE_CREATE
option) requires one of the following:

SYSADM
DBADM
CONTROL on the table or view
INSERT, SELECT, and DELETE on the table or view

So I suppose IMPORT requires the least privileges, if you are generous
in giving INSERT, SELECT, and DELETE.

--Jeff

Feb 1 '07 #5

P: n/a
Hello,

On Thu, 01 Feb 2007 12:19:09 -0800, jefftyzzer wrote:
According to the SQL Reference Volume 2 and the Command Reference,
respectively:
[...]

Thanks. My summary:
http://troels.arvin.dk/db/rdbms/#bul...cate_table-db2

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 1 '07 #6

P: n/a
On Feb 1, 2:51 pm, Troels Arvin <tro...@arvin.dkwrote:
Hello,

On Thu, 01 Feb 2007 12:19:09 -0800, jefftyzzer wrote:
According to the SQL Reference Volume 2 and the Command Reference,
respectively:

[...]

Thanks. My summary:http://troels.arvin.dk/db/rdbms/#bul...cate_table-db2

--
Regards,
Troels Arvin <tro...@arvin.dk>http://troels.arvin.dk/
Nice work--very useful!

--Jeff

Feb 2 '07 #7

P: n/a
Hello,

On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:

1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null
Is there a performance difference between these two approaches?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 2 '07 #8

P: n/a
Troels Arvin wrote:
Hello,

On Thu, 01 Feb 2007 10:12:59 -0800, jefftyzzer wrote:
>in DB2 there are two ways to
get the same result of fast cleanout of all rows in a table:

1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null

Is there a performance difference between these two approaches?
Not that I'm aware of, but option 2 is fully recoverable.
If you truncate an important table using ALTER TABLE you should follow
it up with a backup, otherwise roll forward recovery will hiccup.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 2 '07 #9

P: n/a
On Fri, 02 Feb 2007 08:43:35 -0500, Serge Rielau wrote:
>>1. ALTER TABLE <NOT LOGGED INITIALLY WITH EMPTY TABLE;
2. IMPORT from /dev/null

Is there a performance difference between these two approaches?
Not that I'm aware of, but option 2 is fully recoverable.
If you truncate an important table using ALTER TABLE you should follow
it up with a backup, otherwise roll forward recovery will hiccup.
Ouch. I'll stay away from ALTER TABLE ... NOT LOGGED INITIALLY. (I've
recently been bit by a rather time consuming situation because we tried to
cut some corners using NOT LOGGED INITIALLY.)

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 2 '07 #10

P: n/a
Ian
Serge Rielau wrote:
Doc defect... don't loose your faith just yet.
Dang! Is it a defect in that the doc was updated too soon? ;-)

Feb 3 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.