Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SET SCHEMA THROUGH REXX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Thu Nov 26, 2009 10:34 pm    Post subject: SET SCHEMA THROUGH REXX
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 26, 2009 10:42 pm    Post subject:
Reply with quote

although they both should work : try "set current sqlid"
Back to top
View user's profile Send private message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Fri Nov 27, 2009 11:54 am    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 27, 2009 1:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Fri Nov 27, 2009 5:09 pm    Post subject:
Reply with quote

Hi,
I am not sure of DB2 NFM ,,but the rexx is working fine when i gave the schema name in sqlid icon_lol.gif

ADDRESS DSNREXX "EXECSQL SET CURRENT SQLID ='"SCHEMA"'"

And the procedure worked successfully.

Thanx GuyC.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 27, 2009 5:15 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Looping REXX - unable to interrupt RAVISANKAR07 CLIST & REXX 3 Wed Dec 06, 2017 1:08 pm
No new posts The same REXX program doesnt work for... jackzhang75 CLIST & REXX 5 Wed Dec 06, 2017 2:51 am
No new posts Rexx - File tailoring samzee71 CLIST & REXX 10 Thu Nov 30, 2017 10:47 pm
This topic is locked: you cannot edit posts or make replies. Rexx to Grab ISMF Information. upendrasri All Other Mainframe Topics 16 Sun Nov 26, 2017 12:35 am
No new posts Rexx to send an email without SMTP Se... upendrasri All Other Mainframe Topics 12 Sun Nov 19, 2017 7:59 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us