Disadvantage of delaying commits

[jerome_rajan]

Hi,

We are loading about 40 million records into a DB2 table using DataStage. If the load fails midway, we would like to rollback whatever changes were loaded in that session.
The load is incremental in nature. The only way I can think of is to set the transaction size to 0. But I'm not sure if this is going to come at a performance expense.
If delaying the commit till the end is going to cause accumulation of logs/rollback information, is there any other way to fulfill our requirement?

Thank You

[ray.wurlod]

Your DBA will almost certainly feel angst at transactions of this size.

The usual technique is to include column(s) in the target tables that indicate a unique id of the run that loaded them, or of the most recent update, and possibly a timestamp "last updated". Using these columns you can recover any rows that were committed but, in the light of subsequent events, ought not to have been.

Note that, if the run includes updates or deletes, this technique requires that you have to obtain a "before image" of those rows in the target table that correspond to the keys in your new load set. The easiest way to obtain that is to load just the keys into a temporary table in the target database and select the inner join of that and the target table to get your required "before image".