Cursor - how does it behave

[spider007]

Hi all,
I have a peculiar problem. I have a query which returns multiple rows.
I want to perform some operations on the output row by row. For that, i
declared a cursor and fetching the rows. Now the problem is, that if
run the query on DB2 command, I am getting the output in 11 minutes and
as soon as i put the query in SP in cursor and i do a simple OPEN
<cursorname>, it takes more than 2 hrs.

I am running DB2 v8.2 on AIX.

Culd somebody tell me what happens when a cursor is opened??

[Serge Rielau]

spider007 wrote:

Hi all,
I have a peculiar problem. I have a query which returns multiple rows.
I want to perform some operations on the output row by row. For that, i
declared a cursor and fetching the rows. Now the problem is, that if
run the query on DB2 command, I am getting the output in 11 minutes and
as soon as i put the query in SP in cursor and i do a simple OPEN
<cursorname>, it takes more than 2 hrs.

I am running DB2 v8.2 on AIX.

Culd somebody tell me what happens when a cursor is opened??

The biggest influencer for cursor performance is it's BLOCKING behavior.
That is whether DB2 will ship the result set row-by-row or in blocks to
be dispensed by the client.
Blocking can be influenced through a variety of bind options.
Try BLOCKING ALL and STATIC READ ONLY.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[spider007]

Actually, my entire business logic has been built in SP. I call the SP
from SQC and then SP takes over and does all the processing.
What I think might be the cause of the problem is that the SP is by
default FENCED. So the memory (or bufferpool) taken by the cursor is
FENCED memory which is less than the instance memory. Now I dont know
how much memory has been allocated to FENCED user. Is there any way to
find out that?

Also, where can I find the documentation for the BLOCKING in cursor.

Regards
Saurabh Jain

Serge Rielau wrote:

spider007 wrote:

Hi all,
I have a peculiar problem. I have a query which returns multiple rows.
I want to perform some operations on the output row by row. For that, i
declared a cursor and fetching the rows. Now the problem is, that if
run the query on DB2 command, I am getting the output in 11 minutes and
as soon as i put the query in SP in cursor and i do a simple OPEN
<cursorname>, it takes more than 2 hrs.

I am running DB2 v8.2 on AIX.

Culd somebody tell me what happens when a cursor is opened??

The biggest influencer for cursor performance is it's BLOCKING behavior.
That is whether DB2 will ship the result set row-by-row or in blocks to
be dispensed by the client.
Blocking can be influenced through a variety of bind options.
Try BLOCKING ALL and STATIC READ ONLY.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

spider007 wrote:

Actually, my entire business logic has been built in SP. I call the SP
from SQC and then SP takes over and does all the processing.
What I think might be the cause of the problem is that the SP is by
default FENCED. So the memory (or bufferpool) taken by the cursor is
FENCED memory which is less than the instance memory. Now I dont know
how much memory has been allocated to FENCED user. Is there any way to
find out that?

Also, where can I find the documentation for the BLOCKING in cursor.

Have you searched for the options I posted? That should lead to all the
docs?
Anyway I am suspicious that FENCED is the cause for your problem.
Can you give more background? You note "multiple rows". Multiple to me
means < 10. Is this a complex cursor? How many rows are read vs fetched?
(see e.g. statement monitor)?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[spider007]

I have searched for the option "BLOCKING" on google but couldnt find
something appropriate. I would be thankful to you if you could send me
some docs or post a link where I could find more info about the option
which you specified.

"Multiple rows" can vary from thousand to millions of rows on which I
have to perform some business operations.
It is not a complex cursor. I mean the query for the cursor comes out
in approx 10 minutes but the cursor takes around 3 hrs.
Rows read are almost comparable to rows fetched. Also i have run
db2expln on the query and it shows the query is taking all the indexes
which can also be seen by the fact that query run outside SP is taking
11 minutes.

Regards
Saurabh

Serge Rielau wrote:

spider007 wrote:

Actually, my entire business logic has been built in SP. I call the SP
from SQC and then SP takes over and does all the processing.
What I think might be the cause of the problem is that the SP is by
default FENCED. So the memory (or bufferpool) taken by the cursor is
FENCED memory which is less than the instance memory. Now I dont know
how much memory has been allocated to FENCED user. Is there any way to
find out that?

Also, where can I find the documentation for the BLOCKING in cursor.

Have you searched for the options I posted? That should lead to all the
docs?
Anyway I am suspicious that FENCED is the cause for your problem.
Can you give more background? You note "multiple rows". Multiple to me
means < 10. Is this a complex cursor? How many rows are read vs fetched?
(see e.g. statement monitor)?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[spider007]

With "BLOCKING" if you mean "Isolation levels" then I have defined the
select query for the cursor in SP as RS

spider007 wrote:

I have searched for the option "BLOCKING" on google but couldnt find
something appropriate. I would be thankful to you if you could send me
some docs or post a link where I could find more info about the option
which you specified.

"Multiple rows" can vary from thousand to millions of rows on which I
have to perform some business operations.
It is not a complex cursor. I mean the query for the cursor comes out
in approx 10 minutes but the cursor takes around 3 hrs.
Rows read are almost comparable to rows fetched. Also i have run
db2expln on the query and it shows the query is taking all the indexes
which can also be seen by the fact that query run outside SP is taking
11 minutes.

Regards
Saurabh

Serge Rielau wrote:

spider007 wrote:

Actually, my entire business logic has been built in SP. I call the SP
from SQC and then SP takes over and does all the processing.
What I think might be the cause of the problem is that the SP is by
default FENCED. So the memory (or bufferpool) taken by the cursor is
FENCED memory which is less than the instance memory. Now I dont know
how much memory has been allocated to FENCED user. Is there any way to
find out that?
>
Also, where can I find the documentation for the BLOCKING in cursor.

Have you searched for the options I posted? That should lead to all the
docs?
Anyway I am suspicious that FENCED is the cause for your problem.
Can you give more background? You note "multiple rows". Multiple to me
means < 10. Is this a complex cursor? How many rows are read vs fetched?
(see e.g. statement monitor)?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

spider007 wrote:

With "BLOCKING" if you mean "Isolation levels" then I have defined the
select query for the cursor in SP as RS

http://publib.boulder.ibm.com/infoce...6f%63%6b%22%20

Between a thousand an millions is a difference of three magnitudes.
The question si whether a plan that's good for 1000 rows is also good
for 1000000 rows.
Note that unless DB2 injects SORT or TEMP operations the cursor is NOT
materialized on OPEN. Instead it is executed as you fetch.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

spider007 wrote:

With "BLOCKING" if you mean "Isolation levels" then I have defined the
select query for the cursor in SP as RS

Unfortunately, there are two different things where the term "blocking" is
used. One refers to blocking an application/session due to locks; the
other means that rows of a cursor are collected in a single block and send
together over the wire to the client.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[jefftyzzer]

If you're able to, perhaps the easiest way to test the blocking theory
is to add 'FOR READ ONLY' to the end of your cursor's SQL, thus making
the cursor unambiguous and therefore blocking.

On another note, with all due respect, are you certain you need such a
(relatively) restrictive isolation level (RS)? I appreciate the phantom
read issue, but am wondering if perhaps in your situation you can
mitigate, obviate, or otherwise tolerate it (feel free to think of some
more apropos '-ate' words :-). Assuming your million-row-returning
query is running in a batch window, are there concurrent users that you
need to be concerned with? If you're running the query during the
normal (on-line) window, then I guess that's another story.

Regards,

--Jeff

spider007 wrote:

With "BLOCKING" if you mean "Isolation levels" then I have defined the
select query for the cursor in SP as RS

spider007 wrote:

I have searched for the option "BLOCKING" on google but couldnt find
something appropriate. I would be thankful to you if you could send me
some docs or post a link where I could find more info about the option
which you specified.

"Multiple rows" can vary from thousand to millions of rows on which I
have to perform some business operations.
It is not a complex cursor. I mean the query for the cursor comes out
in approx 10 minutes but the cursor takes around 3 hrs.
Rows read are almost comparable to rows fetched. Also i have run
db2expln on the query and it shows the query is taking all the indexes
which can also be seen by the fact that query run outside SP is taking
11 minutes.

Regards
Saurabh

Serge Rielau wrote:

spider007 wrote:
Actually, my entire business logic has been built in SP. I call the SP
from SQC and then SP takes over and does all the processing.
What I think might be the cause of the problem is that the SP is by
default FENCED. So the memory (or bufferpool) taken by the cursor is
FENCED memory which is less than the instance memory. Now I dont know
how much memory has been allocated to FENCED user. Is there any way to
find out that?

Also, where can I find the documentation for the BLOCKING in cursor.
Have you searched for the options I posted? That should lead to all the
docs?
Anyway I am suspicious that FENCED is the cause for your problem.
Can you give more background? You note "multiple rows". Multiple to me
means < 10. Is this a complex cursor? How many rows are read vs fetched?
(see e.g. statement monitor)?
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
>
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/