[z/os] extents as a health check

Avram Friedman

[z/os] extents as a health check
There are a few active threads on DB2-L that have the objective of mesuring number of extents in a dataset as a DB2 health check.

Number of extents has not been a big concern since the 1980s or before.
It has never been a concern for pre allocated direct access and there are studies from the early 80's (before DB2) that show to praticial impact for sequencial.

(Historical note "Pre allocated" because BDAM will not extend after original formatting)

Many years ago it was thought that dataset extents impacted performace in a negative way.
Not only is this not true today but the reverse is often true. For random access objects extents can improve performace for a busy dataset because they allow for dynamic UCBs to be allocated.

It is only on very old DASD and sequencial access that extents may be a problem and this problem is related to addtional rotational delay and seek time at the end and start of each extent. Studies show this problem is theoritical only.

All supported versions of DB2 have options to increase the number of extents. One of the reasons this improvement was added was to insure dataspaces could reach the max. allowable size. Max. allowable size suggest the most time before a dataset full condition. It is only at the max. allowable size that DB2 will allocate an additonal data set to dynamicly partition a not partioned object.

From a performace standpoint there are 2 conditions where one should be concerned with dataset growth.
1. Reaching the max. allowable size for a partioned object or index (with out peice size) means the dataset can actually fill.
2. Growth implies disorganization, if the primary key is not ever increacing growth implies keys are no nonger clustered,

Problems like running out of space on a volume are now configuration issues (data sets should now be allowed to me multi volume usually via SMS or equivlent rules ... SMS is required as of DB2 V10). There is a diffrence, in terms of urgency, between capacity planning and performance tuning.

The performace problem is growth, not extents.
Growth is a comparative mesurement (requires a before and after values) , extents is a static one (current values only).
Growth can occur without extents if there is lots of free space at the end of the dataset.

I do have mesurement recomendations.

For data areas that do not have ever increasing keys
A high water growth of 10%
High water growth has a 1 to 1 correspondece with decline in random access performace.
So a 10 percent high water growth is a 10% decline in performance.
Very high growth has a much worse impact on performace
The de-clustering concern increases
at low growth its decluster between clustered original and declustered growth or
1 addional I/O for the percent of clusters = percent of growth

at high growth its decluster between non clustered growth and non clustered growth


For partitioned objects (this is a static mesurment)
200+ extents means the data set limit is being pushed and may run out of space
Recall for non partioned objects this is not true, DB2 will simply add an additional dataset.

For sort and temp space
Generally extents should not be allowed at all for these datasets
It's a control over runaway work, not an I/O performace concern.


For indexes,
Any increase in number of index levels
As indexes are b-trees they are performace self correcting until index levels increase.
Index level increases are BIG hits, going from 3 to 4 levels is a 33% performace hit.

Best wishes and happy computing
Avram Friedman

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information

Max Scarpa

Re: [z/os] extents as a health check
(in response to Avram Friedman)
Sorry if I jump here in late. Apart REXX, you can use NaviQuest (an SMS
interface) to obtain details on datasets. It's simple to use and the bugs
should have been corrected since some years.

It was since many years all experts pointed out there were very few
problems dealing with extents and modern DASD (see John Iczkovits' or
Steve Thomas' papers for example). I saw from monitors
DB2 datasets with 100+ extents having really good resp. time (cache resp.
time, very few msec if not zero). Expecially when internal arrays
configuration was designed with DASD manufacturer and DB2 sysprogs/DBAs
and z/OS DASD Managers but it's not common, AFAIK. The main dasd problems
we faced were, as you said, growth and the 'spikes' in some tablespaces
during given periods of heavy workload. If we detected long response times
it was in 90%+ of cases due to a bad SQL .

You'd send this email to my ex-boss in Trento and still thinking in VTOC
terms, who left CICS velocity goal to 90 (the highest) in WLM policy even
if it was never (by far) achieved.

Max Scarpa

Chuck Norris' DB2 law: 'Everyone can become DB2 Gold Consultant, only IDUG
attendees can become DB2 black belt 10 Dan'





From: Avram Friedman <[login to unmask email]>
To: [login to unmask email]
Date: 04/05/2011 06.45
Subject: [DB2-L] [z/os] extents as a health check
Sent by: IDUG DB2-L <[login to unmask email]>



There are a few active threads on DB2-L that have the objective of
mesuring number of extents in a dataset as a DB2 health check.

Number of extents has not been a big concern since the 1980s or before.
It has never been a concern for pre allocated direct access and there are
studies from the early 80's (before DB2) that show to praticial impact for
sequencial.

(Historical note "Pre allocated" because BDAM will not extend after
original formatting)

Many years ago it was thought that dataset extents impacted performace in
a negative way.
Not only is this not true today but the reverse is often true. For random
access objects extents can improve performace for a busy dataset because
they allow for dynamic UCBs to be allocated.

It is only on very old DASD and sequencial access that extents may be a
problem and this problem is related to addtional rotational delay and seek
time at the end and start of each extent. Studies show this problem is
theoritical only.

All supported versions of DB2 have options to increase the number of
extents. One of the reasons this improvement was added was to insure
dataspaces could reach the max. allowable size. Max. allowable size
suggest the most time before a dataset full condition. It is only at the
max. allowable size that DB2 will allocate an additonal data set to
dynamicly partition a not partioned object.

From a performace standpoint there are 2 conditions where one should be
concerned with dataset growth.
1. Reaching the max. allowable size for a partioned object or index (with
out peice size) means the dataset can actually fill.
2. Growth implies disorganization, if the primary key is not ever
increacing growth implies keys are no nonger clustered,

Problems like running out of space on a volume are now configuration
issues (data sets should now be allowed to me multi volume usually via SMS
or equivlent rules ... SMS is required as of DB2 V10). There is a
diffrence, in terms of urgency, between capacity planning and performance
tuning.

The performace problem is growth, not extents.
Growth is a comparative mesurement (requires a before and after values) ,
extents is a static one (current values only).
Growth can occur without extents if there is lots of free space at the end
of the dataset.

I do have mesurement recomendations.

For data areas that do not have ever increasing keys
A high water growth of 10%
High water growth has a 1 to 1 correspondece with decline in random
access performace.
So a 10 percent high water growth is a 10% decline in performance.
Very high growth has a much worse impact on performace
The de-clustering concern increases
at low growth its decluster between clustered original and
declustered growth or
1 addional I/O for the percent of clusters = percent of growth

at high growth its decluster between non clustered growth and
non clustered growth


For partitioned objects (this is a static mesurment)
200+ extents means the data set limit is being pushed and may run out
of space
Recall for non partioned objects this is not true, DB2 will simply
add an additional dataset.

For sort and temp space
Generally extents should not be allowed at all for these datasets
It's a control over runaway work, not an I/O performace concern.


For indexes,
Any increase in number of index levels
As indexes are b-trees they are performace self correcting until
index levels increase.
Index level increases are BIG hits, going from 3 to 4 levels is a 33%
performace hit.

Best wishes and happy computing
Avram Friedman

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User
Run
Your only source for independent, unbiased, and trusted DB2 information


_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information


Ted MacNEIL

Re: [z/os] extents as a health check
(in response to Max Scarpa)
>200+ extents means the data set limit is being pushed and may run out of space

Even that has changed as of z/OS 1.7.
-
Ted MacNEIL
[login to unmask email]
Twitter: @TedMacNEIL

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information