Bulk delete from table using db2 connector?

[karumudi7]

Hi,
I have one table with 70 Million recs. I need to make fresh load into the table using a file have 72 Million recs.

So I created a job; Seq_file--->Transformer--->db2_connector

In db2_connector I tried with:
Write Mode- Insert & Table Action-Truncate - This caused by DB transaction log full while deleting the data from table.

I tried one more with Before routine:
db2 "connect to DBNAME user USERNAME using Pwd"; db2 "load from /dev/null of del replace into TABLE_NAME NONRECOVERABLE"

and made the db2 connector as Write mode - Insert & table action - Append.

In this case, the first query executed successfully but for the second one I got "SQL1024N A database connection does not exist. SQLSTATE=08003"

Anyother way where I can achieve this?

[MT]

Hi karumudi7,

you were close to the solution already.

Try following settings in the DB2 Connector
Write mode: Insert
Table action: Truncate
Generate truncate statement at runtime: No
Truncate statement: truncate tabe_xxx reuse storage immediate

This is assuming you have at least DB2 9.7 running.
Otherwise use following statement as Truncate statement:
alter table table_xxx activate not logged initially with empty table

[karumudi7]

The statement generated is ALTER TABLE TABLE_NAME ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE.

The ALTER TABLE statement failed, most likely due to insufficient user privileges. A DELETE statement will be executed instead.

Due to delete query execution I am getting DB Tx log full.

[ray.wurlod]

Then get privileges to effect that particular ALTER TABLE statement.