Delete rows before inserting

guysma · Post by **guysma** » Wed Jan 30, 2008 3:54 am

Hi, I'm looking for a way to delete all the rows of a certain DB2 table,
And then insert new rows from a flat file. all this only in DS and not in the JCL by loading a dummy file before
Any ideas?

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Wed Jan 30, 2008 6:18 am

You can do by selecting the option "Clear the table and then Insert rows" in DB2 stage

guysma · Post by **guysma** » Wed Jan 30, 2008 6:40 am

MOHAMMAD.ISSAQ wrote:You can do by selecting the option "Clear the table and then Insert rows" in DB2 stage

this option does not exist in the mvs edition
the only option near to it is replace exsisting rows completly , but this option delete the table for each row again and again

sud · Post by **sud** » Wed Jan 30, 2008 3:45 pm

Hi I don't know about the MVS edition, but check for "open command" property and if it is available, you can put the table truncate command there. This open command will be processed before the row level transactions are made.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 30, 2008 4:27 pm

"Replace existing rows completely" performs a DELETE then an INSERT on each row processed. It does not clear the table.

In MVS edition you don't really have an alternative, unless you want to create a routine to delete all rows from the table.