CODEWITHSUNDEEP
transactionsdb2db2-luw
Geoff
Geoff

Reputation: 11

proper update statement in DB2 to avoid transaction log full

I would like to update a table with over 2 million records in DB2 running on unix. Is there a way to update in batch size say 5000 rows a time? In Sybase, I can use "set rowcount 5000" like the following:

set rowcount 5000
while ( 1=1 )
begin
 begin tran
  update X set ... where ...
  if (@@error != 0)
    begin
        rollback tran
        return 1
    end
 commit tran

if(  @@rowcount < 5000 ) break
end

set rowcount 0

How to do this in DB2? The purpose is try to avoid transaction log full leading to update failure.

Upvotes: 1

Views: 4196

Answers (1)

mustaccio
mustaccio

Reputation: 18945

You have a couple of options, both involving compound SQL. If there is a way for you to determine what records have been updated and what have not, you could write something like this:

BEGIN
  WHILE EXISTS (SELECT 1 FROM yourtable WHERE <condition to find records to update>)
  DO
    UPDATE (SELECT * FROM yourtable WHERE <condition> FETCH FIRST 5000 ROWS ONLY)
      SET ...; -- updating 5000 rows at a time
    COMMIT;
  END WHILE;
END

If you cannot easily distinguish between the updated and not yet updated rows, you could do something like this:

BEGIN
  DECLARE i INT DEFAULT 0;
  FOR l AS cur CURSOR WITH HOLD FOR 
   SELECT * FROM yourtable WHERE ... FOR UPDATE
  DO
    UPDATE yourtable SET ... WHERE CURRENT OF cur;
    SET i = i + 1;
    IF MOD(i, 5000) = 0 THEN
      COMMIT; -- every 5000 rows
    END IF;
  END FOR;
  COMMIT; -- last set, may be less than 5000 rows
END

The latter option obviously is less efficient, because it updates rows one by one, as opposed to sets of 5000 rows.

Upvotes: 2

Related Questions