CODEWITHSUNDEEP
stored-proceduresdb2-400jt400
Christopher Smith
Christopher Smith

Reputation: 215

Avoiding hard coding Schema in DB2/400 Stored Procedure

I'm creating Stored Procedures to replace Legacy app programs for an IBM i. I'm calling the stored procedure from a Java Web App. I'm using the jt400 JDBC driver

My JDBC URL is jdbc:as400://myhost/;libraries=*LIBL,MYLIB;prompt=false

The stored procedures can call stored procedures

The initial stored procedure call completes normally if it does not make further stored procedure calls

If the stored procedure makes a call to other stored procedures it fails with

com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] MY_SP in MYLIB type *N not found.

If I hard code a schema in the stored procedure call statement, the call completes normally.

I want to have the called stored procedures use the same schema as the caller

Upvotes: 1

Views: 794

Answers (2)

GenericDisplayName
GenericDisplayName

Reputation: 463

I actually had this same problem, the stored procedure uses your job description library list. You need to edit that you can use TAATOOL CHGLBLJOBD. I am not in front of an iSeries at the moment but I believe the command was either EDTJOBDLIB or EDTJOBDLIBL WRKJOBDLIBL. It is some variation of that.

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11493

You need to SET PATH = "MYLIB"

When I am using SQuirreL to call a stored procedure, I need to use the SET PATH statement to get it to find the stored procedure. I don't know if that is because my library list is bad or what, but the current schema is not used to find an unqualified stored procedure.

Upvotes: 0

Related Questions