DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace

Philip Sevetson

DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace
May I have a couple of expert opinions, please, with sources if possible? The question is: "How big is a Sequential Prefetch, when the tablespace is segmented?"

We have tablespaces that we are recreating. I'm leaning toward using SEGSIZE 16 to minimize waste in smaller tables. However, our bufferpools are several thousand pages. Normally, the prefetch size for a large enough bufferpool is 32 pages.

If the tablespace SEGSIZE is 16, does that mean that Prefetch will limit itself to 16 pages? Or will it get 32 pages?

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Daniel Luksetich

Re: DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace
(in response to Philip Sevetson)
Phil,
Assuming you are using 4K page size...

The first issue is why have a standard segsize? Give your small table
spaces a segsize of 4 and big guys 64.

Now, if you want a standard to save space for the small ones then 16 is
not practical. I believe that the minimum dataset size is a track, which is
12 pages. DB2 will format an entire segment and you should get two tracks
used. No savings there.

Plus, aren't we talking about saving just a few tracks in this case. If
you need to set a system wide standard then I would go 64 over 4 and waste
a few tracks.

make sense?

Finally, the performance guide gives a pretty detailed explanation of the
prefetch quantity, which depends on several factors. None of which is
segsize.

Cheers,
Dan

On Tue, 7 Dec 2010 11:48:32 -0500, "Sevetson, Phil"
<[login to unmask email]> wrote:
> May I have a couple of expert opinions, please, with sources if
possible?
> The question is: "How big is a Sequential Prefetch, when the tablespace
is
> segmented?"
>
> We have tablespaces that we are recreating. I'm leaning toward using
> SEGSIZE 16 to minimize waste in smaller tables. However, our
bufferpools
> are several thousand pages. Normally, the prefetch size for a large
enough
> bufferpool is 32 pages.
>
> If the tablespace SEGSIZE is 16, does that mean that Prefetch will limit
> itself to 16 pages? Or will it get 32 pages?
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Philip Sevetson

Re: DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace
(in response to Daniel Luksetich)
Dan,
Thanks -- I'm specifically talking about the case of a multi-table tablespace. My one-table segmented tablespaces are sized to a minimum of 720K/1 Cyl and given a SEGSIZE of 64, for the reasons you have espoused.

I'll take a look at the performance guide again; thanks for the pointer.

--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
Sent: Tuesday, December 07, 2010 12:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace

Phil,
Assuming you are using 4K page size...

The first issue is why have a standard segsize? Give your small table
spaces a segsize of 4 and big guys 64.

Now, if you want a standard to save space for the small ones then 16 is
not practical. I believe that the minimum dataset size is a track, which is
12 pages. DB2 will format an entire segment and you should get two tracks
used. No savings there.

Plus, aren't we talking about saving just a few tracks in this case. If
you need to set a system wide standard then I would go 64 over 4 and waste
a few tracks.

make sense?

Finally, the performance guide gives a pretty detailed explanation of the
prefetch quantity, which depends on several factors. None of which is
segsize.

Cheers,
Dan

On Tue, 7 Dec 2010 11:48:32 -0500, "Sevetson, Phil"
<[login to unmask email]> wrote:
> May I have a couple of expert opinions, please, with sources if
possible?
> The question is: "How big is a Sequential Prefetch, when the tablespace
is
> segmented?"
>
> We have tablespaces that we are recreating. I'm leaning toward using
> SEGSIZE 16 to minimize waste in smaller tables. However, our
bufferpools
> are several thousand pages. Normally, the prefetch size for a large
enough
> bufferpool is 32 pages.
>
> If the tablespace SEGSIZE is 16, does that mean that Prefetch will limit
> itself to 16 pages? Or will it get 32 pages?
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Max Scarpa

Re: DB2 v9 for z/OS -- Sequential Prefetch, Segmented Tablespace
(in response to Philip Sevetson)
AFAIK prefetch quantity (I suppose you're not talking about dynamic
prefetch, there's a change in DB2 10) is not influence by SEGSIZE,
prefetch is a 'pack' of pages coming from different segments

belonging to the same table (for instance for SEGSIZE 16 it'll be a
2-segments 'pack'). See 'DB2 Version 9.1 for z/OS Performance Monitoring
and Tuning Guide' pag 561 for some infos.

BTW in V9 redbook authors refers to prefetch quantity in Mb (160 MB, 360
Mb) and when the product VPSIZE*VPSEQT has some given values.

HTH

Max Scarpa

Frozen DB2 sysprog
Certified ice cube





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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