IBM Mainframe Forum

I have the following cursor:
EXEC SQL
DECLARE CSR-TEST CURSOR WITH HOLD FOR
SELECT DEPT,EMP_NUM FROM TBL_EMP
FETCH FIRST 1000 ROWS ONLY
END-EXEC

I would like to have the rows returned be controlled by host variable.
The statement will not get past the pre-compiler.
":WS-LIMIT" failed
Tried a couple different definitions
05 WS-LIMIT PIC 9(9) OR PIC S9(9) OR PIC S9(9) COMP-3.

Beat around some manuals, but I am missing something - fill in your joke here - ha ha

Is this even possible?

What is the message number?

Hello,

Dynamic SQL?

What you could also do is if you order by the cursor and check the records fetched to the value dynamically set

Just check if this could be adapted to your need

First of all - thanks for the quick reply.

Not dynamic, the cursor is ordered. What I am doing is getting the record count, then processing the cursor 1000 rows at a time.
I would like to be able to make the value a parm, so if I need to adjust the size I would just change the parm and not have to re-compile

Trying to use variable in the cursor declare, I get the following from the pre-compiler:
05 WS-MAX-COUNT PIC 9(09) VALUE ZEROES.
FETCH FIRST :WS-MAX-COUNT ROWS ONLY

DSNH104I E DSNHPARS LINE 215 COL 26 ILLEGAL SYMBOL ":".

why not use the parm value and check it with the records fetched ?

If I have 50,000 rows to return, I might want to adjust the rows returned to 2000, but the 1000 is hard coded in the cursor declare.
This would require a recompile. If the number of rows in the declare could be a run time variable, then I could adjust on the fly.
If I were to bring in all 50,000 it would impact the other DB2 jobs running as it would take up more memory.

dlangmeyer wrote:First of all - thanks for the quick reply.

Not dynamic, the cursor is ordered. What I am doing is getting the record count, then processing the cursor 1000 rows at a time.
I would like to be able to make the value a parm, so if I need to adjust the size I would just change the parm and not have to re-compile

Trying to use variable in the cursor declare, I get the following from the pre-compiler:
05 WS-MAX-COUNT PIC 9(09) VALUE ZEROES.
FETCH FIRST :WS-MAX-COUNT ROWS ONLY

DSNH104I E DSNHPARS LINE 215 COL 26 ILLEGAL SYMBOL ":".

As you will see from the explanation of the message, you cannot select the number of rows to be fetched at run-time. You'll have to continue the way that you are.

I was hoping that there was a way around the situation. Thanks for your help.

An option would be ROWSET processing (however, depending on the row size you'll probably need a huge working storage to store n rowset rows)