Tweet
Hi folks,
I had this topic in CLLE programming first but now it is all about SQL, so I continue here.
While I am convinced the fix is gonna be beyond trivial, I'm still probably doing all the things wrong that you cannot even think of.
Here's my StoredProcedure:
create or replace procedure MYLIB.STOREDPRC1 (IN USER_ID char(10), IN DBLIB char(10))
--Delete for a given user from table USERS
language SQL modifies SQL data
begin
set schema DBLIB;
delete from USERS
where USER = USER_ID
with NC
;
end
And here's my SQL call statement:
call STOREDPRC1 ('PAUL', 'DATALIB')
Note that MYLIB is in my library list, as is DATALIB, and the STOREDPROC1 only exists in MYLIB. Still I get:
SQL: Error -206, State 42704
Joblog: SQL0206 - USERS in YSYS type *FILE not found.
Ok, so the second parm DBLIB is used to do the 'set schema' but then the 'delete from' does not use the schema but defaults to YSYS, a library that does not exist in my library list for the job and definitely does not contain the database.
So, I try with this instead:
call STOREDPRC1 ('PAUL', DATALIB)
...and I get:
SQL: Error -206, State 42703
Joblog: SQL0206 - Column or global variable DATALIB not found.
As I already indicated, the fix is probably easy as pie but no matter how much time I throw at it, it won't work for me.
Any tips on how to gt this thing on the road are very welcome indeed.
I'd also be interested to hear if a StoredProcedure could be debugged, and if so: how.
-----------------
Happy coding!
Paul
Sweden
I had this topic in CLLE programming first but now it is all about SQL, so I continue here.
While I am convinced the fix is gonna be beyond trivial, I'm still probably doing all the things wrong that you cannot even think of.
Here's my StoredProcedure:
create or replace procedure MYLIB.STOREDPRC1 (IN USER_ID char(10), IN DBLIB char(10))
--Delete for a given user from table USERS
language SQL modifies SQL data
begin
set schema DBLIB;
delete from USERS
where USER = USER_ID
with NC
;
end
And here's my SQL call statement:
call STOREDPRC1 ('PAUL', 'DATALIB')
Note that MYLIB is in my library list, as is DATALIB, and the STOREDPROC1 only exists in MYLIB. Still I get:
SQL: Error -206, State 42704
Joblog: SQL0206 - USERS in YSYS type *FILE not found.
Ok, so the second parm DBLIB is used to do the 'set schema' but then the 'delete from' does not use the schema but defaults to YSYS, a library that does not exist in my library list for the job and definitely does not contain the database.
So, I try with this instead:
call STOREDPRC1 ('PAUL', DATALIB)
...and I get:
SQL: Error -206, State 42703
Joblog: SQL0206 - Column or global variable DATALIB not found.
As I already indicated, the fix is probably easy as pie but no matter how much time I throw at it, it won't work for me.
Any tips on how to gt this thing on the road are very welcome indeed.
I'd also be interested to hear if a StoredProcedure could be debugged, and if so: how.
-----------------
Happy coding!
Paul
Sweden
Comment