cancel
Showing results for 
Search instead for 
Did you mean: 

How to delete all rows from a target table

0 Kudos

How can I delete all the rows from the target table without filling up the db2 logs? I tried checking the box on the target table that says to delete all rows, but our db2 logs end up getting blown out.

Is there another way to empty the table/delete all rows without logging?

Thanks.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Is it possible to delete data from Data Services?

There is an option to "Delete data from table before loading" from DS, but I have to run a Job.

I just want to delete the data from the table target.

How to do that in DS without having to use SQL or scripts?

Former Member
0 Kudos

Use the SQL Command "TRUNCATE". This is a DDL statement that doesn't bother with the actual data (a DML command).

werner_daehn
Active Contributor
0 Kudos

Does DB2 finally support a truncate command?

Any DB2 internal reasons why people would not use that?

I see, supported since DB2 v9.1......Will file a bug immediately...

werner_daehn
Active Contributor
0 Kudos

ADAPT01160844 created on 3rd Dec. 2008

peter_houston
Explorer
0 Kudos

The easiest way I've found is the 'Import from /dev/null..' statement. It performs what is basically is a truncate on the target table.

Unix:

IMPORT FROM /dev/null OF DEL REPLACE INTO schema.table_name

Windows:

IMPORT FROM NUL OF DEL REPLACE INTO schema.table_name

I think it's best to issue it in a script with the sql() function before launching the Data Flow.

Hope this helps.

Regards,

Peter