Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1

    Unanswered: Stored procedure: How to create continue handler for OK message?

    Hi,
    in DB2 v11 fp2 on Linux I have a requirement to write a stored procedure to log SQL actions into admin.log table.

    Simplified sample:
    Code:
    CREATE TABLE ADMIN.TAB1 (COL TIMESTAMP);
    CREATE TABLE ADMIN.TAB2 (COL TIMESTAMP);
    CREATE TABLE ADMIN.LOG (ID INT, ERR_CODE CHAR(5));
    
    CREATE OR REPLACE PROCEDURE ADMIN.TEST
    (
    OUT P_ID INT,
    OUT P_RETURN_CODE INT
    )
    LANGUAGE SQL
    SPECIFIC ADMIN.TEST
    BEGIN
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                SET P_RETURN_CODE = SQLCODE;
                INSERT INTO ADMIN.LOG (ID, ERR_CODE) VALUES (P_ID, P_RETURN_CODE);
            END;
        SET P_RETURN_CODE = 0;
    
        SET P_ID = 1;
        INSERT INTO ADMIN.TAB1 VALUES (CURRENT_TIMESTAMP);
    
        SET P_ID = 2;
        -- the value 'x' is going to produce an error, because timestamp data type is expected
        INSERT INTO ADMIN.TAB2 VALUES ('X');
    
        SET P_ID = 3;
        INSERT INTO ADMIN.TAB2 VALUES (CURRENT_TIMESTAMP);
    END
    @
    Above stored procedure works fine and when at P_ID=2 error appears error handler is executed and error is logged into admin.log table.

    But I also have a requirement to log info for ALL OF SQL statements in log (and additionally to add some statistics how many rows where affected by SQL).
    I added additional code to track successfully executed SQLs (in blue color):
    Code:
    ALTER TABLE ADMIN.LOG ADD COLUMN ROWS INT;
    
    CREATE OR REPLACE PROCEDURE ADMIN.TEST
    (
    OUT P_ID INT,
    OUT P_RETURN_CODE INT
    )
    LANGUAGE SQL
    SPECIFIC ADMIN.TEST
    BEGIN
        DECLARE P_ROWS INT DEFAULT 0;
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                SET P_RETURN_CODE = SQLCODE;
                INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, 0);
            END;
        SET P_RETURN_CODE = 0;
    
        SET P_ID = 1;
        INSERT INTO ADMIN.TAB1 VALUES (CURRENT_TIMESTAMP);
        GET DIAGNOSTICS P_ROWS = ROW_COUNT;
        INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, P_ROWS);
    
        SET P_ID = 2;
        -- the value 'x' is going to produce an error, because timestamp data type is expected
        INSERT INTO ADMIN.TAB2 VALUES ('X');
        GET DIAGNOSTICS P_ROWS = ROW_COUNT;
        INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, P_ROWS);
    
        SET P_ID = 3;
        INSERT INTO ADMIN.TAB2 VALUES (CURRENT_TIMESTAMP);
        GET DIAGNOSTICS P_ROWS = ROW_COUNT;
        INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, P_ROWS);
    END
    @
    Above stored procedure works fine, but what we see blue colored code is repeating all over (and more P_IDs we have more the duplication). I would like to remove this duplication to make code more readable and maintainable. Instead of blue color code above I was trying to write a "continue handler" for successfully executed code (in red) and was hopping continue handler OK would be triggered for every SQL statement:

    Code:
    CREATE OR REPLACE PROCEDURE ADMIN.TEST
    (
    OUT P_ID INT,
    OUT P_RETURN_CODE INT
    )
    LANGUAGE SQL
    SPECIFIC ADMIN.TEST
    BEGIN
        DECLARE OK CONDITION FOR '00000';
        DECLARE P_ROWS INT DEFAULT 0;
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR OK
            BEGIN
                GET DIAGNOSTICS P_ROWS = ROW_COUNT;
                INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, P_ROWS);
            END;
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                SET P_RETURN_CODE = SQLCODE;
                INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, 0);
            END;
        SET P_RETURN_CODE = 0;
    
        SET P_ID = 1;
        INSERT INTO ADMIN.TAB1 VALUES (CURRENT_TIMESTAMP);
    
        SET P_ID = 2;
        -- the value 'x' is going to produce an error, because timestamp data type is expected
        INSERT INTO ADMIN.TAB2 VALUES ('X');
    
        SET P_ID = 3;
        INSERT INTO ADMIN.TAB2 VALUES (CURRENT_TIMESTAMP);
    END
    @
    but it returns error when stored procedure is trying to be created:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0435N An invalid application defined SQLSTATE "00000" was specified. LINE
    NUMBER=9. SQLSTATE=428B3


    It looks like successful (SQLSTATE 00000) is not allowed in handler.

    Any idea how to solve this problem? I would like to remove as much of duplication of code as possible, because this code is going to be executed in many many many stored procedures and each of duplication removed maintenance of code is drastically simplified.
    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Continue Handlers are a form of exception handler, they fire only when there is an exception, but sqlstate '00000' is not an exception.
    IBM documents this by stating that for an exception handler "The first two characters of the SQLSTATE value must not be '00'.".
    Does db2audit not help you? If not, consider event monitors or the tracing API technique outlined by Serge Rielau some years ago (google for it on developerworks) - but you might want to severely limit the deployment if you want to avoid performance impacts.
    Last edited by db2mor; 07-24-17 at 16:36.

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    You can use a local procedure like this:
    Code:
    BEGIN
        ...
        declare procedure log
        begin
          INSERT INTO ADMIN.LOG (ID, ERR_CODE, ROWS) VALUES (P_ID, P_RETURN_CODE, P_ROWS);
        end;
    
        ...
        INSERT INTO ADMIN.TAB2 VALUES (CURRENT_TIMESTAMP);
        GET DIAGNOSTICS P_ROWS = ROW_COUNT;
        call log;
    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
  •