I need to copy data from an existing column A in a Db2 table to a newly added column B for all the existing records. Once done, I need to space out the Column A values. There are other columns as well in the table.
Column A CHAR(18) NOT NULL
Column B CHAR(20) NOT NULL
As per DBA suggestion, we shouldn't use SPUFI because we will be changing too many pages without the commit. DB2 may abend the SPUFI if the updated # of pages reach the system allowable limit. Hence we need to write a one-time DB2 batch job to do this and make sure to put the COMMIT logic for say every 1000 updates.
Could anyone provide any hint on how to accomplish this?
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
One easy to do what you need is to write a bit of cobol code that declares a cursor, reads and updates the rows (moving the colA value to colB and spaces to colA in the same update) , and issues a checkpoint periodically.
You will want (or ask your dba) to create a backup of the table before you update it.