Posted: Tue Nov 03, 2015 7:08 am Post subject: DB2 MultiFetch via REXX
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:
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
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
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.
Ah, I missed in the manual under FETCH it clearly says:
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.
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