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.
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.
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.
WHERE STATU = 2;
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.