DB2 Enterprise Stage - Truncate Option

[avenki77]

Hi All,

Does the 'Truncate' load method in DB2 enterprise stage, issue a 'delete from <table>' command behind the scenes (mean it is logged transaction)? Or does it do a non-logged delete?

If it does a logged delete, then is there a way to do a truncate of the table before loading it from DB2 enterprise stage?

Also, I tried to give the command alter table #db2_tgt_db#.#db2_tgt_table# activate not logged initially with empty table; in the 'Open Command' of the DB2 enterprise stage, but it errored out. Is it allowed to issue this statement in the open command?

thanks

[tehavele]

There is no specific truncate command in db2.
You can use following command in unix script.

Code: Select all

 import from /dev/null of del replace into #tablename#

Call that script in the before-job sub routine.

[avenki77]

Thanks tehavele!

But does the 'Truncate' load method in DB2 enterprise stage, actually issue a 'delete from <table>' command behind the scenes (mean it is logged transaction)?

Thanks
Venkatesh

[andrewn]

In DS 7.5 the "truncate" option makes the DB2 Enterprise stage use "LOAD REPLACE" so there is no "delete from..." statement issued.

You can specify if the load of the new data is logged through the "Load with logging" option in the DB2 Enterprise stage.

(Presumably v8 is similar?)

[arvind_ds]

There is no truncate statement in DB2. Although Truncate fired from datastage gets converted into DELETE at the back end.