What you have documented is essentially what I was surmising when I
said I thought I knew the problem. (How did I let "no" for "know"
slip by? Sloppy!) I ran into this very problem recently. I have not
used SQL in Rexx for years and ran into the same problem. It took me
ages to determine the cause of RC being -10 because there is NO
information about the error in the job log. I had to use the SQL
variables provided by Rexx to fabricate my own message to see what it
was on about:
In this case my code was:
address execsql,
'DESCRIBE TABLE :table INTO :DA'
Running it resulted in:
7 *-* Address execsql 'DESCRIBE TABLE :table INTO :DA';
+++ RC(-10)
With nothing in the job log. Dumping the Rexx SQL variables showed:
SQLSTATE: 42601
SQLCODE: -104
SQLERRP: QSQPLIST
SQLERRMC: DESCRIBE EXECSQL
SQLERRD.1: 0
SQLERRD.2: 0
SQLERRD.3: 0
SQLERRD.4: 0
SQLERRD.5: 1
SQLERRD.6: 0
Which tells me it's a syntax error but the statement looks fine. It
addresses the SQL environment and passes a valid SQL statement.
What's wrong? I added code to fabricate an error message from these
variables:
if RC <> 0 then
do
MSGID = 'SQL0'STRIP(ABS(SQLCODE))
ADDRESS COMMAND,
'SNDPGMMSG MSGID(&MSGID) MSGF(QSQLMSG) MSGDTA(&SQLERRMC)'
end
Now the job log shows:
Token DESCRIBE was not valid. Valid tokens: EXECSQL.
Which tells me that the SQL environment is getting the SQL statement
but that it also wants EXECSQL even though it's already present on
the line.
I tried running the Rexx program specifying CMDENV(*EXECSQL). Same
error. Very odd. I noticed that the Rexx SQL examples in the IBM
documentation showed essentially what you have done. Their code did
an ADDRESS *COMMAND for each CL command then simply EXECSQL for each
SQL statement. This, of course fails to run unless you also specify
CMDENV(*EXECSQL) on the STRREXPRC command.
Thus I determined that you need to specify EXECSQL twice in order to
make it work. Either:
1) Specify EXECSQL on each SQL line and specify CMDENV(*EXECSQL) on
invocation
2) Specify ADDRESS *EXECSQL (or EXECSQL) before any SQL statements
and specify EXECSQL on each SQL line
3) Specify ADDRESS *EXECSQL EXECSQL 'sql statement' on each line
Given that Rexx treats *EXECSQL and EXECSQL as synonyms this gives:
ADDRESS EXECSQL EXECSQL 'DESCRIBE TABLE :TABLE INTO :DA'
which is patently silly.
Now, my memory is usually very good and I do not recall having to
code double EXECSQL keywords in version 3 which was when I last used
Rexx and SQL. However, I no longer have the code I used back then and
it is quite possible I adopted the:
ADDRESS *EXECSQL
EXECSQL 'sql stmt'
format, in which case I may not have noticed this behaviour.
I noticed that Rexx and SQL would also accept:
ADDRESS *EXECSQL 'EXECSQL DESCRIBE TABLE :table INTO :da'
Note that the second EXECSQL is inside the single quotes?
Syntactically, this is much like an HLL:
C/EXEC SQL
C+ DESCRIBE TABLE :TABLE INTO :DA
C/END-EXEC
So on one level it may make some sense to code it this way. However,
to my way of thinking this just makes it harder for people to use SQL
and Rexx and I consider it a defect either in the design of the Rexx
SQL interface or in the implementation.
The Rexx standard states that "A command that is not part of an
ADDRESS instruction is processed in the ACTIVE environment."
To me that says once ADDRESS *EXECSQL has been issued all commands
(i.e., things that are not Rexx statements) should be passed to that
environment. This appears to be the case with SQL except that SQL
expects and requires the first token in that statement to be EXECSQL.
It seems reasonable to me that once ADDRESS *EXECSQL has been issued
that 'valid SQL statement' should be passed and processed
successfully without the requirement for EXECSQL on the SQL statement
itself.
The documentation (DB2 SQL Programming with Host Languages) does show
EXECSQL as the first token on all SQL statements. It also states:
"Each SQL statement in a Rexx procedure must begin with EXECSQL (in
any combination of uppercase and lowercase letters), followed by either:
o The SQL statement enclosed in single or double quotes, or
o A REXX variable containing the statement. Note that a colon must
not precede a REXX variable when it contains an SQL statement."
So although I consider the requirement for double EXECSQL keywords
inane I think the response to any DCR or complaint will be "working
as designed". I can tolerate the form:
ADDRESS EXECSQL
... bunch of Rexx statements
EXECSQL 'sql stmt'
much better than ADDRESS EXECSQL EXECSQL 'sql stmt' which I find
simply stupid.
More comments follow:
On 04/04/2008, at 1:11 AM, <lgoodbar@xxxxxxxxxxxxxx>
<lgoodbar@xxxxxxxxxxxxxx> wrote:
Simon,
Here is a code excerpt that works:
------
Address execsql
Address command "clrpfm file1"
Mystatement="select axascd from vngdbdta/dbaxrep where axazcd='WV'"
Execsql "set option commit=*none, naming=*sys;"
Execsql "prepare s1 from :mystatement;"
Execsql "declare c1 cursor for s1;"
Execsql "open c1;"
Execsql "fetch c1 into :currentyear;"
Execsql "close c1;"
Address command "strqmqry qmqry(s9sales)""
------
This code does not.
------
Address command
"clrpfm file1"
Mystatement="select axascd from vngdbdta/dbaxrep where axazcd='WV'"
Address Execsql "set option commit=*none, naming=*sys;"
Address Execsql "prepare s1 from :mystatement;"
Address Execsql "declare c1 cursor for s1;"
Address Execsql "open c1;"
Address Execsql "fetch c1 into :currentyear;"
Address Execsql "close c1;"
"strqmqry qmqry(s9sales)""
--------
Yes, in this form you need to code
Address execsql execsql 'sql stmt'
Rather stupid looking syntax.
Based on your and Tom's later email (active versus alternate
environments), and sending statements to the current environment. This
does not work for SQL. Even with the execsql environment specified,
one
must explicitly specify the execsql environment when running SQL
statements. (See next code block.)
------
address command
"clrpfm s9sales"
mystatement="select axascd from vngdbdta/dbaxrep where axazcd='WV'"
address execsql
"set option commit=*none, naming=*sys;"
"prepare s1 from :mystatement;"
"declare c1 cursor for s1;"
"open c1;"
"fetch c1 into :currentyear;"
"close c1;"
Address /* Swap to command environment. */
"strqmqry qmqry(s9sales)"
--------
True, but you and I (and I suspect any reasonable programmer) would
expect it to work because the current environment has been set to
EXECSQL and all subsequent commands are valid SQL statements.
This works.
--------
address command
"clrpfm s9sales"
mystatement="select axascd from vngdbdta/dbaxrep where axazcd='WV'"
address execsql
execsql "set option commit=*none, naming=*sys;"
execsql "prepare s1 from :mystatement;"
execsql "declare c1 cursor for s1;"
execsql "open c1;"
execsql "fetch c1 into :currentyear;"
execsql "close c1;"
address /* Swap to command environment. */
"strqmqry qmqry(s9salesqm)"
-------
Because now you have satisfied the Rexx SQL syntax requirement of
"each statement must begin with EXECSQL".
I was curious specifically on using the execsql environment, the rules
for its interaction with other environments. As your next email
inferred, I am using both the command and execsql environments in the
same script.
The EXECSQL environment behaviour seems inconsistent with that of the
COMMAND environment. I do not have to code ADDRESS COMMAND COMMAND
'DSPMSG' so why should I have to code ADDRESS EXECSQL EXECSQL ...?
I must test the behaviour of the *CPICOMM environment. Perhaps it has
a similar silly requirement.
Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Simon Coulter
Sent: Wednesday, April 02, 2008 18:44
To: Midrange Systems Technical Discussion
Subject: Re: Rexx address command versus execsql
On 03/04/2008, at 6:08 AM, <lgoodbar@xxxxxxxxxxxxxx>
<lgoodbar@xxxxxxxxxxxxxx> wrote:
If I say "ADDRESS EXECSQL" at the top of a Rexx program, then I can
run
SQL statements fine. Prefixing i5/OS commands with ADDRESS COMMAND
works
well also.
However, if I use just ADDRESS COMMAND at the beginning of a program,
then ADDRESS EXECSQL statements return with RC(-10).
I think I no the problem. I also have an OPINION on the behaviour of
EXECSQL in Rexx. Show me some source so I can be sure.
Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists
http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------
midrange.com
