#1457 Call Stored Procedure with input parameters on DB2 z/os

[Mirco]

Hi,

this is my Squirrel setup

SQuirreL SQL Client Version 4.1.0
Java(TM) SE Runtime Environment - Version: 1.8.0_271
O/S: Windows 10
DB2 Z/OS 1 Version 11.01 (DSN11015)
Driver name: IBM Data Server Driver for JDBC and SQLJ
Driver version: 4.26.14

I use DB2 11 Z/OS and I need to call a Stored Proceder (SP_ABC) with input parameters but when I run the statement "CALL" I get this error:

Error: NO AUTHORIZED PROCEDURE NAMED OWNER1.SP_ABC HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14
SQLState:  42884
ErrorCode: -440 

Statement:
CALL OWNER1.SP_ABC('XX')

I checked SQLCODE -440 on the IBM documentations and I found that
1. The routine name it's correct
2. The Qualified reference and schema used are correct.
3. The user's SQL path contain the schema to which the function belongs.
4. The number of arguments included it's correct
5. The data type of the arguments is correct.
6. I am authorized to execute the routine

To ensure it is not a grant or parameters issue, I called the SP using IBM DATA STUDIO 4.1.3 with the same user, the same JDBC drivers and the same statements but i get no error.

So, I excecute the call statement on Squirrel, enabling the JDBC log (attached: jdbc_log_sp_abc.txt ) and I found this error:

[jcc][Time:2021-02-04-10:16:23.549][Thread:Thread-4][Statement@f7f204]**execute (CALL OWNER1.SP_ABC('xx')) called**
[jcc][Time:2021-02-04-10:16:23.549][Thread:Thread-4][Statement@f7f204]stmt_bidiTransform (CALL OWNER1.SP_ABC('xx')) called
[jcc][Time:2021-02-04-10:16:23.549][Thread:Thread-4][Statement@f7f204]**stmt_bidiTransform not enabled (CALL OWNER1.SP_ABC('xx')) called**
[jcc][t4][time:2021-02-04-10:16:23.549][Thread:Thread-4][tracepoint:1][Request.flush]
[jcc][t4]        SEND BUFFER: EXCSQLSTT              (ASCII)           (EBCDIC)
...
.....
[jcc][t4] 
**com.ibm.db2.jcc.am.SqlSyntaxErrorException
**  at com.ibm.db2.jcc.am.b7.a(b7.java:810)
    at com.ibm.db2.jcc.am.b7.a(b7.java:66)
    at com.ibm.db2.jcc.am.b7.a(b7.java:140)
    at com.ibm.db2.jcc.am.k4.b(k4.java:2471)
    at com.ibm.db2.jcc.am.k4.c(k4.java:2452)
    at com.ibm.db2.jcc.t4.ab.n(ab.java:914)
    at com.ibm.db2.jcc.t4.ab.f(ab.java:156)
    at com.ibm.db2.jcc.t4.p.e(p.java:81)
    at com.ibm.db2.jcc.t4.av.k(av.java:175)
    at com.ibm.db2.jcc.am.k4.ao(k4.java:2413)
    at com.ibm.db2.jcc.am.k4.a(k4.java:3383)
    at com.ibm.db2.jcc.am.k4.e(k4.java:1131)
    at com.ibm.db2.jcc.am.k4.execute(k4.java:1110)
    at net.sourceforge.squirrel_sql.client.session.StatementWrapper.execute(StatementWrapper.java:168)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:361)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.runDirect(SQLExecuterTask.java:214)
    at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:137)
    at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.lang.Thread.run(Unknown Source)
[jcc] BEGIN TRACE_DIAGNOSTICS

For comparing the different logs, I also activate the IBM DATA STUDIO JDBC log (where the CALL statements work correctly) and I noticed that a prepareCall() is done before executing execute()
(attached: ibm data studio_JDBC_LOG_CALL_SP.txt ):

[jcc][Time:2021-02-03-20:52:10.381][Thread:Worker-19][Connection@97f8a03c]prepareCall (CALL OWNER1.SP_ABC('XX')) called
[jcc] [time:2021-02-03-20:52:10.383][Thread:Worker-19][tracepoint:4000]LiteralInfo entry,sql:CALL OWNER1.SP_ABC('XX')
[jcc] [time:2021-02-03-20:52:10.387][Thread:Worker-19][tracepoint:4005]LiteralInfo.return,OriginalSQL:CALL OWNER1.SP_ABC('XX'), modifiedSQL:CALL OWNER1.SP_ABC(?), literalCount=1, parmCount=1[CHARACTER(XX)]
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][Connection@97f8a03c]prepareCall () returned MappedCallableStatement@691cf97[com.ibm.db2.jcc.am.CallableStatement@88958c83]
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][Connection@97f8a03c]prepareCall () returned MappedCallableStatement@691cf97[com.ibm.db2.jcc.am.CallableStatement@88958c83]
[jcc][Thread:Worker-19][SystemMonitor:stop] core: 9.5471ms | network: 0.0ms | server: 0.0ms 
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][CallableStatement@88958c83]setMaxRows (0) called
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][CallableStatement@88958c83]setString (1, XX) called
[jcc][Thread:Worker-19][SystemMonitor:start] 
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][CallableStatement@88958c83]execute () called
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][CallableStatement@88958c83]stmt_bidiTransform (CALL OWNER1.SP_ABC(?)) called
[jcc][Time:2021-02-03-20:52:10.390][Thread:Worker-19][CallableStatement@88958c83]stmt_bidiTransform not enabled (CALL OWNER1.SP_ABC(?)) called
[jcc][t4][time:2021-02-03-20:52:10.390][Thread:Worker-19][tracepoint:1][Request.flush]

I looked in the IBM documentation and it seems that in order to call SP with parameters in the Z/OS environment, it is necessary to perform a PreparedStatement.

is it possible to have support?

Thanks,
Mirco

[Gerd Wagner]

Sorry, I can't reproduce your problem on the DB2 instances I've got access to.
Though I get the same error message when I use non existing Schema or Procedure names.
You may check SQuirreL's Object tree if in Schema OWNER1 there is a procedure SP_ABC.

[Mirco]

Hi, the stored procedure is correctly defined on the schema.
If the SP has NO input parameters (example SP_ABC2) it works.
If the stored procedure has input parameters (example SP_ABC) it doesn't run.
I also checked SQUIRREL's "CURRENT_PATH" and it is correct.

[Mirco]

This bug is only reproducible with Db2-for-Z.
How can I help you?

[Gerd Wagner]

You may try if the following Java code reproduces the problem. If so it is a problem of your driver / database.

public class Bug1457Reproduction
{
   public static void main(String[] args) throws Exception
   {
      Class.forName("yourDriverClassName");

      Connection con = DriverManager.getConnection(
            "<yourJdbcUrl>", "<yourUserName>", "<yourPassword>");

      final Statement statement = con.createStatement();

      statement.execute("<yourProcedure>");

      // Depending on your procedure's return type
      statement.getUpdateCount();
      // or
      statement.getResultSet();
   }
}

[Mirco]

Hi Gerd, sorry to keep you waiting.

On DB2 Z/OS (this does not apply to DB2 AIX or DB2 LUW), if a Stored Procedure have a I/O parameters you cannot use "Statement" methods to execute CALL statements.
You must use PreparedStatement methods or CallableStatement methods.

Here is the IBM documentation link:
https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_tjvcscsp.html

In any case,starting from your code,I did some tests.

Here you find my code

Public class SquirrelBug1457CallSp {
    public static void main(String[] args) {
        try {
            testGerd_zOS();
            testMirco_ZOS();
        } catch (Exception ex) {
            Logger.getLogger(SquirrelBug1457CallSp.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
   public static void testGerd_zOS() throws Exception
   {
      System.out.println("**** Class testGerd_zOS()"); 
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      System.out.println("**** Loaded the JDBC driver"); 
      String urlSvil    = "jdbc:db2://XX.XX.X.XX:50001/DB2S"; 
      String userSvil   = "MIRCO";
      String pwSvil     = "BEMXXXXX";

      Connection con = DriverManager.getConnection(urlSvil, userSvil, pwSvil);
      final Statement statement = con.createStatement();
      String statement_call_sp_without_input = "CALL OWNER1.SP_ABC2();";
      System.out.println("**** EXECUTE :" + statement_call_sp_without_input);            
      statement.execute(statement_call_sp_without_input);
      System.out.println(" ok ");  

      String statement_call_sp_with_input = "CALL OWNER1.SP_ABC('AA');";
      System.out.println("**** EXECUTE :" + statement_call_sp_with_input);            
       try {
           statement.execute(statement_call_sp_with_input);
        } catch (SQLException ex) {
          System.out.println("errore :" + ex.toString());            
        }

   }


      public static void testMirco_ZOS() throws Exception
   {
      System.out.println("> Class testMirco_ZOS()"); 
      System.out.println("TEST - DB2 Z/OS - DB2 V11");
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      System.out.println("*Loaded the JDBC Driver*"); 
      //* I am using the same Db2 drivers and licenses used by Squirrel.
      String urlSvil    = "jdbc:db2://XX.XX.X.XX:XXXXX/DB2S"; 
      String userSvil   = "MIRCO";
      String pwSvil     = "BEMXXXXX";
      Connection con = DriverManager.getConnection(urlSvil, userSvil, pwSvil);
      System.out.println("*Created JDBC connection*"); 

      final Statement statement = con.createStatement();

      System.out.println("--------------------------------------------------------------");
      System.out.println("TEST 1 - CALL STORED PROCEDURE WITHOUT INPUT/OUTPUT PARAMETERS");
      System.out.println("--------------------------------------------------------------");
      String statement_call_sp_without_input = "CALL OWNER1.SP_ABC2();";
      System.out.println("execute:" + statement_call_sp_without_input);            
      statement.execute(statement_call_sp_without_input);      
      System.out.println("TEST 1 - SUCCESSFULLY COMPLETED.\n");


      System.out.println("--------------------------------------------------------------");
      System.out.println("TEST 2 - CALL STORED PROCEDURE WITH    INPUT        PARAMETERS");
      System.out.println("--------------------------------------------------------------");
      String statement_call_sp_with_input = "CALL OWNER1.SP_ABC(?);";      
      System.out.println("Create a CallableStatement object:" + statement_call_sp_with_input);            
      CallableStatement cstmt = con.prepareCall(statement_call_sp_with_input);

      String input_param_1 = "xx";
      System.out.println("Set input parameter:" + input_param_1);            
      cstmt.setString (1, input_param_1); 
      System.out.println("execute:" + statement_call_sp_with_input);            
      cstmt.execute();

      System.out.println("TEST 2 - SUCCESSFULLY COMPLETED.\n\n\n\n");

   }


}

This is the sysout.

run:
**** Class testGerd_zOS()
**** Loaded the JDBC driver
**** EXECUTE :CALL OWNER1.SP_ABC2();
ok 
**** EXECUTE :CALL OWNER1.SP_ABC('AA');
errore :com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PROCEDURE;OWNER1.SP_ABC, DRIVER=4.26.14


Class testMirco_ZOS()
TEST - DB2 Z/OS - DB2 V11
*Loaded the JDBC Driver*
*Created JDBC connection*
--------------------------------------------------------------
TEST 1 - CALL STORED PROCEDURE WITHOUT INPUT/OUTPUT PARAMETERS
--------------------------------------------------------------
execute:CALL OWNER1.SP_ABC2();
TEST 1 - SUCCESSFULLY COMPLETED.

--------------------------------------------------------------
TEST 2 - CALL STORED PROCEDURE WITH    INPUT        PARAMETERS
--------------------------------------------------------------
Create a CallableStatement object:CALL OWNER1.SP_ABC(?);
Set input parameter:xx
execute:CALL OWNER1.SP_ABC(?);
TEST 2 - SUCCESSFULLY COMPLETED.

BUILD SUCCESSFUL (total time: 1 second)

As you can see:

1) The "statement" method does not work if there are I/O parameters
2) In these cases, the CallableStatement method (or PreparedStatement ) must be used on Z/OS

[Gerd Wagner]

Thanks, Mirco, for checking this out.

SQuirreL can and will not try to execute a statement in the a way you do in your code. It must rely on "dynamically executing an unknown SQL string" as is specified here
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#execute(java.lang.String)

and as other JDBC drivers including other versions of DB2 do.

That's to say by not allowing to execute unknown SQL strings dynamically via calling java.sql.Statement.execute(String sql) the ZOS JDBC driver breaks the JDBC specification.
So please contact your JDBC driver vendor for the problem.