Topic
  • 10 replies
  • Latest Post - ‏2012-06-21T14:50:37Z by Oratorio
MBNate
MBNate
7 Posts

Pinned topic Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

‏2012-04-04T21:07:35Z |
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/cddbtest2");
Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("CALL DB2DBA.P_CDDB_DLR_RECAP('56113')");

Error 500: com.ibm.db2.jcc.am.SqlSyntaxErrorException: jcc1041310940http://3.63.75 Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text CALL DB2DBA.P_CDDB_DLR_RECAP('56113'). ERRORCODE=-4463, SQLSTATE=42601

Does anyone know why this error occurs? Thanks.
Updated on 2012-06-21T14:50:37Z at 2012-06-21T14:50:37Z by Oratorio
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-05T18:58:22Z  
    Sorry, some garbage in the error message. Here is the error message again:

    Error 500: com.ibm.db2.jcc.am.SqlSyntaxErrorException: jcc1041310940http://3.63.75 Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text CALL DB2DBA.P_CDDB_DLR_RECAP('56113'). ERRORCODE=-4463, SQLSTATE=42601
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-05T18:59:20Z  
    Error 500: com.ibm.db2.jcc.am.SqlSyntaxErrorException: (jcc)(10413)(10940)(3.63.75) Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text CALL DB2DBA.P_CDDB_DLR_RECAP('56113'). ERRORCODE=-4463, SQLSTATE=42601
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-13T15:59:07Z  
    • MBNate
    • ‏2012-04-05T18:59:20Z
    Error 500: com.ibm.db2.jcc.am.SqlSyntaxErrorException: (jcc)(10413)(10940)(3.63.75) Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text CALL DB2DBA.P_CDDB_DLR_RECAP('56113'). ERRORCODE=-4463, SQLSTATE=42601
    can you post the definition of your stored procedure?
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-13T20:19:37Z  
    can you post the definition of your stored procedure?
    CREATE PROCEDURE P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    • SQL Stored Procedure
    P1: BEGIN

    -- Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    -- Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-13T20:21:14Z  
    • MBNate
    • ‏2012-04-13T20:19:37Z
    CREATE PROCEDURE P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    • SQL Stored Procedure
    P1: BEGIN

    -- Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    -- Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
    CREATE PROCEDURE CONS084.P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    P1: BEGIN

    -- Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    -- Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-13T20:33:29Z  
    • MBNate
    • ‏2012-04-13T20:21:14Z
    CREATE PROCEDURE CONS084.P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    P1: BEGIN

    -- Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    -- Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
    CREATE PROCEDURE P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    P1: BEGIN

    • Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    • Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-04-26T13:20:27Z  
    • MBNate
    • ‏2012-04-13T20:33:29Z
    CREATE PROCEDURE P_CDDB_DLR_RECAP ( IN CDE_DLR VARCHAR(5) )
    RESULT SETS 16
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID DLRK04
    WLM ENVIRONMENT WLMDBT1
    ASUTIME NO LIMIT
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&53.252.31.93:*)'

    P1: BEGIN

    • Declare cursors

    DECLARE D_CURSOR CURSOR WITH RETURN FOR
    SELECT...

    . . .

    • Cursors left open for the client application

    OPEN D_CURSOR;

    END P1
    can you post your java code as well?
  • MBNate
    MBNate
    7 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-05-02T18:10:16Z  
    can you post your java code as well?
    Context ic = new InitialContext();
    DataSource ds = (DataSource) ic.lookup("jdbc/cddbtest2");
    Connection con = ds.getConnection();
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("CALL DB2DBA.P_CDDB_DLR_RECAP('56113')");
  • Oratorio
    Oratorio
    25 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-06-21T14:49:48Z  
    I had the same problem

    you need to use prepareCall, CallableStatement, setString for input parameters and registerOutParameter for output parameters

    String query = "CALL DB2DBA.P_CDDB_DLR_RECAP('56113')"
    CallableStatement stmt = conn.prepareCall(query);

    stmt.setString (1, '56113');

    ResultSet rset = stmt.executeQuery(); /* if the proc returns someting otherwise stmt.executeUpdate */
  • Oratorio
    Oratorio
    25 Posts

    Re: Calling DB2 z/OS stored procedure with literal parms from Java/JSP in WASv7

    ‏2012-06-21T14:50:37Z  
    • Oratorio
    • ‏2012-06-21T14:49:48Z
    I had the same problem

    you need to use prepareCall, CallableStatement, setString for input parameters and registerOutParameter for output parameters

    String query = "CALL DB2DBA.P_CDDB_DLR_RECAP('56113')"
    CallableStatement stmt = conn.prepareCall(query);

    stmt.setString (1, '56113');

    ResultSet rset = stmt.executeQuery(); /* if the proc returns someting otherwise stmt.executeUpdate */
    use ? here
    String query = "CALL DB2DBA.P_CDDB_DLR_RECAP(?)"