Mass update on an extensively used DB2 table

IBM's flagship relational database management system

Mass update on an extensively used DB2 table

Postby letmelive » Thu Apr 11, 2013 3:55 am

I am working on a mission to correct data(1 or more column) in some DB2 tables. These tables are heavily used across applications and > 500K rows(30% of the total) are to be updated. I am trying to figure out the best way to do this. I am a mainframe programmer and hence thinking of writing COBOL-DB2 program. I have given the pseudo-code below. I am not sure if can hold 500K rows until last row has been updated. How long it will take to update all the rows, how is it going to affect concurrency, Am I on right path or do I need to follow some other method? - I need some idea on this.
Declare c1 cursor with hold for
select * from TABLEA
   col1 = 'X'  or col2 = 'X'  or col3 = 'X'
for update of col1,col2,col3

Open cursor

fetch col1,col2,col3,... into ws-col1,ws-col2,ws-col3
  when sqlcode = 0
    if ws-col1 = 'X
       move 'Y' to ws-col1
    if ws-col2 = 'X'
       move 'Y' to ws-col2
    if ws-col3 = 'X'
       move 'Y to ws-col3
    Update TABLEA
       set col1 = :ws-col1,
            col2 = :ws-col2,
            col3 = :ws-col3
     where current of c1
     update-cnt = update-cnt + 1

If update-cnt > 500
   update-cnt = 0

close cursor
Posts: 20
Joined: Thu Oct 04, 2012 1:27 am
Has thanked: 6 times
Been thanked: 0 time

Re: Mass update on an extensively used DB2 table

Postby BillyBoyo » Thu Apr 11, 2013 4:15 am

Perhaps have a chat with your DBA(s).
Global moderator
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Mass update on an extensively used DB2 table

Postby dick scherrer » Thu Apr 11, 2013 7:45 pm


When you talk with your dba(s), also mention that the table can be unloaded, the data modified, and the table reloaded with the corrections very quickly.

This approach often runs faster than an update "on the fly". This could be done during a maintanance window or when the db is offline for backups.
Hope this helps,
User avatar
dick scherrer
Global moderator
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Mass update on an extensively used DB2 table

Postby pmartyn » Wed Apr 24, 2013 4:03 am

Please refer to Dick's method of converting the data. It is a very smart way of performing this task. If you 'must' perform this change online (as you seemed to mention in your question) please remember that a COBOL program uses the FETCH with a cursor that returns only one row. You can them update it and move on to the next row. After a number of updates perform a COMMIT that releases the updates and all locks you used.

Because you mentioned that this was a high access database remember to keep the COMMIT count low so COMMITs occur frequently. I do not make this suggestion lightly. During your process you will be using IX and X locks presumably with a prefetch. In English this means that the database will be 'unavailable' to others.

Please look at Dick's suggestion as your primary alternative.

These users thanked the author pmartyn for the post:
letmelive (Fri May 10, 2013 3:18 am)
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times

Return to DB2


  • Related topics
    Last post