|
View previous topic :: View next topic
|
| Author |
Message |
LearningDb2
New User
Joined: 19 Jun 2009
Posts: 40
Location: India
|
|
|
|
Hi,
I am working on DB2 9.1 z/os
I am trying to call a stored Procedure from REXX.The rexx works fine for procedure with statiC sql.
But in case of dynamic sql it fails giving error -204.This is because it is taking the schema for that case as the TSO login id and hence it exits giving undefined name.
I tried giving
| Quote: |
ADDRESS DSNREXX "EXECSQL SET CURRENT SCHEMA = ''SCHEMA_NAME" "
IF (SQLCODE < 0) THEN DO
/* SAY 'AP_CC_PROC EXECUTION FAILED'
SAY 'ERROR MESSAGE IS:' SQLCODE SQLERRMC
SAY 'SQLCODE IS:' SQLCODE*/
CALL SQLCA
END
|
But it is giving -104 saying
SQLCODE =-104
SQLERRMC =SCHEMA DEGREE,PACKAGESET,RULES,SQLID
SQLERRP =DSNTZSET
SQLERRD =0,0,0,0,0,0
SQLWARN = , , , , , , , , , ,
SQLSTATE=42601
Is there any way of setting the schema through rexx or any other way of handling dynamic sql through rexx.
The part of called proc is like
| Quote: |
SET QUERY_STMT = VAR_L_QUERY_VALUE ;
PREPARE STMT FROM QUERY_STMT ;
|
The variable VAR_L_QUERY_VALUE contains values fetched from a cursor eg:- DELETE FROM TABLE_NAME
For Such cases it gives sqlcode -204(undefined name) while preparing.
Please help and correct me if i am wrong. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| although they both should work : try "set current sqlid" |
|
| Back to top |
|
|
LearningDb2
New User
Joined: 19 Jun 2009
Posts: 40
Location: India
|
|
|
|
Hi GuyC
Thanx for ur reply.I tried setting current sqlid ...it works but my proc is not getting called although it is giving no error.
And my sqlid and schema name are different. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
are you in DB29 NFM ?
just asking because :
| Quote: |
THE CURRENT SCHEMA SPECIAL REGISTER WAS
INTRODUCED IN DB2 FOR Z/OS V8, BUT CANNOT BE
USED INDEPENDANT OF THE CURRENT SQLID SPECIAL
REGISTER UNTIL DB2 V9 FOR Z/OS NEW FUNCTION
MODE. PRIOR TO DB2 V9 NFM, ANY TOOL THAT
BUILDS SQL PROCEDURES MUST SET CURRENT SQLID
TO CONTROL THE DEFAULT SCHEMA QUALIFIER
ASSIGNED TO THE PROCEDURE NAME AT CREATION. |
|
|
| Back to top |
|
|
LearningDb2
New User
Joined: 19 Jun 2009
Posts: 40
Location: India
|
|
|
|
Hi,
I am not sure of DB2 NFM ,,but the rexx is working fine when i gave the schema name in sqlid 
ADDRESS DSNREXX "EXECSQL SET CURRENT SQLID ='"SCHEMA"'"
And the procedure worked successfully.
Thanx GuyC. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| LearningDb2 wrote: |
| I tried setting current sqlid ...it works but my proc is not getting called although it is giving no error. |
| LearningDb2 wrote: |
| the rexx is working fine when i gave the schema name in sqlid |
First answer too fast ?
Anyway, glad it works |
|
| Back to top |
|
|
|
|
