IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 thru REXX error in prepare statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Thu May 03, 2012 3:09 pm
Reply with quote

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

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Thu May 03, 2012 6:36 pm
Reply with quote

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

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Thu May 03, 2012 7:11 pm
Reply with quote

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. icon_smile.gif
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Thu May 03, 2012 7:30 pm
Reply with quote

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. icon_smile.gif


Well, I didn't expect you to remove all the IFs...
and one more word: INDENTATION.
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Thu May 03, 2012 7:44 pm
Reply with quote

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

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 03, 2012 8:05 pm
Reply with quote

Quote:
and one more word: INDENTATION.
This is an alien to Programmers at my current shop.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 03, 2012 8:06 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Running REXX through JOB CLIST & REXX 13
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts run rexx code with jcl CLIST & REXX 15
Search our Forums:

Back to Top