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

Answer 1

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