How do you Mass-Update the Master-Detail table structure without any SQL UPDATE? by Cuneyt Goksu

Cuneyt Goksu with Ember Brooks and Melanie Stopfer at IDUG NA in May 2013.jpg

There are circumstances that some columns of a very large table need to be updated for various reasons. If this requirement is triggered by Application Developers and Business Users, generally and unfortunately, an SQL UPDATE program is written. Hopefully it COMMITS well frequently, it might create vast amount of logging, consumes too much CPU, system resources, elapsed time. DB2 Utilites are very well matured to handle those cases. There are very obvious advantages doing such a mass-Update with DB2 utilites such as fast, trusted and less resource consumtion and easier to implement then writing a program.

 

Let’s go thru an example

There are two tables, MASTER and DETAY.

 

MASTER (ID BIGINT NOT NULL WITH DEFAULT, 

STATU SMALLINT NOT NULL WITH DEFAULT,     

C1 INTEGER NOT NULL WiTH DEFAULT,         

C2 INTEGER NOT NULL WiTH DEFAULT)                 

 

DETAY (ID BIGINT NOT NULL WITH DEFAULT,          

C4 INTEGER NOT NULL WiTH DEFAULT,         

C5 INTEGER NOT NULL WiTH DEFAULT)         

 

MASTER has one unique KEY on ID Column

DETAY has ID Column and Foreign Key of MASTER table.

  

The requirement:

ID Column in every table will be updated as follows

If the STATU is equal to 1, ID=ID+1000000000

If the STATU is equal to 2, ID=ID+2000000000

Pay attention that STATU column is only in MASTER table and not in DETAY table.   

 

The Solution:

 

1) Unload Data from DETAY table with the following SQL Statements.

 

SELECT DETAY.ID+100000000000000000, C4,C5    

FROM MASTER, DETAY                           

WHERE MASTER.ID=DETAY.ID AND MASTER.STATU = 1;

 

SELECT DETAY.ID+200000000000000000, C4,C5    

FROM MASTER, DETAY                           

WHERE MASTER.ID=DETAY.ID AND MASTER.STATU = 2;

 

Those two datasets produced with DSNTIAUL  with SQL option from DETAY table contains UPDATED ID Column that are related with the right STATU flag.

 

2) Unload Data from MASTER table with the following SQL Statements.

 

SELECT ID+200000000000000000,STATU,C1,C2

FROM MASTER                            

WHERE STATU = 2;                        

 

SELECT ID+100000000000000000,STATU,C1,C2

FROM MASTER                            

WHERE STATU = 1;                   

 

3) Now we have four datasets for two tables. It’s easy to LOAD back this data into correponding tables. But be careful the first dataset of each table must be LOADed with REPLACE option and the second dataset must be LOADed with RESUME YES option.

 

 This approach is much easier to implement and less resource intensive.

 

Author: Cuneyt Goksu, IDUG Volunteer since 2003.

Recent Stories
The IDUG Board of Directors Slate for 2017-2018 is Open for Voting

Open Board of Directors Position - Apply Before the January 13, 2017 Deadline

Enable Social Login