Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: How to handle exception handling in SQL stored procedures

    Hi,

    I am migrating the java based stored procedures to SQL based. Can some one help how to handle exception handling in SQL based stored procedures.

    Java Based:

    public class ADDACCESSLOG
    {
    public static final String ADD_ACCESSLOG_SQL = " INSERT INTO DB2INST1.ACCESSLOG " +
    " ( USERID, PARAMETERS, COMMENTS, RULENUMBER, ACCOUNTNUMBER, " +
    " IPADDRESS, LOGTIME, SOURCEOFACTIVITY, ORGANIZATIONID ) " +
    " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

    public static final String OLD_ACCESSLOG_SQL = " INSERT INTO DB2INST1.SWAPACCESSLOG " +
    " ( USERID, PARAMETERS, COMMENTS, RULENUMBER, ACCOUNTNUMBER, " +
    " IPADDRESS, LOGTIME, SOURCEOFACTIVITY, ORGANIZATIONID ) " +
    " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

    public static void aDDACCESSLOG ( long iUserId,
    String iParameters,
    String iComments,
    int iRuleNumber,
    String iIPAddress,
    long iOrganizationId,
    ResultSet[] rs ) throws SQLException, Exception
    {
    // Get connection to the database
    Connection con = DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement pstmt = null;
    String sql;
    try {

    pstmt = con.prepareStatement( ADD_ACCESSLOG_SQL );
    pstmt.setLong(1,iUserId);
    pstmt.setString(2,iParameters);
    pstmt.setString(3,iComments);
    pstmt.setInt(4,iRuleNumber);
    pstmt.setString(5,"");
    pstmt.setString(6,iIPAddress);
    Timestamp _curtime = new java.sql.Timestamp(new java.util.Date().getTime());
    pstmt.setTimestamp(7,_curtime);
    pstmt.setString(8,"I");
    pstmt.setLong(9,iOrganizationId);
    pstmt.executeUpdate();
    if(pstmt != null) pstmt.close();
    con.commit();
    }catch(SQLException se){
    String errorMsg = se.getMessage();
    if( ( errorMsg.indexOf("SQL0204N") > 0 ) ||
    ( errorMsg.indexOf("SQL0727N") > 0 )) {

    pstmt=null;
    pstmt = con.prepareStatement( OLD_ACCESSLOG_SQL );
    pstmt.setLong(1,iUserId);
    pstmt.setString(2,iParameters);
    pstmt.setString(3,iComments);
    pstmt.setInt(4,iRuleNumber);
    pstmt.setString(5,"");
    pstmt.setString(6,iIPAddress);
    Timestamp _curtime = new java.sql.Timestamp(new java.util.Date().getTime());
    pstmt.setTimestamp(7,_curtime);
    pstmt.setString(8,"I");
    pstmt.setLong(9,iOrganizationId);
    pstmt.executeUpdate();
    if(pstmt != null) pstmt.close();
    con.commit();

    }else throw se;


    }
    finally{
    if (con != null) con.close();
    }


    }

    }

    SQL based :


    CREATE PROCEDURE DB2INST1.ADDACCESSLOG_SQL ( IN iUserId BIGINT,
    IN iParameters VARCHAR(200),
    IN iComments VARCHAR(100),
    IN iRuleNumber INTEGER,
    IN iIPAddress VARCHAR(50),
    IN iOrganizationId BIGINT )
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- iUserId
    -- iParameters
    -- iComments
    -- iRuleNumber
    -- iIPAddress
    -- iOrganizationId
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable
    INSERT INTO DB2INST1.ACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );



    END P1


    How to handle exceptions in SQL stored procedures , Any help is appreciated?
    p.srinivasarao

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You did not mention what DB2 version or OS you are using.

    For DB2 V8.1 for LUW and greater, if you look at the syntax diagram for "CREATE PROCEDURE", there are condition handlers that you can include (they go near the top of a compound statement). How you use them depends on what you want to do.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can download "Chapter 5 - Condition Handling" of the book entitled DB2 SQL Procedural Language for Linux, UNIX, and Windows for free at the following website:

    http://www-128.ibm.com/developerwork...oks/sqlplbook/
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2003
    Posts
    47

    Exception Handling not happening as excepcted

    I have two tables, If ACCESSLOG table does not exists, I want to write to
    SWAPACCESSLOG TABLE, I added the code but does not seems to be working.
    Any help is appreciated.

    DB2 VERSION IS 8.1.12


    P1: BEGIN
    -- Declare variable
    DECLARE v_sqlstate_test CHAR(5);
    DECLARE v_sqlcode_test INT;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE v_TABLE_NOT_FOUND INT DEFAULT 0;
    DECLARE c_TABLE_NOT_FOUND CONDITION FOR SQLSTATE '56098';

    DECLARE CONTINUE HANDLER FOR c_TABLE_NOT_FOUND
    SET v_TABLE_NOT_FOUND = 1;

    INSERT INTO DB2INST1.ACCESSLOG( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );

    IF v_TABLE_NOT_FOUND = 1 THEN

    INSERT INTO DB2INST1.SWAPACCESSLOG( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );

    END IF;
    END P1
    p.srinivasarao

  5. #5
    Join Date
    Jan 2003
    Posts
    47

    Additional Comments

    Even though the accesslog table does not exists, it is not inserting a record into swapaccess log tables. I am not getting any exception, but I don't see entries in swapaccess log table. Any suggestion is appreciated.
    p.srinivasarao

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you try executing the first INSERT from the command line when the table does not exist, you will probably get SQLSTATE = '42704', not '56098'.

    I think I would write the SP like this:

    P1: BEGIN
    -- Declare variable
    DECLARE v_sqlstate_test CHAR(5);
    DECLARE v_sqlcode_test INT;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE tableExists int default 0;

    SET tableExists = (select count(*) from syscat.tables where tabschema = 'DB2INST1' and tabname = 'ACCESSLOG');

    IF (tableExists <> 0)
    THEN
    INSERT INTO DB2INST1.ACCESSLOG( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );

    ELSE

    INSERT INTO DB2INST1.SWAPACCESSLOG( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );

    END IF;
    END P1

    Andy

  7. #7
    Join Date
    Jan 2003
    Posts
    47

    Can't I do the same with exception handlers

    Thanks Marcus for the url and thanks Winner for your suggestion.

    Each day there will be 1 million entries into this table and access log table won't available for 1 to 2 Minutes. Making a call to database to check the table exists, I would think is a expensive process. By still setting the sqlstate on sql stored procedure to '42704' , I am not able to insert into swap accesslog table if accesslog table does not exists. Am I doing some thing wrong in exception handling?
    p.srinivasarao

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have you tried to use the debugger and step through your SP to see what is happening?

    Andy

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I just recreated the scenario (just much simpler):
    Code:
    $ db2 "create table a ( a int )"
    $ db2 "create table b ( b int )"
    $ db2 "create procedure p() modifies sql data begin declare v int default 0; declare continue handler for sqlstate '42704' set v = 1; insert into a values(1); if v > 0 then insert into b values(1); end if; end"
    $ db2 "call p()"
      Return Status = 0
    $ db2 "select * from a"
    
    A
    -----------
              1
    
    9 local:~/ $ db2 "select * from b"
    
    B
    -----------
    
      0 record(s) selected.
    
    $ db2 "drop table a"
    $ db2 "call p()"
    SQL0727N  An error occurred during implicit system action type "1".
    Information returned for the error includes SQLCODE "-204", SQLSTATE "42704"
    and message tokens "STOLZE.A".  SQLSTATE=56098
    As we can see, there is an error message returned from DB2. And that error tells us exactly what is happening.

    1. the DROP TABLE statement invalidated the procedure (obviously)
    2. on the next execution of the procedure, DB2 has to recreate/rebind the procedure package to handle the "invalid" state
    3. during this rebind, it is detected that the table A does not exist any longer, and, therefore the procedure can't be used.


    Now, if that does not match palchuri's approach, then we would need a more details on what is happening. Especially, what is the error received from DB2 upon the CALL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2003
    Posts
    47
    Yes stolze, that is what it is happening.

    I don't have this problem with Java based stored procs. I am under the impression that java also converts SP's to SQL at runtime.

    Here are my questions.

    1. During runtime(NOT BUILD TIME) does DB2 REBUILD (SOME OF TYPE OF CHECK) the stored procedure to make sure that all acessed objects exists or not.

    2. Is this the limitation in SQL based stored procedures ?

    3. Is there any way to overcome this issue

    a. Call this stored procedure on some other stored procedure and handle the
    exception? Any examples is appreciated.

    b. Is there is any way I can achieve in DB2 SQL based stored procedures?


    I would appreciate any help in this regard.


    Thanks
    Shri
    p.srinivasarao

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by palchuri
    Yes stolze, that is what it is happening.

    I don't have this problem with Java based stored procs. I am under the impression that java also converts SP's to SQL at runtime.

    Here are my questions.

    1. During runtime(NOT BUILD TIME) does DB2 REBUILD (SOME OF TYPE OF CHECK) the stored procedure to make sure that all acessed objects exists or not.

    2. Is this the limitation in SQL based stored procedures ?

    3. Is there any way to overcome this issue

    a. Call this stored procedure on some other stored procedure and handle the
    exception? Any examples is appreciated.

    b. Is there is any way I can achieve in DB2 SQL based stored procedures?


    I would appreciate any help in this regard.


    Thanks
    Shri
    SQL Stored Procedures use embedded static SQL by default. In order to prevent the problem you have encountered you can use embedded dynamic SQL (prepare and execute, or execute immediate).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by palchuri
    I don't have this problem with Java based stored procs. I am under the impression that java also converts SP's to SQL at runtime.
    No, Java code in an SP is not and cannot be converted automatically to SQL. As soon as you have external actions (like sending email or something), you will have a problem.

    What is happening is that the Java SP is pretty much like any other Java application and can communicate with DB2 to execute SQL statements. The main difference in a Java SP is that DB2 knows about this context and verifies that the SP doesn't execute UPDATE statements if the SP was declared as READS SQL DATA only.

    Here are my questions.

    1. During runtime(NOT BUILD TIME) does DB2 REBUILD (SOME OF TYPE OF CHECK) the stored procedure to make sure that all acessed objects exists or not.
    When you execute the DROP TABLE, DB2 walks through all dependent objects (like your SP) and invalidates them. When the procedure is invoked, DB2 will only verify if the SP is invalidated or not. If not, then the procedure is invoked. Otherwise, the procedure is re-compiled (which includes all the various verification steps). So in general, the major work is performed during DROP TABLE only - the test for invalidation is negligible.

    2. Is this the limitation in SQL based stored procedures ?
    I wouldn't call it that. It is just the design of SPs in DB2 that you don't have to deal with missing objects inside your procedure. That's the same as in any executable program where the linking step makes sure that all required functions/classes exist.

    3. Is there any way to overcome this issue

    a. Call this stored procedure on some other stored procedure and handle the
    exception? Any examples is appreciated.

    b. Is there is any way I can achieve in DB2 SQL based stored procedures?
    • Redesign your application logic - I have no idea why you are using two of these log tables in the first place.
    • given that you have a dynamic component in your application (tables come and go), you have to stick to dynamic SQL, which means:
      1. build any SQL statements as string
      2. use the PREPARE statement to compile the statements, followed by EXECUTE for the actual (and possibly repeated) execution
      3. use EXECUTE IMMEDIATE for statements that you don't have to prepare

    Have a look at the samples in the DB2 manual first.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jan 2003
    Posts
    47

    Still not working

    Here I have 2 issues.

    1. DECLARE logtime timestamp DEFAULT current timestamp; is giving
    compilation error.

    2. The following snippet is not throwing any error if the table does not exist but excpetion handling looks like not working as expected.


    DECLARE SQLStmt VARCHAR(1000);
    DECLARE SOURCEOFENTRY CHAR(1) DEFAULT 'I';
    DECLARE v_TABLE_NOT_FOUND INT DEFAULT 0;
    DECLARE c_TABLE_NOT_FOUND CONDITION FOR SQLSTATE '42704';
    DECLARE c1 CURSOR WITH RETURN FOR s1;
    DECLARE CONTINUE HANDLER FOR c_TABLE_NOT_FOUND SET v_TABLE_NOT_FOUND = 1;

    SET SQLStmt='INSERT INTO DB2INST1.ACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID )
    VALUES (?,?,?,?,?,?,?)';
    PREPARE s1 FROM SQLStmt;
    EXECUTE s1 using iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, SOURCEOFENTRY,iOrganizationId;

    IF v_TABLE_NOT_FOUND = 1 THEN

    SET SQLStmt='INSERT INTO DB2INST1.SWAPACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID )
    VALUES (?,?,?,?,?,?,?)';
    PREPARE s1 FROM SQLStmt;
    EXECUTE s1 using iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, SOURCEOFENTRY,iOrganizationId;
    END IF;


    Shri
    p.srinivasarao

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) post entire SP to see why you are getting error.

    2) You need to check what error you are getting when the table does not exist. My guess it is not sqlstate 42704.

    Andy

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by palchuri
    1. DECLARE logtime timestamp DEFAULT current timestamp; is giving compilation error.
    Could you be a bit more specific and tell us which error you get? As you know, there are many possible reasons imaginable.

    2. The following snippet is not throwing any error if the table does not exist but excpetion handling looks like not working as expected.


    DECLARE SQLStmt VARCHAR(1000);
    DECLARE SOURCEOFENTRY CHAR(1) DEFAULT 'I';
    DECLARE v_TABLE_NOT_FOUND INT DEFAULT 0;
    DECLARE c_TABLE_NOT_FOUND CONDITION FOR SQLSTATE '42704';
    DECLARE c1 CURSOR WITH RETURN FOR s1;
    DECLARE CONTINUE HANDLER FOR c_TABLE_NOT_FOUND SET v_TABLE_NOT_FOUND = 1;

    SET SQLStmt='INSERT INTO DB2INST1.ACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID )
    VALUES (?,?,?,?,?,?,?)';
    PREPARE s1 FROM SQLStmt;
    EXECUTE s1 using iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, SOURCEOFENTRY,iOrganizationId;

    IF v_TABLE_NOT_FOUND = 1 THEN

    SET SQLStmt='INSERT INTO DB2INST1.SWAPACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID )
    VALUES (?,?,?,?,?,?,?)';
    PREPARE s1 FROM SQLStmt;
    EXECUTE s1 using iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, SOURCEOFENTRY,iOrganizationId;
    END IF;
    Have you tried to execute the INSERT statements on the command line? Maybe you have a syntax error? Also, it can happen that the 2nd INSERT statement also fails so that you don't see any results?
    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
  •