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?

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

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/

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

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.

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

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?

Have you tried to use the debugger and step through your SP to see what is happening?

Andy

I just recreated the scenario (just much simpler):
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.

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).

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.

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.

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.

• 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.

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

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

Could you be a bit more specific and tell us which error you get? As you know, there are many possible reasons imaginable.

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?