I am trying to call a DB2 stored procedure from Squirrel. I can call the stored procedure successfully from the DB2 command line processor.
CALL PIPPOOWN.PIPPO
('04','prov','0001','provprov')
I append ;retrieveMessagesFromServerOnGetMessage=true; to connection string for the database.
jdbc:db2://xx.xx.x.xx:50001/DB2XYX:retrieveMessagesFromServerOnGetMessage=true;
But when I try this in Squirrel I get this error message. I am running
Error: [jcc][10413][10940][4.19.49]
Analisi della sostituzione letterale non riuscita per la chiamata della procedura a DB2 per z/OS.
Testo SQL non riuscito CALL
SP_XXXXX('XX','prova').
ERRORCODE=-4463, SQLSTATE=42601
SQLState: 42601
ErrorCode: -4463
2021-02-02 12:01:03,409 [Thread-6] ERROR net.sourceforge.squirrel_sql.fw.util.DefaultExceptionFormatter - Exception occurred while formatting: null
java.lang.NullPointerException
at net.sourceforge.squirrel_sql.plugins.db2.DB2JCCExceptionFormatter.format(DB2JCCExceptionFormatter.java:93)
at net.sourceforge.squirrel_sql.fw.util.DefaultExceptionFormatter.format(DefaultExceptionFormatter.java:68)
at net.sourceforge.squirrel_sql.client.session.Session.formatException(Session.java:1195)
at net.sourceforge.squirrel_sql.client.session.mainpanel.SQLExecutionHandler.sqlExecutionException(SQLExecutionHandler.java:458)
at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.handleError(SQLExecuterTask.java:621)
at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.runDirect(SQLExecuterTask.java:264)
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)
i updated jdbc drivers but i didn't fix.
DB2 Version: 11.01
If I use IBM DATA STUDIO or I use a DB2 AIX it works.
EDIT 03/02/2021
I downloaded "IBM Data Server Driver for JDBC and SQLJ (JCC Driver)" Version 11.5.4, then I configured it to use db2jcc4.jar Now it gives the following error:
NO AUTHORIZED PROCEDURE NAMED PIPPOOWN.PIPPO HAVING COMPATIBLE ARGUMENTS WAS FOUND.
SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14 SQL Code: -440, SQL State: 42884
but if i use IBM DATA STUDIO with the same user and statements i get no error
Stored Procedure "PIPPO" - GRANT
GRANT EXECUTE ON PROCEDURE "PIPPOOWN"."PIPPO" TO "ALL";
GRANT EXECUTE ON PROCEDURE "PIPPOOWN"."PIPPO" TO "PUBLICB";
GRANT EXECUTE ON PROCEDURE "PIPPOOWN"."PIPPO" TO "PIPPOOWN" WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE "PIPPOOWN"."PIPPO" TO "USERWEB";
GRANT EXECUTE ON PROCEDURE "PIPPOOWN"."PIPPO" TO PUBLIC;
Stored Procedure "PIPPO" - Distribution Options (using Data Studio)
CURRENT_PATH BY SQUIRREL
SELECT CURRENT_PATH FROM SYSIBM.SYSDUMMY1
RESULTS
-----------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","PIPPOOWN"
Finally, I can't find any error in the Squirrel log.
Any ideas?
EDIT 04/02/2021
I excecute the call statement on Squirrel, enabling the JDBC log 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)
...
.....
**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)
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().
IBM DATA STUDIO
[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][4] and it seems that in order to call SP with parameters in the Z/OS environment, it is necessary to perform a PreparedStatement.
At this point I think it’s a Squirrel BUG...
I tried to open a BUG: squirrel-sql:bugs#1457 Call Stored Procedure with input parameters on DB2 z/os