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
where
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
end-if
if ws-col2 = 'X'
move 'Y' to ws-col2
end-if
if ws-col3 = 'X'
move 'Y to ws-col3
end-if
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
commit
update-cnt = 0
end-if
close cursor
select * from TABLEA
where
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
end-if
if ws-col2 = 'X'
move 'Y' to ws-col2
end-if
if ws-col3 = 'X'
move 'Y to ws-col3
end-if
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
commit
update-cnt = 0
end-if
close cursor
