By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

DB2 database shutdowns after excecution of sqlj procedure

P: 1
In my SQLJ procedure , there is call to another stored procedure using a callable statement. But during excecution when .excute() happens . . the DB2 V7 database shuts down.

Plz help.

Follwing is the code . .
************ CAL02RSCAL.sqlj which call another PL/SQL Strored procedure TESTRSFILEPL written in DB2 *******************


package PAKCAL02RSCAL ;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.math.*;
import java.io.*;
import java.text.*;
import java.util.Date;

#sql iterator Defined_iter2 (String CLIENTID, String GRANTNUMBER, String PPTUNIQUEID);

public class CAL02RSCAL
{

public static void cAL02RS (
String instrPPTUNIQUEID,
String instrCLIENTID,
ResultSet[] out_rs
) throws SQLException, Exception
{

try
{
Defined_iter2 iter2;
String strPPTUNIQUEID = "";
String strCLIENTID = "";
PrintWriter pwx = null;
strPPTUNIQUEID = instrPPTUNIQUEID;
strCLIENTID = instrCLIENTID;
pwx = new PrintWriter( new FileWriter("/sapphire/users/devuser/ajitk/UPLOAD_RS/PAKCAL02RSCAL.log"),true );
//Start
pwx.println("CAL02RS: SQLJ versionn");
pwx.flush();
Connection conn = null;
String url;
String procName = "";
CallableStatement callStmt;

conn=DriverManager.getConnection("jdbc:db2:DEV3DB; create=true","spsdev","sps2diff");
conn.setAutoCommit(false);
pwx.println("CAL02RS: Before call of TESTRSFILEPLllll");
pwx.flush();
String sql = "CALL SPSSTADM.TESTRSFILEPL (?,?)";
callStmt = conn.prepareCall(sql);
callStmt.setString (1, instrPPTUNIQUEID);
callStmt.setString (2, instrCLIENTID);
callStmt.execute();
out_rs[0] = callStmt.getResultSet();

pwx.println("CAL02RS: SQLJ version End");
pwx.flush();
} // try
catch (Exception e)
{
System.out.println("IN the Exception Block");
}
}
}
*********** Create Statement *******
CREATE PROCEDURE SPSDEV.CAL02RSCAL ( IN instrPPTUNIQUEID varchar(4000), IN instrCLIENTID varchar(4000) ) EXTERNAL NAME 'SPSDEV.PAKCAL02RSCAL:PAKCAL02RSCAL.CAL02RSCAL.cAL 02RS' RESULT SETS 1 LANGUAGE JAVA PARAMETER STYLE JAVA NOT DETERMINISTIC FENCED NO DBINFO NULL CALL MODIFIES SQL DATA;
call sqlj.refresh_classes(void);

******* code for TESTRSFILE ************
CREATE PROCEDURE TESTRSFILEPL ( IN instrPPTUNIQUEID varchar(4000), IN instrCLIENTID varchar(4000))
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE c3 CURSOR WITH RETURN FOR
select CLIENTID, GRANTNUMBER, PPTUNIQUEID
from grantmaster where
clientid = instrCLIENTID
and PPTUNIQUEID = instrPPTUNIQUEID;

OPEN c3;

END P1

@
***************************

Plzzz Help
Jul 17 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.