We have a COBOL DB2 v7.x stored proc in mainframe DB2 that takes an input parameter and has four other out params. It also returns a cursor. It is called via a Java client via JDBC and DB2 Connect.

CallableStatement cs = connDB2.prepareCall("{CALL XXXXX.CPSP0006(?,?,?,?,?)}");

The problem is... at some point the procedure decides it no longer wants to return the out params. It returns nulls for all character out params. But it does return a valid correct cursor. Strange...

For the most part the stored proc returns what it should. We have put displays in the COBOL proc and can see the output in the "SPAS" area. We have also wrote a batch COBOL program the call the proc and it returns the same information.

The only thing that seems to clear it up is a STOP and START PROCEDURE .

Any ideas on debugging or what the heck the problem is?

More pertinent information.

Our DB2 is udb 7.2 fix pack 9 for OS/390.

Meaningful Portions of Offending code is..

EXEC SQL DECLARE C1 CURSOR WITH RETURN FOR
SELECT ......


LINKAGE SECTION.
*
01 LINK-SQLCODE PIC S9(09) COMP.
01 LINK-ERRMC PIC X(70).
01 LINK-SQLSTATE PIC X(05).
01 LINK-MESSAGE PIC X(50).
01 LINK-ID PIC S9(09) COMP.
*---------------------------------------------------------------*
PROCEDURE DIVISION USING
LINK-SQLCODE
, LINK-ERRMC
, LINK-SQLSTATE
, LINK-MESSAGE
, LINK-ID.
*--CHECKS PARMS and initialized linkage -------------------------*

PERFORM 1000-INITIALIZE THRU 1000-EXIT
*-OPENS CURSOR----------------------------------------------*

IF OK-TO-CONTINUE
PERFORM 2000-PROCESS THRU 2000-EXIT
END-IF


DISPLAY 'AT THE END OF CPSP0006:'
DISPLAY ' LINK-SQLCODE = ' LINK-SQLCODE
DISPLAY ' LINK-ERRMC = ' LINK-ERRMC
DISPLAY ' LINK-SQLSTATE= ' LINK-SQLSTATE
DISPLAY ' LINK-MESSAGE = ' LINK-MESSAGE
DISPLAY ' LINK-ID = ' LINK-ID.

GOBACK
.


Java Code that fails....Sometimes.

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance() ;

Connection connDB2 = DriverManager.getConnection
("jdbc:db2:XXXX","uanem", "pwd");

CallableStatement cs;

cs = connDB2.prepareCall("{CALL IBCPLN.CPSP0006(?,?,?,?,?)}");


cs.registerOutParameter(1, Types.INTEGER);
cs.registerOutParameter(2, Types.CHAR,70);
cs.registerOutParameter(3, Types.CHAR,5);
cs.registerOutParameter(4, Types.CHAR,50);

cs.setInt(5, 35826);
String param1 = " ";
String param2 = " ";
String param3 = " ";
String param4 = " ";

//cs.setInt(5, 0);
cs.setInt(1, 0);
cs.setString(2, param1);
cs.setString(3, param2);
cs.setString(4, param3);
System.out.println("Six proc calling");

cs.execute();
// These all print nulls (except the first int prints 0)
System.out.println("Six proc cs var 1: " + cs.getInt(1));
System.out.println("Six proc cs var 2: " + cs.getString(2));
System.out.println("Six proc cs var 3: " + cs.getString(3));
System.out.println("Six proc cs var 4: " + cs.getString(4));
System.out.println(" Warnings: " + cs.getWarnings());
}


ResultSet myRs = cs.getResultSet();

if ((myRs!=null) && (myRs.next())){

System.out.println("Six proc myRs var 1: " + myRs.getString(1));
System.out.println("Six proc myRs var 1: " + myRs.getString(2));

}

myRs.close();
cs.close();
connDB2.close();