View previous topic :: View next topic
|
Author |
Message |
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
Hi,
I'm trying to connect DB2 thru REXX. With a simple code I wanted and execute but I fail in doing so.
Code: |
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" "DB2"
IF SQLCODE \= "0" THEN
DO
SAY "FAILURE TO CONNECT TO DATABASE"
EXIT 8
END
END
SQLSTMT = "EXECSQL SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1"
ADDRESS DSNREXX "EXECSQL PREPARE S4 FROM :SQLSTMT"
SAY SQLCODE
ADDRESS DSNREXX "EXECSQL DECLARE C4 CURSOR FOR S4"
SAY SQLCODE
ADDRESS DSNREXX "EXECSQL OPEN C4"
SAY SQLCODE
ADDRESS DSNREXX "EXECSQL FETCH C4 INTO :POLICY_NBR"
SAY SQLCODE
ADDRESS DSNREXX "EXECSQL CLOSE C4"
ADDRESS DSNREXX "DISCONNECT"
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
EXIT 0
|
When i put a trace I found that for the "EXECSQL PREPARE S4 FROM C4" is giving me a -104 and subsequent open and fetch are -514 and -516. I'm unable to resolve this issue.
Here is trace.
Code: |
*-* ADDRESS TSO "SUBCOM DSNREXX"
>>> "SUBCOM DSNREXX"
+++ RC(1) +++
*-* IF RC
>>> "1"
*-* THEN
*-* DO
*-* S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
>>> "0"
*-* ADDRESS DSNREXX "CONNECT" "DB2"
>>> "CONNECT DB2"
*-* IF SQLCODE := "0"
>>> "0"
*-* END
*-* SQLSTMT = "EXECSQL SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1"
>>> "EXECSQL SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1"
*-* ADDRESS DSNREXX 'EXECSQL PREPARE S4 FROM :SQLSTMT'
>>> "EXECSQL PREPARE S4 FROM :SQLSTMT"
+++ RC(-1) +++
*-* SAY SQLCODE
>>> "-104"
*-* ADDRESS DSNREXX "EXECSQL DECLARE C4 CURSOR FOR S4"
>>> "EXECSQL DECLARE C4 CURSOR FOR S4"
*-* SAY SQLCODE
>>> "0"
*-* ADDRESS DSNREXX "EXECSQL OPEN C4"
>>> "EXECSQL OPEN C4"
+++ RC(-1) +++
*-* SAY SQLCODE
>>> "-514"
*-* ADDRESS DSNREXX "EXECSQL FETCH C4 INTO :POLICY_NBR"
>>> "EXECSQL FETCH C4 INTO :POLICY_NBR"
+++ RC(-1) +++
*-* IF SQLCODE := "0"
>>> "1"
*-* THEN
*-* DO
*-* SAY "FAILED WITH SQLCODE = " SQLCODE
>>> "FAILED WITH SQLCODE = -516"
FAILED WITH SQLCODE = -516
*-* END
*-* SAY POLICY_NBR
>>> "POLICY_NBR"
POLICY_NBR
*-* ADDRESS DSNREXX "EXECSQL CLOSE C4"
>>> "EXECSQL CLOSE C4"
+++ RC(-1) +++
*-* ADDRESS DSNREXX "DISCONNECT"
>>> "DISCONNECT"
*-* S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
>>> "0"
*-* EXIT 0
>>> "0"
|
Is that there I'm missing a LIBDEF function which gets the DSNREXX connectivity???
Is'nt the function ADDRESS TSO "SUBCOM DSNREXX" gets the exclusive connectivity to DB2 thru REXX.
I'm unable to resolve the error for the "EXECSQL PREPARE S4 FROM C4"
i get a SQLCODE -104 can i get in the description of the error in the trace so that i can get where the error is being done by me.Please help. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
The 1st error in your code is quite easy to spot: your SQL statement is
Code: |
"EXECSQL SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1" |
Do you notice anything unusual in that select ?
The 2nd error is hidden here:
Code: |
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" "DB2"
IF SQLCODE \= "0" THEN
DO
SAY "FAILURE TO CONNECT TO DATABASE"
EXIT 8
END
END |
Sometimes it will work, sometimes not...
Hint: check your own logic: if the DSNREXX environment is not loaded, load it and connect to DB2 else do nothing. Then process the cursor. |
|
Back to top |
|
|
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
Thanks Marso I corrected my code to have the sql statement as
Code: |
"SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1"
|
and also
Code: |
ADDRESS TSO "SUBCOM DSNREXX"
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" "DB2"
|
It worked fine and im able to connect to DB2 thru rexx. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
tecnokrat wrote: |
...and also
Code: |
ADDRESS TSO "SUBCOM DSNREXX"
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" "DB2"
|
It worked fine and im able to connect to DB2 thru rexx. |
Well, I didn't expect you to remove all the IFs...
and one more word: INDENTATION. |
|
Back to top |
|
|
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
Hi , in case if i need to fetch more than one variable so does the fetch statement be made as
Code: |
"EXECSQL FETCH C4 INTO :p1,:p2,:p3,:p4"
|
Irrespective of the host variable names can we give any name or do we need to give explicitly the same as in that of host variable declaration. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Quote: |
and one more word: INDENTATION. |
This is an alien to Programmers at my current shop. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
the only 'names' that db2 knows about are the names used in DDL.
host-variable names are just that. db2 knows nothing and cares even less what naming convention that you use.
the only thing that matters is that the 'name' abides by the rules of the 'host' language. |
|
Back to top |
|
|
|