Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: Urgent Help Required in DB2 stored Procedure

    Hello friends,

    I have written a procedure in DB2 9.5 that takes two arguments as input.
    It fetches the bulk record in cursor (say 5 million) and perform two update operation for each record.

    My worry is that if I run this for all 5 million records , transaction logs will full.

    And When I am trying to give commit after each update , it is throwing an error.

    Is there any way to give a commit after update.
    After googling i found one option ....declare cursor with hold option.

    But I don't understand what exactly it was.....so please post your valuable inputs..

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Too general and vague to say something practical.
    But, I thought there were some examples of procedure in which commit was given after every n rows updated.

    And When I am trying to give commit after each update , it is throwing an error.
    What error code and text did you got?

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you execute a COMMIT, all opened cursors will be closed. You can't use the cursor any longer. An exception to that are cursors that were declared with the WITH HOLD option. Those cursors are held open over a COMMIT (but never over a ROLLBACK). Thus, you have to declare your cursor using that option, do the COMMIT when needed (maybe only every 1000th record) and then do the next FETCH operation. Note that a WITH HOLD CURSOR is not positioned on any row after the COMMIT operation; you need to do the FETCH first.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2009
    Posts
    6
    hii Stolze,

    Thanks for ur valuable inputs.This will surely help me a lot.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, I haven't seen so many examples, I have never seen the necessity of cursor in an SQL procedure.

    I saw the neccesity of cursor only in external procedures or host language programs which process external(other than DB2 tables) resoruces(typically OS files) with DB2 table(s).
    In other words, all cursors I saw in SQL procedure could be rewritten without using cursor.

    Here is an example which commit every bulk_unit(given by a parameter) rows updated.

    Table to be updated:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE update_test
    ( pk     INTEGER NOT NULL PRIMARY KEY
    , amount INTEGER
    , desc   VARCHAR(150)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO update_test
    WITH gen_repeat(n , pk , amount , desc) AS (
    VALUES (1 , 1 , 0 , '' )
    UNION ALL
    SELECT n  + 1
         , pk + 1 + MOD(n , 2)
         , 0 , ''
      FROM gen_repeat
     WHERE n < 100000
    )
    SELECT pk , amount , desc
      FROM gen_repeat;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample SQL procedure
    (You can use FETCH FIRST n ROWS ONLY clause, if bulk_unit is a fixed number and not given by a parameter.):

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE
     PROCEDURE bulk_update_test
     ( IN call_id    VARCHAR(3)
     , IN bulk_unit  INTEGER
     , IN max_repeat INTEGER
     , IN start_pk   INTEGER
     , IN operation  VARCHAR(1)
     , IN increment  INTEGER
     )
     MODIFIES SQL DATA
     NO EXTERNAL ACTION
    BEGIN
    DECLARE repeat_cnt , last_pk , updated_cnt
            INTEGER;
    
    SET (repeat_cnt , last_pk      , updated_cnt)
      = (1          , start_pk - 1 , 1          );
    loop_a:
    WHILE repeat_cnt <= max_repeat
      AND updated_cnt > 0
    DO
       SELECT MAX(pk) , COUNT(*)
         INTO last_pk , updated_cnt
         FROM FINAL TABLE (
              UPDATE update_test
                 SET amount
                   = CASE operation
                     WHEN '+' THEN
                          amount + increment
                     WHEN '-' THEN
                          amount - increment
                     WHEN '*' THEN
                          amount * increment
                     ELSE amount
                     END
                   , desc
                   = rightmost(
                     desc || 'ID(' || call_id
                          || '): new amount= '
                          || VARCHAR(amount) || ' ' || operation || ' ' || VARCHAR(increment)
                          || ' (bulk unit='  || VARCHAR(bulk_unit)
                          || ', repeat cnt=' || VARCHAR(repeat_cnt)
                          || ', start pk='   || VARCHAR(start_pk)
                          || '); '
                     , 150)
               WHERE pk IN
                     (SELECT pk
                        FROM (SELECT pk
                                   , ROW_NUMBER() OVER(ORDER BY pk) rn
                                FROM update_test
                               WHERE pk > last_pk
                             ) q
                       WHERE rn <= bulk_unit
                     )
              ) r
        ;
        SET repeat_cnt = repeat_cnt + 1;
        COMMIT;
        END WHILE loop_a;
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample execution:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CALL bulk_update_test
    ('A' , 3 , 2 , 7 , '+' , 13);
    ------------------------------------------------------------------------------
    
      Return Status = 0
    Results:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM update_test
    FETCH FIRST 15 ROWS ONLY;
    ------------------------------------------------------------------------------
    
    PK          AMOUNT      DESC                                                                                                                                                  
    ----------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------
              1           0                                                                                                                                                       
              3           0                                                                                                                                                       
              4           0                                                                                                                                                       
              6           0                                                                                                                                                       
              7          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=1, start pk=7);                                                                                    
              9          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=1, start pk=7);                                                                                    
             10          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=1, start pk=7);                                                                                    
             12          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=2, start pk=7);                                                                                    
             13          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=2, start pk=7);                                                                                    
             15          13 ID(A): new amount= 0 + 13 (bulk unit=3, repeat cnt=2, start pk=7);                                                                                    
             16           0                                                                                                                                                       
             18           0                                                                                                                                                       
             19           0                                                                                                                                                       
             21           0                                                                                                                                                       
             22           0                                                                                                                                                       
    
      15 record(s) selected.
    UDF rightmost used in the procedure:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION rightmost
     ( str VARCHAR(4000) , len INTEGER )
     RETURNS VARCHAR(4000)
     CONTAINS SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
    SUBSTR( str , MAX( LENGTH(str) - len + 1 , 1 ) )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The above SP seems a lot more complicated than using a cursor to me.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    BUt, the above SP would run with better performance than using cursor.

    One notice is that set operations(or bulk operations) of RDB usually would show better performance than one by one operations(by using cursor).

    Another point is many people used to loop operations of objects(e.g. files, arrays) in programming.

    I think that it is really a restriction of most programming languages in which loops are neccesary even in a very simple operation like initializing an array variable.
    I can remember PL/I language in which some array operations like "array <op> scalar" are supported.
    For example, you can initialize every elements of array_variable[i] to zero by
    array_variable = 0;

    without coding...
    Do i = 1 to 100 by 1;
    array_variable[i] = 0;
    End;

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The above SP seems a lot more complicated than using a cursor to me.
    The code would be longer by using cursor.

    You would be neccesary to code more statements, like...
    DECLARE fetch_count, DECLARE cursor, DECLARE (not found) condition, SET fetch_count to zero, OPEN, FETCH, handling of not found condition, IF(fetch_count exceed bulk_unit), SET(FETCH_count to zero), CLOSE, etc.
    in contrast with sinple WHERE clause in an UPDATE statement and without using variables last_pk and updated_cnt.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I agree with Marcus that using the procedural approach by processing one record at a time is easier to maintain because it is more natural to most humans/developers. But I also agree with Tonkuma that the best performance can typically be achieved by using a set-oriented approach, i.e. push the loop into the database engine and avoiding a lot of context switches this way. In the end, it comes down to priorities: if performance is of utmost importance, then a bit more complexity is justified (if it is well documented). But if easy maintenance is preferred at the costs of slightly slower execution times, then a different way may be preferable.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you compared
    1) a Java program which was fully utilized Java specific functionarity(e.g. Object Oriented programming)
    and
    2) a Java program which was designed and coded like C(not C++),
    do you think 2) is easier to maintain because it is more natural to most humans/developers?
    Last edited by tonkuma; 06-15-10 at 09:59. Reason: Add "designed and "

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that (as I wrote before):
    The code would be longer by using cursor.

    Here are simple examples comparing SPs with/without using a cursor.

    Both requirements are same:
    Update first (bulk_unit(fixed to 5) * max_repeat) rows beginning from pk >= start_pk (order of pk) in the table update_test,
    taking COMMIT after every bulk_unit rows updated.

    The requirement was slightly modified from
    Sample SQL procedure
    (You can use FETCH FIRST n ROWS ONLY clause, if bulk_unit is a fixed number and not given by a parameter.):

    By extracting procedure bodies(lines between BEGIN and END@) and comment on different lines,
    bulk_update_test specific lines were 15
    and
    cursor_update_test specific lines were 23.


    Followings are the actual code(omitted SET clause in UPDATE statement).
    (I have not so many experience in using cursor.
    If there are shorter or smarter cursor example for the requirement, I want to see the sample eagerly.)

    Sample bulk_update_test SP:
    Code:
    BEGIN
    DECLARE repeat_cnt
          , last_pk , updated_cnt                     -- bulk
            INTEGER;
    
    SET ( repeat_cnt
        , last_pk      , updated_cnt )                -- bulk
      = ( 1
        , start_pk - 1 , 1           );               -- bulk
    
    loop_a:
    WHILE repeat_cnt <= max_repeat
      AND updated_cnt > 0                             -- bulk
    DO
       SELECT MAX(pk) , COUNT(*)                      -- bulk
         INTO last_pk , updated_cnt                   -- bulk
         FROM FINAL TABLE (                           -- bulk
              UPDATE update_test
                 SET amount
                     .....
               WHERE pk IN                            -- bulk
                     (SELECT pk                       -- bulk
                        FROM update_test              -- bulk
                       WHERE pk > last_pk             -- bulk
                       ORDER BY pk                    -- bulk
                      FETCH FIRST 5 ROWS ONLY         -- bulk
                     )                                -- bulk
              ) r                                     -- bulk
        ;
    
        COMMIT;
        SET repeat_cnt = repeat_cnt + 1;
        END WHILE loop_a;
    
    END@
    Sample cursor_update_test SP:
    Code:
    BEGIN
    DECLARE repeat_cnt
          , fetch_cnt , v_amount                      -- cursor
            INTEGER;
    DECLARE v_desc VARCHAR(150);                      -- cursor
    
    DECLARE not_found CHAR(1) DEFAULT 'N';            -- cursor
    
    DECLARE c1 CURSOR WITH HOLD                       -- cursor
        FOR SELECT amount , desc                      -- cursor
              FROM update_test                        -- cursor
             WHERE pk >= start_pk                     -- cursor
             ORDER BY pk                              -- cursor
            FOR UPDATE                                -- cursor
            ;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND            -- cursor
            SET not_found = 'Y';                      -- cursor
    
    SET ( repeat_cnt
        , fetch_cnt )                                 -- cursor
      = ( 1
        , 0         );                                -- cursor
    OPEN c1;                                          -- cursor
    
    loop_a:
    WHILE repeat_cnt <= max_repeat
    DO
       FETCH c1 INTO v_amount , v_desc;               -- cursor
       IF not_found = 'Y' THEN                        -- cursor
          LEAVE loop_a;                               -- cursor
          END IF;                                     -- cursor
       SET fetch_cnt = fetch_cnt + 1;                 -- cursor
       UPDATE update_test
          SET amount
              .....
        WHERE CURRENT OF c1                           -- cursor
       ;
    
       IF MOD(fetch_cnt , 5) = 0 THEN                 -- cursor
          COMMIT;
          SET repeat_cnt = repeat_cnt + 1;
          END IF;                                     -- cursor
       END WHILE loop_a;
    
       CLOSE c1;                                      -- cursor
    END@
    Last edited by tonkuma; 06-15-10 at 13:51.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb ROWSET POSITIONING cursor

    You can use also AREA for FETCH dummy value from cursor:

    Code:
    DECLARE cursor-name CURSOR WITH HOLD WITH ROWSET POSITIONING
    FOR SELECT 1 from your_table WHERE... ... For update of....;
    
    OPEN cursor-name; 
    
    FETCH cursor-name into your_area NEXT ROWSET;
    
    UPDATE your_table SET.... WHERE CURRENT OF cursor-name;
    
    COMMIT; (after each update)
    
    when SQLCODE = 100 ==> (last update)
    
    CLOSE cursor-name;
    Lenny

  13. #13
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by stolze View Post
    by using a set-oriented approach
    This is a database forum. Any other approach is 2nd best....

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I disagree. The best approach is the one that matches best with the requirements - DBMS technologies are just one aspect there.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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