Page 1 of 1

Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 1:32 am
by dlangmeyer
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?

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 2:13 am
by Akatsukami
What is the message number?

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 7:15 am
by dick scherrer
Hello,

Dynamic SQL?

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 12:12 pm
by Pandora-Box
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

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 7:41 pm
by dlangmeyer
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 ":".

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 8:13 pm
by Pandora-Box
why not use the parm value and check it with the records fetched ?

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 8:22 pm
by dlangmeyer
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.

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 8:36 pm
by Akatsukami
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.

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 8:47 pm
by dlangmeyer
I was hoping that there was a way around the situation. Thanks for your help.

Re: Host variable to limit returned rows in cursor

PostPosted: Fri Apr 26, 2013 10:20 pm
by alexm
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)