Posted: Tue Nov 04, 2014 9:35 am Post subject: How do you code REXX DB2 to SET SCHEMA (Sqlcode = -104)
I have a REXX DB2 program where I'd need to execute a SET SCHEMA statement; however no matter what I try I get an SQLCODE = -104. The "DB2 Programming and Application Guide" (for z/OS DB2 v10) says "The following dynamic statements must be executed using EXECUTE IMMEDIATE or PREPARE and EXECUTE under DSNREXX:" and then specifies SET SCHEMA as one of them.
Does anyone have a working example of how to code this?
On a totally different subject, what happens if one doesn't properly exit the DB2 interface by not executing a DISCONNECT and/or not executing RXSUBCOM DELETE?
Here's a working extract from my test program.
Code:
/* rexx */
parse arg arg_ssid
/*----------------------
| enter db2 interfaces
|-----------------------*/
'SUBCOM DSNREXX'
SAY 'SUBCOM DSNREXX, RC='RC
IF RC THEN DO
RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
SAY 'RXSUBCOM ADD, RC='RC
end
ADDRESS DSNREXX
SAY 'ADDRESS DSNREXX, RC='RC
ADDRESS DSNREXX 'CONNECT' ARG_SSID
SAY "ADDRESS DSNREXX CONNECT "ARG_SSID", RC="RC
"EXECSQL SET CURRENT PACKAGESET = 'DSNREXCS'"
say "EXECSQL SET CURRENT PACKAGESET, rc="rc
/*---------------------
| test setting schema
|----------------------*/
"EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA = 'TEST'"
say 'EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA, rc='rc', sqlcode='sqlcode
"EXECSQL SET CURRENT SCHEMA = 'TEST'"
say 'EXECSQL SET CURRENT SCHEMA, rc='rc', sqlcode='sqlcode
INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 452 /* char */
INSQLDA.1.SQLLEN = 4
INSQLDA.1.SQLDATA = 'TEST'
INSQLDA.1.SQLIND = 0
"EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA = ?"
say 'EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA, rc='rc', sqlcode='sqlcode
INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 448 /* varchar */
INSQLDA.1.SQLLEN = 4
INSQLDA.1.SQLDATA = 'TEST'
INSQLDA.1.SQLIND = 0
"EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA = ?"
say 'EXECSQL EXECUTE IMMEDIATE SET CURRENT SCHEMA, rc='rc', sqlcode='sqlcode
I figured it out...by reading the manual more slowly and finally noticed it does not say IMMEDIATE when you use a PREPARE. I could not get it to work using EXECUTE IMMEDIATE.
This works:
Code:
sqlstmt = "SET CURRENT SCHEMA = 'DB2DVL3'"
outsqlda. =
"EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"
say 'EXECSQL PREPARE S1, rc='rc', sqlcode='sqlcode
"EXECSQL EXECUTE S1"
say 'EXECSQL S1, rc='rc', sqlcode='sqlcode
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
