SQL Query to truncate table in IBM DB2

Question

Can any one give me the syntax to truncate a table in IBM DB2.

I m running the following command: truncate table tableName immediate;

The eror is DB2

SQLCODE=-104, SQLSTATE=42601, SQLERRMC=table;truncate ;JOIN , DRIVER=3.50.152 Message: An unexpected token "table" was found following "truncate ". Expected tokens may include: "JOIN ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.50.152

The syntax matches the one specified in the reference docs of IBM : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_truncate.htm

What version of DB2 are you running? – Nick Vallely Jun 09 '10 at 15:24 — Nick Vallely, Jun 09 '10 at 15:24

score 15 · Accepted Answer · edited Jul 17 '13 at 04:31

There is a great article about truncating, here is the Gist of DB2 stuff

Almost follows the standard.(since version 9.7) DB2 requires that the IMMEDIATE keyword be added the the ordinary TRUNCATE TABLE statement, e.g.:

TRUNCATE TABLE someschema.sometable IMMEDIATE

TRUNCATE TABLE must be the first statement in a transaction. A transaction starting with TRUNCATE TABLE may include other statements, but if the transaction is rolled back, the TRUNCATE TABLE operation is not undone. DB2s TRUNCATE TABLE operation has a number of optional arguments, see the documentation for more on this; especially, the REUSE STORAGE argument may be important for ad-hoc DBA tasks. In DB2 versions < 9.7, you may abuse the IMPORT statement. Unfortunately, you need to know which operating system the command is executed from for this to work:

On unix-like systems: IMPORT FROM /dev/null OF DEL REPLACE INTO tablename On Windows: IMPORT FROM NUL OF DEL REPLACE INTO tablename IMPORT cannot be abused in all contexts. E.g., when working with dynamic SQL (from Java/.NET/PHP/...—not using the db2 command line processor), you need to wrap the IMPORT command in a call to ADMIN_CMD, e.g.:

CALL ADMIN_CMD('IMPORT FROM /dev/null OF DEL REPLACE INTO tablename')

IMPORT seems to be allowed in a transaction involving other operations, however it implies an immediate COMMIT operation.

The ALTER TABLE command may also be abused to quickly empty a table, but it requires more privileges, and may cause trouble with rollforward recovery.

This was taken from the website: http://troels.arvin.dk/db/rdbms/#bulk-truncate_table-db2

Thx. IMPORT FROM /dev/null OF DEL REPLACE INTO tablename did the trick — Cshah, Jun 10 '10 at 07:26

score 3 · Answer 2 · answered Mar 19 '13 at 17:24

3

If you are using DB2 for AS400, IMMEDIATE TRUNCATE TABLE will NOT work. The equivallent work around is to either:

DELETE FROM [tableName]

then if it is an auto increment equivalant column, run:

ALTER TABLE ALTER COLUMN RESTART WITH 1

OR the faster (most efficient way)

Pass a command to the system to clear out the Physical File

Java syntax:

CommandCall command = new CommandCall(new AS400(AS400SystemName, AS400JavaUser, AS400JavaPwd));
try {
    command.run("CLRPFM FILE(as400SchemaName/" + tableName + ")");

answered Mar 19 '13 at 17:24

ResourceReaper

555
2
10
27

As it turns out, there is a way to actually do the equivalent of TRUNCATE TABLE, not just deleting all the rows. The command syntax is not at all intuitive: http://stackoverflow.com/a/3616556/95852 – John Y Apr 14 '14 at 16:21
IBM finally decided to add the TRUNCATE statement to Db2 for i, as of version [7.2](https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafztruncate.htm). – John Y Aug 08 '19 at 18:07

score 1 · Answer 3 · answered Jun 09 '10 at 15:30

Which version of DB2 are you using? The truncate table command was introduced in DB2 v9 (at least on the mainframe, which appears to be what you're asking about based on the link).

You may have to resort to the delete from option although this article gives a stored procedure way of doing it in DB2 v8.

score 0 · Answer 4 · answered Dec 18 '14 at 16:48

DB2 on z/OS V10 Empty one of your tables: truncate table; followed by commit work. Ex. truncate temp; Someone else table: truncate owner.table Ex: truncate student.work ; I have not tried this on a linked DB2. I do not know if truncate node2.student.work; is good.

SQL for creating list of tables automatically. Substring (substr) used because column width for table name and creator are sooo long. Your values may be different.

select 'truncate table '||substr(creator,1,9)||'.'||substr(name,1,20)  
    from sysibm.systables
        where creator = 'Student';

score 0 · Answer 5 · answered Nov 09 '10 at 18:45

0

use truncate 'table_name' immediate

answered Nov 09 '10 at 18:45

vtokmak

1,496
6
35
66

score 0 · Answer 6 · answered May 17 '11 at 05:29

0

This is the exact reference documentation available for TRUNCATE in DB2 from 9.7 version

DB2 Reference for TRUNCATE

answered May 17 '11 at 05:29

raksja

3,969
5
38
44

Sattam · Answer 7 · 2021-03-24T09:50:51.213

0

in Java make sure it's the first statement in the transaction

edited Mar 24 '21 at 09:50

answered Mar 24 '21 at 08:47

Sattam

1
3

SQL Query to truncate table in IBM DB2

7 Answers7

Linked