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
 
DB2 thru REXX error in prepare statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 thru REXX error in prepare statement
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: 1243
Location: Israel

PostPosted: Thu May 03, 2012 6:36 pm    Post subject: Reply to: DB2 thru REXX error in prepare statement
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    Post subject:
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: 1243
Location: Israel

PostPosted: Thu May 03, 2012 7:30 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Thu May 03, 2012 8:05 pm    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Thu May 03, 2012 8:06 pm    Post subject:
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    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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
This topic is locked: you cannot edit posts or make replies. Rexx to execute spool commands upendrasri CLIST & REXX 5 Wed Oct 04, 2017 6:54 am
This topic is locked: you cannot edit posts or make replies. Rexx to execute spool commands upendrasri CLIST & REXX 47 Fri Sep 29, 2017 1:08 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm

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