Host variable to limit returned rows in cursor



IBM's flagship relational database management system

Host variable to limit returned rows in cursor

Postby dlangmeyer » Fri Apr 26, 2013 1:32 am

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?
dlangmeyer
 
Posts: 10
Joined: Thu Sep 27, 2012 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Host variable to limit returned rows in cursor

 

Re: Host variable to limit returned rows in cursor

Postby Akatsukami » Fri Apr 26, 2013 2:13 am

What is the message number?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Host variable to limit returned rows in cursor

Postby dick scherrer » Fri Apr 26, 2013 7:15 am

Hello,

Dynamic SQL?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Host variable to limit returned rows in cursor

Postby Pandora-Box » Fri Apr 26, 2013 12:12 pm

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
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times

Re: Host variable to limit returned rows in cursor

Postby dlangmeyer » Fri Apr 26, 2013 7:41 pm

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 ":".
dlangmeyer
 
Posts: 10
Joined: Thu Sep 27, 2012 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Host variable to limit returned rows in cursor

Postby Pandora-Box » Fri Apr 26, 2013 8:13 pm

why not use the parm value and check it with the records fetched ?
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times

Re: Host variable to limit returned rows in cursor

Postby dlangmeyer » Fri Apr 26, 2013 8:22 pm

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
 
Posts: 10
Joined: Thu Sep 27, 2012 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Host variable to limit returned rows in cursor

Postby Akatsukami » Fri Apr 26, 2013 8:36 pm

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.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Host variable to limit returned rows in cursor

Postby dlangmeyer » Fri Apr 26, 2013 8:47 pm

I was hoping that there was a way around the situation. Thanks for your help.
dlangmeyer
 
Posts: 10
Joined: Thu Sep 27, 2012 9:01 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Host variable to limit returned rows in cursor

Postby alexm » Fri Apr 26, 2013 10:20 pm

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)
User avatar
alexm
 
Posts: 33
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post