Delete+Insert (Move) DB2 OLD TABLE

Question

I have two tables TARGET and SOURCE. I need to UPDATE rows of SOURCE table and insert those updated rows into TARGET table and then delete original rows from SOURCE. Currently I'm first updating SOURCE table completely through SP and then doing Move operation in another SP.

BEGIN P1:
insert into TARGET(select * from SOURCE where col=someValue)
delete from SOURCE where col=someValue;
END P1

I also tried something like

insert into TARGET(SELECT * FROM OLD TABLE(DELETE FROM SOURCE WHERE col=someValue))

but this didn't work in SP.

I think this is common scenario e.g. History/Archive Table and must be having solution in DB2. Can anybody tell how can I achieve this without affecting performance? I mean SP should not take long time to run. Also can I remove redundant update SP. Instead Can I directly insert updated rows into TARGET and delete corresponding rows from SOURCE. Also I've been advised to make the Delete and Insert operation in single transaction. Will that cause any performance loss?

Transactions always have a performance cost. Period. And yes, this NEEDS to be in a transaction, or you run some terrible risks. Also, please note that your current 'move' SP, even if it's in a transaction, will NOT behave 'safely'. I'm afraid we need to know more to be sure, but you probably can directly update Target without the intermediate table. — Clockwork-Muse
Yes I want to insert updated rows and delete old rows without redundant updates on old rows. The current update sp using merge is taking ver less time to update old table and i don't want to lose performance by introducing some single row insert and delete operation in the sp. So i'm looking for something that will maintain the batch update of target table and corresponding deletes from old table — techknowfreak
What type of column is col, and must it be unique? If it's not going to be unique, you need to use some sort of flag value, so you can tell which rows have been inserted into TARGET, or you risk inserting rows into SOURCE after the INSERT, but before the DELETE... with the expected results. Although, don't discount the possibility that the SP may still perform acceptably without attempting it. — Clockwork-Muse

bhamby bhamby · Accepted Answer · 2012-04-27T22:19:14

If I'm understanding your question correctly, and these are all updates, you could create a TRIGGER to put the old rows into an audit table (though, it's not just UPDATEs that you can define triggers for. INSERT and DELETE are triggerable, as well). Something like:

CREATE TRIGGER AUDIT_SOURCE
 AFTER UPDATE ON SOURCE
 REFERENCING OLD AS O
 FOR EACH ROW
 BEGIN ATOMIC
   INSERT INTO TARGET
     VALUES (O.Col1, O.Col2, ..., O.ColN);
 END

Delete+Insert (Move) DB2 OLD TABLE

1 Answers