CODEWITHSUNDEEP
syntaxdb2truncate
Cshah
Cshah

Reputation: 5840

SQL Query to truncate table in IBM DB2

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

Upvotes: 9

Views: 71819

Answers (7)

Sattam
Sattam

Reputation: 1

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

Upvotes: 0

TechnoCaveman
TechnoCaveman

Reputation: 1

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';

Upvotes: 0

Nick Vallely
Nick Vallely

Reputation: 1396

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

Upvotes: 15

ResourceReaper
ResourceReaper

Reputation: 565

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 + ")");

    Upvotes: 3

    raksja
    raksja

    Reputation: 4029

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

    DB2 Reference for TRUNCATE

    Upvotes: 0

    vtokmak
    vtokmak

    Reputation: 1506

    use truncate 'table_name' immediate

    Upvotes: 0

    paxdiablo
    paxdiablo

    Reputation: 881133

    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.

    Upvotes: 1

    Related Questions