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:[email protected]] On Behalf Of > Martin Packer > Sent: Saturday, September 05, 2009 3:36 AM > To: [email protected] > 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: [email protected] > > 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 [email protected] 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 [email protected] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
