SQL Query to truncate table in IBM DB2

Question

Hi,

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 SQL Error: 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

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.

Answer 2

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