MVSFORUMS.com

Pknair · Beginner Joined: 29 Nov 2005 Posts: 26 Topics: 9

Hi

I have the following cursor

kolusu · Posted: Fri Mar 10, 2006 11:36 am Post subject:

Pknair,

we really cannot help not knowing how the indexes are defined on each table. btw did you realize how many tables are involved in the join ? Even if one table hits a tablespace scan you will have problems. My advice is to talk to your DBA.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

pknair,
If "FETCH FIRST 10 ROWS ONLY" is in effect and the OPEN is complete, then FETCH should not take time. What else is going on in the program logic, what is going on in the SP with the result - ie, is it a RESULT SET or not, or maybe the caller of the SP is not obtaining the results. Check the program logic, something must be holding it. Thanks

Pknair · Beginner Joined: 29 Nov 2005 Posts: 26 Topics: 9

Hi Kolusu

Thank you for a quick reply

You are right about that.

My understanding is that once the Open Cursor statement is successful then the Query is materialised and the result set is created. A fetch only goes against that result set. In the second senario when I included the FETCH FIRST 10 ROWS ONLY statement the OPEN CURSOR worked fine but it started clocking on the fetch. That was why I was confused.

I verified the explain and it does not show any tablespace scans.

Pknair · Beginner Joined: 29 Nov 2005 Posts: 26 Topics: 9

Hi Suresh

In between the OPEN and the FETCH there is only one statement which is an SQLCODE verification for the OPEN statement which is

IF SQLCODE NOT = ZEROES
PERFORM XXX-PARA
END-IF

It clocks at the very first FETCH

Thanks

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

Pknair,
The issue is more in line with Kolusu's explanation. Its good to have the SQL tested thru QMF/SPUFI with fetch 1 or 2 rows to take an initial shot with the SQL. Thanks

Pknair · Beginner Joined: 29 Nov 2005 Posts: 26 Topics: 9

Hi Kolusu/Suresh

My uderstanding is that an OPEN cursor statement will materialise the CURSOR sql statements(This is when the indexes are used), does it not mean that a Fetch should only read these records from the buffer.

In this case the OPEN is successfull but it clocks in Fetch. Which means that the records have already been retrieved from the respective tables but the reading of the records from the materialised cursor is clocking.

Please help...

kolusu · Posted: Mon Mar 13, 2006 7:07 pm Post subject:

Pknair,

Our onsite DBA will be better person to answer your question as he has all the information needed. Did you try to contact him?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

issac1029 · Intermediate Joined: 10 Dec 2005 Posts: 159 Topics: 75

What will be happen if you want to fetch 10 rows,but there are only 9 row in condition?

kolusu · Posted: Tue Mar 14, 2006 8:46 am Post subject:

Pknair · Beginner Joined: 29 Nov 2005 Posts: 26 Topics: 9

I did a little bit of research and got the following. The SQL's in the CURSOR are not fully materialised when an OPEN CURSOR statement is executed. It is only done on or before the first FETCH statement.

So even if it traverses the OPEN statement quickly it could get hung up on the first FETCH statement.

Thank you everyone for your answers. It was educative
Pknair