MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DB2 MultiFetch via REXX

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
RoyKMathur
Beginner


Joined: 05 Jan 2012
Posts: 43
Topics: 10

PostPosted: Tue Nov 03, 2015 7:08 am    Post subject: DB2 MultiFetch via REXX Reply with quote

I am attempting to write a REXX program that will execute a DB2 MultiFetch; getting an SQLCODE = -104 on the PREPARE statement. I've removed the error-trapping logic in the below code to make it easier to read. I've been scouring the manuals and the Internet but all to no avail. Is MultiFetch even possible with REXX ?

The z/OS DB2 Application SQL Reference for v11 says:
Quote:
DB2 REXX applications do not support cursors that are declared WITH
ROWSET POSITIONING. To allow a cursor for a SELECT statement in a
DB2 REXX application to be used with row-positioned or
rowset-positioned FETCH statements, specify WITH ROWSET
POSITIONING in the attribute string of the PREPARE statement for the
SELECT statement.


Code:
sql = "SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = 'DB2TEST' FETCH FIRST 200 ROWS ONLY WITH UR"
 
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"                                                         
                                                                                                           
ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA USING NAMES ATTRIBUTES WITH ROWSET POSITIONING FROM :SQL"
                                                                                                           
ADDRESS DSNREXX "EXECSQL OPEN C1"                                                                           
                                                                                                           
ADDRESS DSNREXX "EXECSQL FETCH NEXT ROWSET FROM C1 FOR 100 ROWS USING DESCRIPTOR :OUTSQLDA"                 
                                                                                                           
ADDRESS DSNREXX "EXECSQL CLOSE C1"

Here's the results and error:
[quote]after DECLARE, rc=0, sqlcode=0, sqlstate=00000
after PREPARE, rc=-1, sqlcode=-104, sqlstate=42601

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "WITH". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: <HOST-VARIABLE>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNTZPRP SQL PROCEDURE DETECTING ERROR
after OPEN , rc=-1, sqlcode=-514, sqlstate=26501 [/quote
Back to top
View user's profile Send private message
RoyKMathur
Beginner


Joined: 05 Jan 2012
Posts: 43
Topics: 10

PostPosted: Tue Nov 03, 2015 9:08 am    Post subject: Reply with quote

I got an answer from IDUG.ORG. I needed to replace the "WITH ROWSET POSITIONING" with a REXX variable ":SQLATTR" that contained the "WITH ROWSET POSITIONING" string. Weird, but it worked! Now if only I can get the FETCH to work.
Back to top
View user's profile Send private message
RoyKMathur
Beginner


Joined: 05 Jan 2012
Posts: 43
Topics: 10

PostPosted: Tue Nov 03, 2015 9:46 am    Post subject: Reply with quote

Ah, I missed in the manual under FETCH it clearly says:

Quote:

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. Multiple row fetch is not supported in REXX, Fortran, or SQL Procedure applications39. The FETCH statement with the WITH CONTINUE clause is not supported in REXX.


So it's impossible.
Back to top
View user's profile Send private message
misi01
Intermediate


Joined: 02 Dec 2002
Posts: 332
Topics: 80
Location: Stockholm, Sweden

PostPosted: Tue Nov 17, 2015 7:38 am    Post subject: Reply with quote

That's the age-old problem with computing.
Am I doing something wrong or is it a case that what I want to do, CAN'T be done
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group