Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    206

    Unanswered: DB2 z/OS Commit frequency on update statement

    Hello folks,

    i want to write a program or stored procedure to do the update and use cursor
    processing (with hold option so the cursor will not close when you commit).
    Then I want to commit every 5.000-10.000 updates.

    Can anyone give me an example what this should look like on z/OS?

    The update statement is ...

    UPDATE DB2PROD.TABLE1
    SET CAR_PRICE_ONLY = 'N'
    WHERE FK_CLIENT = '1'
    AND
    (CAR_PRICE_ONLY = ' '
    OR CAR_PRICE_ONLY IS NULL)

    Thank you for your help.

    Best regards,
    Christian

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello Christian,

    Something like this:
    Code:
    create procedure myupd(p_cmt_cnt int)
    disable DEBUG MODE
    begin
    declare SQLCODE int default 0;
    declare l_cmt_cnt int default 0;
    declare c1 cursor with hold for
    SELECT CAR_PRICE_ONLY FROM DB2PROD.TABLE1
    WHERE FK_CLIENT = '1'
    AND
    (CAR_PRICE_ONLY = ' '
    OR CAR_PRICE_ONLY IS NULL)
    for update of CAR_PRICE_ONLY;
    open c1;
    fetch c1;
    while (SQLCODE<>100) do
      update DB2PROD.TABLE1
      SET CAR_PRICE_ONLY = 'N'
      where current of c1;
      set l_cmt_cnt = l_cmt_cnt + 1;
      if (l_cmt_cnt >= p_cmt_cnt) then
        commit;
        set l_cmt_cnt = 0;
      end if;
      fetch c1;
    end while;
    if (l_cmt_cnt > 0) then
      commit;
    end if;
    close c1;
    end@
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •