MVSFORUMS.com

[Pknair]

Anyone has an idea of how to do a mutli row fetch using REXX. I'm able to declare a cursor. But the fetch statement always gives me a -104.

SQLSTMT='SELECT CREATOR , NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = 'XXXXX'"

SQLATTR = " WITH ROWSET POSITIONING"

ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"

ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA ATTRIBUTES :SQLATTR FROM :SQLSTMT"

ADDRESS DSNREXX "EXECSQL OPEN C1"

ADDRESS DSNREXX "EXECSQL FETCH NEXT ROWSET FROM C1 FOR 100 ROWS USING DESCRIPTOR :OUTSQLDA"

The above fetch always gives me -104.

Thanks in advance

[jsharon1248]

Is that the exact code? Why is there a single quote at the beginning of the SQLSTMT assignment and a double quote at the end?

[Nic Clouston]

Which statement is giving the problem?
My FETCH statement looks like this:

[Pknair]

Hi JSharon
That was a typo. the SQLSTMT statement did not have a singl quote.

Hi Nic
The Fetch that you are executing retireves one row at a time. Whereas the one that I am trying to execute retrieves multiple rows. It uses the ROWSET keyword in the fetch and the DECLARE cursor.

[Nic Clouston]

I have not seen the ROWSET keyword in relation to Rexx but then I am only up to Version 7. If you are sure you can use ROWSET in a Rexx program then I would double-check the syntax with the manual. Unfortunately I do not have a V8 or a messages manual on me so I cannot help further at this stage.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

[jsharon1248]

Looking at the syntax in the manual, it looks like the FETCH syntax should be INTO DESCRIPTOR, not USING DESCRIPTOR.

[Pknair]

I tried using the "INTO" keyword but the result is still the same. I'm getting SQLCODE of -104 when trying to execute the FETCH. All the other SQLs gave me an SQLCODE of "0".

[jsharon1248]

2 things. First, I didn't read far enough down in the FETCH documentation. Missed this:

[Pknair]

I'm sorry if I have in any of my previous replies , unintentionally offended you. Infact I do appreciate all the help being extended by everyone.

The version of DB2 is 8 The error is as given below

SQLCODE =-104
SQLERRMC =ROWSET C1, C2, ... C200
SQLERRP =DSNTZFET
SQLERRD =0,0,0,0,0,0
SQLWARN = , , , , , , , , , ,

[jsharon1248]

No offense at all. Some people are a little over-sensitive when you ask a question about versions if they're using a feature only available in a newer version.

I'm at a loss here. It almost looks like DB2 does not like the ROWSET keyword. Just for grins, would you be willing to change this to a singular FETCH just to see if that works? At least we'd be able to narrow down the problem to the multiple row fetching functionality.

[Nic Clouston]

I just had a horrible little thought - is it possible that the version of DSNREXX you are you using is at the right level for DB2 V8? You would probably have to have your sysprogs or DBAs check that info. I do not know if an incompatible version would cause that error - possiby not but if all else fails...
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

[Pknair]

I'm not sure about the DSNREXX version, However As for JSHARON's question I did try with the singleton select and it does work fine

[jsharon1248]

Something else you might try is to put this functionality into a COBOL pgm as static SQL to see if that works.

[Pknair]

The cobol program works fine too.

Does anyone know the different statements that can be passed in the ATTRIBUTES

[jsharon1248]

The ATTRIBUTES syntax in the PREPARE looks fine. I can only suggest two more things. First, try to code the WITH ROWSET POSITIONING directly in the DECLARE cursor, and second, try a cursor name in the C51-C100 range. If neither of those works, you might have the nightmare that Nic Clouston presented.