Updating Db2 tables in succession

[deeptik]

Hi,

There is a table T1. For each row of T1, I need to update tables T2, T3 and T4 in succession i.e if T2 update is successful, then update T3 and if T3 successful then T4 and so on. What is the shortest way to implement this logic in cobol db2 program.

thanks.

[Natarajan]

Hi Deepti,

Welcome to mainframegurukul.com.

According to your requirement, you need to declare cursor and fetch record by
record from T1, and update T2,T3,T4 in sequence... i dont see any other shortest
way for this.

Let me know, if i did not understand your requirement correctly.

[deeptik]

Hi Natarajan,

What I want to know is can we do this entire set of processing i.e updating t2, t3 , t4 in sequence for each fetched row of T1 using nested cursors instead of using separate SQL update queries.

[Natarajan]

It is DB2 version dependent... what version of db2 you have in your shop.

[deeptik]

it is V8

[Natarajan]

In my view, you can avoid Declaring curosr on T1. You cannot avoid multiple update statements.


UPDATE T2
SET XXXX = YYYY
WHERE ID = ( SELET ID FROM T1 )


Other ways...

You may update all three tables togther by declaring updatable view on these
tree tables (T2,T3,T4 ).

OR

write a Stored procedure to do update task and call that SP.

[Anand_Hakke]

Please try below mthod. This is simple and good in performance point of view.

Create two triggers
trigger1- which will update table t3 after update on table t2.
trigger2 -which will update table t4 after update on table t3.

write update statement for table t2 by selecting from table t1.

the example of triggers and update statement are as below,
create trigger t2
after update of address on table2
referencing OLD as old NEW as new
FOR EACH ROW
mode db2sql
BEGIN ATOMIC
UPDATE table3 SET table3.address = new.address where table3.no = OLD.no;
END@


create trigger t3
after update of address on table3
referencing OLD as old NEW as new
FOR EACH ROW
mode db2sql
BEGIN ATOMIC
UPDATE table4 SET table4.address = new.address where table4.no = OLD.no;
END@


update table2 a set a.address =(select b.address from table1 b where b.no = a.no )@



Regards,
Anand.