I'm trying to repeat something I did before at another job. There is a
stored procedure on a remote DB2 system that returns a result set. On
the local system, I'm trying to do the following:

connect to the remote DB2 system

call the stored procedure

associate the result set from the procedure with a result set locator

ALLOCATE RESULT SET LOCATOR (:RSLOC) WITH PROCEDURE MYSCHMA.MYPROC

allocate a cursor for the result set

ALLOCATE C1 CURSOR FOR RESULT SET :RSLOC

fetch rows from the cursor and do something with them

reset the connection to the local system


I did this successfully at my last job, but both the local and remote
systems were IBM i. Now, the local is IBM i and the remote is z/OS.
When I attempt to call the stored procedure, it fails with a SQLSTATE
value of 42884:

No routine was found with the specified name and compatible arguments.

However, if I make the same stored procedure call from an ACS SQL script window, the call is successful and a result set is returned.

Does anyone have an idea why this might be failing when called from an
IBM i program (SQLCBLLE)?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Monday, September 10, 2018 at 4:20:57 PM UTC-4, Jonathan Ball wrote:

I did this successfully at my last job, but both the local and remote
systems were IBM i. Now, the local is IBM i and the remote is z/OS.
When I attempt to call the stored procedure, it fails with a SQLSTATE
value of 42884:

No routine was found with the specified name and compatible arguments.

However, if I make the same stored procedure call from an ACS SQL script window, the call is successful and a result set is returned.

Does anyone have an idea why this might be failing when called from an
IBM i program (SQLCBLLE)?

My bet is a parameter type mismatch. Db2 for i will implicitly CAST across CHAR and VARCHAR (mostly). Db2 for z may not. Make 100% sure that the Cobol variables are exactly what the remote stored proc parameters are.

The other quick possibility is an authority issue.

--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/11/2018 5:51 AM, Buck wrote:

On Monday, September 10, 2018 at 4:20:57 PM UTC-4, Jonathan Ball wrote:

I did this successfully at my last job, but both the local and remote
systems were IBM i. Now, the local is IBM i and the remote is z/OS.
When I attempt to call the stored procedure, it fails with a SQLSTATE
value of 42884:

No routine was found with the specified name and compatible arguments. >>
However, if I make the same stored procedure call from an ACS SQL script
window, the call is successful and a result set is returned.

Does anyone have an idea why this might be failing when called from an
IBM i program (SQLCBLLE)?

My bet is a parameter type mismatch. Db2 for i will implicitly CAST across CHAR and VARCHAR (mostly). Db2 for z may not. Make 100% sure that the Cobol variables are exactly what the remote stored proc parameters are.

There aren't any parameters for the stored procedure, neither input nor
output. It only opens a cursor and returns the result set. As I said,
calling the procedure from an ACS SQL window works; it's only calling it
from the HLL program that fails.

The other quick possibility is an authority issue.

--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, September 11, 2018 at 10:47:30 AM UTC-4, Jonathan Ball wrote:

When I attempt to call the stored procedure, it fails with a SQLSTATE
value of 42884:

No routine was found with the specified name and compatible arguments.

However, if I make the same stored procedure call from an ACS SQL script >> window, the call is successful and a result set is returned.

There aren't any parameters for the stored procedure, neither input nor output. It only opens a cursor and returns the result set. As I said, calling the procedure from an ACS SQL window works; it's only calling it
from the HLL program that fails.

Hm. Parameter mismatch is out then. It was a theoretical issue because the scripting tool binds literals differently than an HLL binds variables.

Is the remote database registered in WRKRDBDIRE? I'm thinking that ACS is using a JDBC driver which is configured differently to IBM i. To that point, is there a SQLSTATE on the CONNECT?
--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/12/2018 6:30 AM, Buck wrote:

On Tuesday, September 11, 2018 at 10:47:30 AM UTC-4, Jonathan Ball wrote:

When I attempt to call the stored procedure, it fails with a SQLSTATE
value of 42884:

No routine was found with the specified name and compatible arguments.

However, if I make the same stored procedure call from an ACS SQL script >>>> window, the call is successful and a result set is returned.

There aren't any parameters for the stored procedure, neither input nor
output. It only opens a cursor and returns the result set. As I said,
calling the procedure from an ACS SQL window works; it's only calling it
from the HLL program that fails.

Hm. Parameter mismatch is out then. It was a theoretical issue because the scripting tool binds literals differently than an HLL binds variables.

Is the remote database registered in WRKRDBDIRE? I'm thinking that ACS is using a JDBC driver which is configured differently to IBM i. To that point, is there a SQLSTATE on the CONNECT?

Embarrassment abounds. I misspelled the name of the procedure in the
COBOL program, getting an alpha 'o' where I should have had a numeric
'0'. Everything works now. There is, of course, a remote database
definition for the z/OS system in the RDB directory. I don't think I'd
be able to do a SQL CONNECT or use three-part naming at all if there
weren't.

JDBC only figures into the connection from the remote client (laptop) to
the IBM i. It isn't implicated in the DRDA connection from the IBM i to
the z/OS system. Similarly, if I were to run the SQL statements in a
5250 STRSQL session, Telnet would not be implicated in the DRDA
connection from IBM i to z/OS.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, September 14, 2018 at 12:15:04 AM UTC-4, Jonathan Ball wrote:

Embarrassment abounds. I misspelled the name of the procedure in the
COBOL program, getting an alpha 'o' where I should have had a numeric
'0'. Everything works now.

These are the best kind of errors because they are easy to understand :-) Thanks for posting the solution!
That courtesy is all too often forgotten these days...
--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)