Urgent Help Required in DB2 stored Procedure

ramandeep13 · #1 (**permalink**) 06-11-10, 02:28

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

tonkuma · #2 (**permalink**) 06-11-10, 03:25

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.

Quote:

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

What error code and text did you got?

stolze · #3 (**permalink**) 06-11-10, 06:20

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.

ramandeep13 · #4 (**permalink**) 06-12-10, 06:00

hii Stolze,

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

tonkuma · #5 (**permalink**) 06-12-10, 12:57

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.

Marcus_A · #6 (**permalink**) 06-13-10, 12:51

The above SP seems a lot more complicated than using a cursor to me.

tonkuma · #7 (**permalink**) 06-13-10, 14:01

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;

tonkuma · #8 (**permalink**) 06-13-10, 14:46

Quote:

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.

stolze · #9 (**permalink**) 06-14-10, 08:01

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.

tonkuma · #10 (**permalink**) 06-14-10, 14:37

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?

tonkuma · #11 (**permalink**) 06-15-10, 12:45

I think that (as I wrote before):

Quote:

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

Quote:

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@

Lenny77 · #12 (**permalink**) 06-15-10, 14:53

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