Ron
I am not sure what you have tried -- or how the actual DASD is
configured. I assume it is some large disk array since you are
involved. One more suggestion that might improve IOSQ is to stripe the
VSAM dataset defined in DSNDB07. This would help if you ended up with a
better spread across disk on the back-end array. You can get the VSAM
striping with just an SMS change and delete/define of the data sets.
Tom Moulder
Ron Hawkins wrote:
Martin,
OK, you are referring to DSNDB07 as SORTWK. I never seen this before, and I
think it's confusing. You may want to rethink this description as in DB2 V9
DSNDB07 is not just the DB2 SQL sort area, tables from DSNDB04 are now in
DSNDB07 table spaces.
Besides increasing the size of the Buffer Pool you would have to use the
thresholds to disable pre-fetch. Given the nature of how these tables are
used you would need to buffer the whole of DSNDB07 at its maximum, otherwise
sequential IO without pre-fetch would just walk the buffer pool and cause
some pretty inefficient IO and low buffer hit rates. The IOSQ Time would go
down, but there would be some pretty horrendous batch and transaction
elongation.
I don't have the SMF data in front of me, but in this case we are talking
around 20x3390-9 with 4K and 32K DSNDB07 in segmented areas across all these
volumes and around 10 areas on each volume. IO was spread fairly evenly over
the volumes. The 32K areas were far more active than the 4K, and were around
half the space on each volume. A back of the envelope estimate says you
would need to buffer half of the 20 volumes in order to get this locked in a
dedicated Buffer Pool, which is about 40GB.
I don't think the scale of buffering, along with the performance hit from
disabling Sequential pre-fetch makes this the best way to reduce that IOSQ
time.
Ron
-----Original Message-----
From: IBM Mainframe Discussion List [mailto:ibm-m...@bama.ua.edu] On
Behalf Of
Martin Packer
Sent: Saturday, September 05, 2009 3:36 AM
To: IBM-MAIN@bama.ua.edu
Subject: Re: [IBM-MAIN] EMC DASD and Hyper-PAVs
Ron, just to fill in some DB2 background (as a z/OS guy who's done a fair
amount of "interloping" in DB2). :-)
DB2 Sort Work data sets, whether 32KB or some other size, are buffered by
standard DB2 buffer pools.
(Often these buffer pools are dedicated to sort work data sets for
management and reporting purposes. Indeed in my reporting code on the
matter - fed by the DB2 Catalog - I could tell if this was the case.)
It's entirely feasible to resize these buffer pools (using the normal DB2
buffer pool mechanisms) and to play with their sequential and update
thresholds. If you make them big enough you might be able to reduce the
amount of DB2 sort I/O. Not that that would NECESSARILY change the profile
of the I/Os (which is I guess your gist).
Hoping that helps a little.
Cheers, Martin
Martin Packer
Performance Consultant
IBM United Kingdom Ltd
+44-20-8832-5167
+44-7802-245-584
email: martin_pac...@uk.ibm.com
Twitter ID: MartinPacker
"They're figuring out that collaboration isn't a productivity hit, it
makes them smarter." Sam Palmisano on BlogCentral, 26 November 2008
Unless stated otherwise above:
IBM United Kingdom Limited - Registered in England and Wales with number
741598.
Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
