Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Unanswered: First SQL procedure Build for Debug throws SQL state :42704

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know anything about debug mode, but I have a few suggestions:

    1. 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.
    2. 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.
    3. 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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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;

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    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.
    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
    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.
    *******************************************
    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

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 11-06-10 at 00:16. Reason: "rare" ---> "likely"

Tags for this Thread

Posting Permissions

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