First SQL procedure Build for Debug throws SQL state :42704

idea4good · #1 (**permalink**) 11-03-10, 00:30

Hi All:
I am java developer writing first Stored Procedure. When i do build for debug in Db2 Development Center tool I get the following error.

WWWTRN.UPDATE_MAG_SELECT_LIST - Build for debug started.
DROP SPECIFIC PROCEDURE WWWTRN.SQL101102150918100
WWWTRN.UPDATE_MAG_SELECT_LIST - Drop stored procedure completed.
[IBM][CLI Driver][DB2/LINUXX8664] SQL0444N Routine "*PILEMODE" (specific name "PSMD_SET_COMPMODE") is implemented with code in library or path ".../sqllib/function/unfenced/db2psmdr", function "*g" which cannot be accessed. Reason code: "4". SQLSTATE=42724

WWWTRN.UPDATE_MAG_SELECT_LIST - Build for debug failed.
WWWTRN.UPDATE_MAG_SELECT_LIST - Roll back completed successfully.

*******************************************
The Stored Procedure :
*******************************************

Code:

CREATE PROCEDURE WWWTRN.UPDATE_MAG_SELECT_LIST ()
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
 BEGIN
    DECLARE seqnbr INTEGER DEFAULT 1;
    Declare counter Integer;
    Declare seqData Integer;
    DECLARE c1 CURSOR FOR
        SELECT seq_nbr
        FROM   wwwtrn.mag_select_list as magSelectList
        WHERE  list_id = -11
        ORDER  BY option_text_displayed;
    set counter =  (select count (*) from  wwwtrn.mag_select_list WHERE  list_id = -11 );
    OPEN c1;
    fetch c1 into  seqData;
    WHILE seqnbr<counter DO
        UPDATE mag_select_list
               SET   seq_nbr = seqnbr;
--              WHERE CURRENT OF c1;
      SET seqnbr = seqnbr+1;
    END WHILE;
    CLOSE c1;
  END

I tried to run after simple build(i.e with out debug) it said the transaction log is full something like.Dont have the log stored so cant paste the exact error.

Marcus_A · #2 (**permalink**) 11-03-10, 01:12

I don't know anything about debug mode, but I have a few suggestions:

Fetch from the cursor until you hit SQLCODE 100, which is end of cursor. Skip the select count(*) to see how many times you will loop.
If you updating more than a few thousand rows (which you may be since your transaction logs are full), then you can do a commit every so often (like every 1000 updates). You will need to open your cursor WITH HOLD option so it will not close when you do a commit.
Do you really need to have an ORDER BY in the cursor? Does it matter what order you do the updates in? This makes the performance worse, and more importantly hold many more locks on the rows which could cause contentions with other SQL statements.

tonkuma · #3 (**permalink**) 11-03-10, 11:38

If the procedure is not for your study,
I want to recommend not to use cursor.

The body of the procedure would be written in an update statement, like this ...

Code:

UPDATE
(SELECT msl.*
      , ROW_NUMBER()
          OVER(ORDER BY option_text_displayed) AS seqnbr
  FROM  wwwtrn.mag_select_list AS msl
  WHERE list_id = -11
)
   SET seq_nbr = seqnbr
;

Marcus_A · #4 (**permalink**) 11-03-10, 12:50

Quote:

Originally Posted by tonkuma

If the procedure is not for your study,
I want to recommend not to use cursor.

That might work OK, but in situations where there are a large number of rows to update it could have a serious negative impact on concurrency by holding a lot of exclusive locks at one time.

tonkuma · #5 (**permalink**) 11-03-10, 23:11

If you want to commit after every n rows updated, the procedure body may be like this...

(commit after every 500 rows updated)

Code:

DECLARE last_option_text_displayed VARCHAR(100) DEFAULT '';
                                /* data type may be different */
DECLARE last_seq_nbr               INTEGER      DEFAULT 0;
DECLARE at_end                     SMALLINT     DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
        SET at_end = 1;

REPEAT
   SELECT MAX(option_text_displayed)
        , MAX(seqnbr)
    INTO( last_option_text_displayed
        , last_seq_nbr
        )
    FROM  OLD TABLE
          (UPDATE (SELECT msl.*
                        , ROW_NUMBER()
                            OVER(ORDER BY option_text_displayed)
                          + last_seq_nbr AS seqnbr
                    FROM  wwwtrn.mag_select_list AS msl
                    WHERE list_id = -11
                      AND option_text_displayed > last_option_text_displayed
                    ORDER BY option_text_displayed
                    FETCH FIRST 500 ROWS ONLY
                  )
              SET seq_nbr = seqnbr
          )
   ;
   COMMIT;

   UNTIL at_end > 0
   END REPEAT;

Marcus_A · #6 (**permalink**) 11-04-10, 21:15

Quote:

Originally Posted by tonkuma

If you want to commit after every n rows updated, the procedure body may be like this...

That may work OK regarding commits, but since it has an order by clause it will not perform as well as an old-fashioned cursor.

The best way to do it where high concurrency is needed is to open a cursor WITH HOLD and WITH UR, then update or delete the rows you want using the PK (retrieved via the cursor) without the WHERE CURRENT OF CURSOR, and do intermediate commits on a frequency needed to minimize lock contention.

tonkuma · #7 (**permalink**) 11-05-10, 01:09

Quote:

That may work OK regarding commits, but since it has an order by clause it will not perform as well as an old-fashioned cursor.

Cursor also needs order by clause depending on the requirements.
See DECLARE c1 CURSOR in the stored procedure in OP.
(The stored procedure updates seq_nbr ordered by option_text_displayed.)

Appropriate index may be neccesary to avoid sort.

Quote:

*******************************************
The Stored Procedure :
*******************************************

Code:

CREATE PROCEDURE WWWTRN.UPDATE_MAG_SELECT_LIST ()
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
 BEGIN
    DECLARE seqnbr INTEGER DEFAULT 1;
    Declare counter Integer;
    Declare seqData Integer;
    DECLARE c1 CURSOR FOR
        SELECT seq_nbr
        FROM   wwwtrn.mag_select_list as magSelectList
        WHERE  list_id = -11
        ORDER  BY option_text_displayed;
    set counter =  (select count (*) from  wwwtrn.mag_select_list WHERE  list_id = -11 );
    OPEN c1;

.....


  END

Marcus_A · #8 (**permalink**) 11-05-10, 13:59

Quote:

Originally Posted by tonkuma

Cursor also needs order by clause depending on the requirements.
See DECLARE c1 CURSOR in the stored procedure in OP.
(The stored procedure updates seq_nbr ordered by option_text_displayed.)

Appropriate index may be neccesary to avoid sort.

I saw the ORDER BY in the Declare Cursor by the OP, but as I previously stated, it is very unlikely that it is needed if using a cursor.

tonkuma · #9 (**permalink**) 11-05-10, 16:12

Quote:

I saw the ORDER BY in the Declare Cursor by the OP, but as I previously stated, it is very unlikely that it is needed if using a cursor.

Whether it is likely or not, cursor without ORDER BY clause is another problem.

But, in my limited experience, many times there were some ways to exclude updated rows from succesive update statement(typicaly using primary key).

If you have another example of bulk update using cursor, I want to see the example and want to study a way to multi row update with commit.

Internet Services

Web Development

Digital Marketing

Consumer Tech