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)?
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
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.
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.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 295 |
Nodes: | 16 (2 / 14) |
Uptime: | 01:20:08 |
Calls: | 6,642 |
Calls today: | 2 |
Files: | 12,190 |
Messages: | 5,325,421 |