Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    9

    Unanswered: COBOL Stored Proc problem

    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?

  2. #2
    Join Date
    Feb 2004
    Posts
    9

    Re: COBOL Stored Proc problem

    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();

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •