DB2 zOS - Sequential Detection

Marc van der Valk

DB2 zOS - Sequential Detection
Hello listserv-ers,

I have a question about Sequential Detection:

on which level does DB2 for z/OS track (near)sequential access to pages, on
tablespace level or on partition level? Couldn't find a clear answer in the
manuals...

Simple example:

partitioned table, partitioning by month, within partition clustering on
customer number, NPI on customer number.

Query gets data for customer 1 to 1000 for all months and data access is
via NPI. Is dynamic prefetching possible or doesn't DB2 detect sequential
access because every next page is in a different partition?

Thanks in advance,

Marc


------------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van vertrouwelijke informatie met de burger of voor de bekendmaking van beslissingen. De Belastingdienst hanteert conventies voor het gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
=====

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: DB2 zOS - Sequential Detection
(in response to Marc van der Valk)
Hi Marc

Better to think of sequential detection as "checking whether pages read are in (near) PHYSICAL sequence" - so your example, where pages will be read from DIFFERENT partitions means that they are definitely NOT "
...in (near) physical sequence" so sequential detection will not be enabled

You might get sequential detection in your NPI though

Phil Grainger
CA


________________________________

From: DB2 Data Base Discussion List on behalf of Marc vd Valk
Sent: Thu 27/11/2008 07:31
To: [login to unmask email]
Subject: [DB2-L] DB2 zOS - Sequential Detection



Hello listserv-ers,

I have a question about Sequential Detection:

on which level does DB2 for z/OS track (near)sequential access to pages, on
tablespace level or on partition level? Couldn't find a clear answer in the
manuals...

Simple example:

partitioned table, partitioning by month, within partition clustering on
customer number, NPI on customer number.

Query gets data for customer 1 to 1000 for all months and data access is
via NPI. Is dynamic prefetching possible or doesn't DB2 detect sequential
access because every next page is in a different partition?

Thanks in advance,

Marc


------------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van vertrouwelijke informatie met de burger of voor de bekendmaking van beslissingen. De Belastingdienst hanteert conventies voor het gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
======

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: DB2 zOS - Sequential Detection
(in response to Phil Grainger)
Hi Mark,

Long time no see...

In DB2 V9 sequential detection is both for tablespaces and indexspaces.
When 4 out of the last 8 pages are in sequence than DB2 uses Dynamic
Prefetch. If not DB2 uses a sync io to get the page. So, it is a very
efficient way off getting pages into the bufferpool.
There is no difference between partition or tablespace level, just
pages.

Dynamic Prefetch is not shown in the PLAN_TABLE as it is detection at
execution time.
If an index access shows a S for prefetch, DB2 might use Dynamic
Prefetch instead.

And if accessing a index that has a low clusterratio, DB2 can use List
Prefetch to efficiently get the datapages.
Hope this helps.

CU.

Vriendelijke groet,
Douwe van Sluis



-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Marc vd Valk
Verzonden: donderdag 27 november 2008 8:31
Aan: [login to unmask email]
Onderwerp: [DB2-L] DB2 zOS - Sequential Detection


Hello listserv-ers,

I have a question about Sequential Detection:

on which level does DB2 for z/OS track (near)sequential access to pages,
on tablespace level or on partition level? Couldn't find a clear answer
in the manuals...

Simple example:

partitioned table, partitioning by month, within partition clustering on
customer number, NPI on customer number.

Query gets data for customer 1 to 1000 for all months and data access is
via NPI. Is dynamic prefetching possible or doesn't DB2 detect
sequential access because every next page is in a different partition?

Thanks in advance,

Marc


------------------------------------------------------------------------
------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking van
beslissingen. De Belastingdienst hanteert conventies voor het gebruik
van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
=======================
======

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Isaac Yassin

Re: DB2 zOS - Sequential Detection
(in response to Douwe van Sluis)
Hi,

5 out of 8 last pages, per "file", in near sequence (16 pages backward/forward) are needed to kick-in dynamic prefetch.
In V9 you should see it in the plan_table as well.

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Douwe van Sluis
Sent: Thursday, November 27, 2008 10:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 zOS - Sequential Detection

Hi Mark,

Long time no see...

In DB2 V9 sequential detection is both for tablespaces and indexspaces.
When 4 out of the last 8 pages are in sequence than DB2 uses Dynamic
Prefetch. If not DB2 uses a sync io to get the page. So, it is a very
efficient way off getting pages into the bufferpool.
There is no difference between partition or tablespace level, just
pages.

Dynamic Prefetch is not shown in the PLAN_TABLE as it is detection at
execution time.
If an index access shows a S for prefetch, DB2 might use Dynamic
Prefetch instead.

And if accessing a index that has a low clusterratio, DB2 can use List
Prefetch to efficiently get the datapages.
Hope this helps.

CU.

Vriendelijke groet,
Douwe van Sluis



-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Marc vd Valk
Verzonden: donderdag 27 november 2008 8:31
Aan: [login to unmask email]
Onderwerp: [DB2-L] DB2 zOS - Sequential Detection


Hello listserv-ers,

I have a question about Sequential Detection:

on which level does DB2 for z/OS track (near)sequential access to pages,
on tablespace level or on partition level? Couldn't find a clear answer
in the manuals...

Simple example:

partitioned table, partitioning by month, within partition clustering on
customer number, NPI on customer number.

Query gets data for customer 1 to 1000 for all months and data access is
via NPI. Is dynamic prefetching possible or doesn't DB2 detect
sequential access because every next page is in a different partition?

Thanks in advance,

Marc


------------------------------------------------------------------------
------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking van
beslissingen. De Belastingdienst hanteert conventies voor het gebruik
van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
=======================
======

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

--
I am using the free version of SPAMfighter.
We are a community of 5.7 million users fighting spam.
SPAMfighter has removed 4029 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

The Professional version does not have this message

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: DB2 zOS - Sequential Detection
(in response to Isaac Yassin)
Hi Isaac,

Spot on. I should have typed "more than 4". And yesterday 5 was more
than 4 ;-))
Thanks for the "minor" adjustment.

Vriendelijke groet,
Douwe van Sluis




-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Isaac Yassin
Verzonden: vrijdag 28 november 2008 12:18
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] DB2 zOS - Sequential Detection


Hi,

5 out of 8 last pages, per "file", in near sequence (16 pages
backward/forward) are needed to kick-in dynamic prefetch.
In V9 you should see it in the plan_table as well.

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Douwe van Sluis
Sent: Thursday, November 27, 2008 10:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 zOS - Sequential Detection

Hi Mark,

Long time no see...

In DB2 V9 sequential detection is both for tablespaces and indexspaces.
When 4 out of the last 8 pages are in sequence than DB2 uses Dynamic
Prefetch. If not DB2 uses a sync io to get the page. So, it is a very
efficient way off getting pages into the bufferpool. There is no
difference between partition or tablespace level, just pages.

Dynamic Prefetch is not shown in the PLAN_TABLE as it is detection at
execution time. If an index access shows a S for prefetch, DB2 might use
Dynamic Prefetch instead.

And if accessing a index that has a low clusterratio, DB2 can use List
Prefetch to efficiently get the datapages. Hope this helps.

CU.

Vriendelijke groet,
Douwe van Sluis



-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Marc vd Valk
Verzonden: donderdag 27 november 2008 8:31
Aan: [login to unmask email]
Onderwerp: [DB2-L] DB2 zOS - Sequential Detection


Hello listserv-ers,

I have a question about Sequential Detection:

on which level does DB2 for z/OS track (near)sequential access to pages,
on tablespace level or on partition level? Couldn't find a clear answer
in the manuals...

Simple example:

partitioned table, partitioning by month, within partition clustering on
customer number, NPI on customer number.

Query gets data for customer 1 to 1000 for all months and data access is
via NPI. Is dynamic prefetching possible or doesn't DB2 detect
sequential access because every next page is in a different partition?

Thanks in advance,

Marc


------------------------------------------------------------------------
------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking van
beslissingen. De Belastingdienst hanteert conventies voor het gebruik
van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
=======================
======

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more. If
you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

--
I am using the free version of SPAMfighter.
We are a community of 5.7 million users fighting spam. SPAMfighter has
removed 4029 of my spam emails to date. Get the free SPAMfighter here:
http://www.spamfighter.com/len

The Professional version does not have this message

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms