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
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*/
But it is giving -104 saying
SQLERRMC =SCHEMA DEGREE,PACKAGESET,RULES,SQLID
SQLWARN = , , , , , , , , , ,
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
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.
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.