what happens in opening cursor in detail?

Jeremy Huang

what happens in opening cursor in detail?
Dear listers,

In DB2 for z, when a program opens a cursor, I assume that DB2 returns the
whole result set. So there are the following questions:

1. What happens if the result set has millions of records?

2. Where does the result set locate? In DBM1 storage or working data sets?

3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the
whole result set or only n rows of the result set present when opening the
cursor? What will happen when fetch the n+1 row?

Thanks in advance.

Jeremy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: what happens in opening cursor in detail?
(in response to Jeremy Huang)
Your assumption is wrong. DB2 does not always return the whole result set
(This causes junior programmers no end of grief....) it depends on what,
exactly, your cursor is doing and how the indices are and how the stats
are on the data and finally , of course, the access path and SQL. For
example for a 2 million row table perfectly in clustered sequence using an
order by based on the clustering index then DB2 might only fetch the first
rows that qualify....however the moment it has to do a sort for *any*
reason... then it must fetch all rows and externalize the result set into
the DSNDB07 work spaces. Thats why you should have LOTS of these babys!!!!
Now the optimize for nn rows only affects the optimization so depending on
how many rows must really be put into DSNDB07 you might still get millions
of records there....even if ou have optimize for row...DB2 simply takes
its best attempt to get the quickest way to 1 row from the answer set -
Adding DISTINCT, GROUP, UNION etc will still kill ya...



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Jeremy Huang <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
03.12.2009 07:57
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] what happens in opening cursor in detail?






Dear listers,

In DB2 for z, when a program opens a cursor, I assume that DB2 returns the
whole result set. So there are the following questions:

1. What happens if the result set has millions of records?

2. Where does the result set locate? In DBM1 storage or working data sets?

3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the
whole result set or only n rows of the result set present when opening the
cursor? What will happen when fetch the n+1 row?

Thanks in advance.

Jeremy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: what happens in opening cursor in detail?
(in response to Roy Boxwell)
Hi Jeremy

"what happens in opening cursor in detail?" - IT DEPENDS (sorry, couldn't
resist!)

In simple terms, if DB2 can materialize your results set directly from the
table(s) in question, than that's what will happen and an OPEN CURSOR will
do almost no work. The full results set will NOT be materialized

HOWEVER

If DB2 CANNOT get your result set from the table(s) directly, then the OPEN
CURSOR will need to do that - in your work database (DSNDB07). And yes, ALL
of the rows will be materialized - even if there are millions (and millions)
of them.

OPTIMZE FOR will NOT affect the number of rows in this materialized set, it
only affects the access path choices to get the rows

FETCH FIRST though WILL limit the number of rows materialized to "n"

Hope this helps

Phil Grainger
Grainger Database Solutions Ltd


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeremy Huang
Sent: 03 December 2009 06:57
To: [login to unmask email]
Subject: [DB2-L] what happens in opening cursor in detail?

Dear listers,

In DB2 for z, when a program opens a cursor, I assume that DB2 returns the
whole result set. So there are the following questions:

1. What happens if the result set has millions of records?

2. Where does the result set locate? In DBM1 storage or working data sets?

3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the
whole result set or only n rows of the result set present when opening the
cursor? What will happen when fetch the n+1 row?

Thanks in advance.

Jeremy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: what happens in opening cursor in detail?
(in response to Phil Grainger)
I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result set
materialization. If I want the first 10 rows of a result set that is ordered
by a non-indexed column (or set of columns), and if the target table has 1
million qualifying rows, DB2 is going to have to sort those 1 million rows
before it can do the FETCH FIRST 10 ROWS ONLY.

FETCH FIRST n ROWS ONLY would reduce the number of values generated by a
non-correlated subquery used in an IN predicate, and I suppose that can be
thought of as a form of materialization reduction.

Robert


On Thu, Dec 3, 2009 at 3:52 AM, Phil Grainger <
[login to unmask email]> wrote:

> Hi Jeremy
>
> "what happens in opening cursor in detail?" - IT DEPENDS (sorry, couldn't
> resist!)
>
> In simple terms, if DB2 can materialize your results set directly from the
> table(s) in question, than that's what will happen and an OPEN CURSOR will
> do almost no work. The full results set will NOT be materialized
>
> HOWEVER
>
> If DB2 CANNOT get your result set from the table(s) directly, then the OPEN
> CURSOR will need to do that - in your work database (DSNDB07). And yes, ALL
> of the rows will be materialized - even if there are millions (and
> millions)
> of them.
>
> OPTIMZE FOR will NOT affect the number of rows in this materialized set, it
> only affects the access path choices to get the rows
>
> FETCH FIRST though WILL limit the number of rows materialized to "n"
>
> Hope this helps
>
> Phil Grainger
> Grainger Database Solutions Ltd
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeremy Huang
> Sent: 03 December 2009 06:57
> To: [login to unmask email]
> Subject: [DB2-L] what happens in opening cursor in detail?
>
> Dear listers,
>
> In DB2 for z, when a program opens a cursor, I assume that DB2 returns the
> whole result set. So there are the following questions:
>
> 1. What happens if the result set has millions of records?
>
> 2. Where does the result set locate? In DBM1 storage or working data sets?
>
> 3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the
> whole result set or only n rows of the result set present when opening the
> cursor? What will happen when fetch the n+1 row?
>
> Thanks in advance.
>
> Jeremy
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
> presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
> Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for
> every
> level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
> presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
> Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for
> every level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

M. Khalid Khan

Re: what happens in opening cursor in detail?
(in response to Robert Catterall)
That's true but there is further twist in version 9 - it may use "smart
sort" where it goes through the million qualifying rows exactly once
picking up the first 10 per the order by specification. No intermediate
runs or merges will be needed for such sort. I won't call it
materialization although some sort of sort (!) is involved.

Khalid




I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result
set materialization. If I want the first 10 rows of a result set that is
ordered by a non-indexed column (or set of columns), and if the target
table has 1 million qualifying rows, DB2 is going to have to sort those 1
million rows before it can do the FETCH FIRST 10 ROWS ONLY.
<snip>


HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at (312)
653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in
Oklahoma; or (972)766-6900 in Texas.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: what happens in opening cursor in detail?
(in response to M. Khalid Khan)
Good point Robert



I was thinking of using GET FIRST "n" to limit the size of a static
scrollable cursor



I knew I shouldn't have taken all those weeks off....



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Catterall
Sent: 04 December 2009 16:01
To: [login to unmask email]
Subject: Re: [DB2-L] what happens in opening cursor in detail?



I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result set
materialization. If I want the first 10 rows of a result set that is ordered
by a non-indexed column (or set of columns), and if the target table has 1
million qualifying rows, DB2 is going to have to sort those 1 million rows
before it can do the FETCH FIRST 10 ROWS ONLY.

FETCH FIRST n ROWS ONLY would reduce the number of values generated by a
non-correlated subquery used in an IN predicate, and I suppose that can be
thought of as a form of materialization reduction.

Robert


On Thu, Dec 3, 2009 at 3:52 AM, Phil Grainger
<[login to unmask email]> wrote:

Hi Jeremy

"what happens in opening cursor in detail?" - IT DEPENDS (sorry, couldn't
resist!)

In simple terms, if DB2 can materialize your results set directly from the
table(s) in question, than that's what will happen and an OPEN CURSOR will
do almost no work. The full results set will NOT be materialized

HOWEVER

If DB2 CANNOT get your result set from the table(s) directly, then the OPEN
CURSOR will need to do that - in your work database (DSNDB07). And yes, ALL
of the rows will be materialized - even if there are millions (and millions)
of them.

OPTIMZE FOR will NOT affect the number of rows in this materialized set, it
only affects the access path choices to get the rows

FETCH FIRST though WILL limit the number of rows materialized to "n"

Hope this helps

Phil Grainger
Grainger Database Solutions Ltd



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeremy Huang
Sent: 03 December 2009 06:57
To: [login to unmask email]
Subject: [DB2-L] what happens in opening cursor in detail?

Dear listers,

In DB2 for z, when a program opens a cursor, I assume that DB2 returns the
whole result set. So there are the following questions:

1. What happens if the result set has millions of records?

2. Where does the result set locate? In DBM1 storage or working data sets?

3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the
whole result set or only n rows of the result set present when opening the
cursor? What will happen when fetch the n+1 row?

Thanks in advance.

Jeremy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L




--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: what happens in opening cursor in detail?
(in response to Phil Grainger)
And, of course, this "smart sort" must happen during OPEN CURSOR, otherwise
the first FETCH won't know which row to return



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of M. Khalid Khan
Sent: 04 December 2009 16:34
To: [login to unmask email]
Subject: Re: [DB2-L] what happens in opening cursor in detail?




That's true but there is further twist in version 9 - it may use "smart
sort" where it goes through the million qualifying rows exactly once picking
up the first 10 per the order by specification. No intermediate runs or
merges will be needed for such sort. I won't call it materialization
although some sort of sort (!) is involved.

Khalid




I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result set
materialization. If I want the first 10 rows of a result set that is ordered
by a non-indexed column (or set of columns), and if the target table has 1
million qualifying rows, DB2 is going to have to sort those 1 million rows
before it can do the FETCH FIRST 10 ROWS ONLY.
<snip>



HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at (312)
653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in
Oklahoma; or (972)766-6900 in Texas.



_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Martin Hubel

Re: what happens in opening cursor in detail?
(in response to Phil Grainger)
In DB2 LUW, explain shows both the full cost and first row cost. If full materialization of the the answer is required, the first row cost will be the same or to the full cost.

In situations where the rows are pulled from the table or via an index, the first row cost can be much lower. The same is true in z/OS.

--Martin


>> And, of course, this smart sort must happen during OPEN CURSOR, otherwise
>> the first FETCH wont know which row to return



>> Phil Grainger

>> Grainger Database Solutions Ltd

>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of M. Khalid Khan
>> Sent: 04 December 2009 16:34
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] what happens in opening cursor in detail?




>> That's true but there is further twist in version 9 - it may use "smart
>> sort" where it goes through the million qualifying rows exactly once
>> picking up the first 10 per the order by specification. No intermediate
>> runs or merges will be needed for such
>> sort. I won't call it materialization although some sort of sort (!) is
>> involved.

>> Khalid




>> I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result
>> set materialization. If I want the first 10 rows of a result set that is
>> ordered by a non-indexed column (or set of columns), and if the target
>> table has 1 million qualifying rows,
>> DB2 is going to have to sort those 1 million rows before it can do the
>> FETCH FIRST 10 ROWS ONLY.
>> <snip> HCSC Company Disclaimer The information contained in this
>> communication is confidential, private,proprietary, or otherwise
>> privileged and is intended only for the use ofthe addressee.
>> Unauthorized use, disclosure, distribution or copying is
>> strictly prohibited and may be unlawful. If you have received
>> thiscommunication in error, please notify the sender immediately at
>> (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500
>> inOklahoma; or (972)766-6900 in Texas.





>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here.




====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Peter Vanroose

Re: what happens in opening cursor in detail?
(in response to Martin Hubel)
> In DB2 LUW, explain shows both the full cost and first row cost.

Anybody knows whether this information is also available on z/OS through one
of the new explain tables?

-- Peter.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L