DB2 and PAV

[Terry Draper]

I have a situation where we are writing to a Shark DASD from DB2.
�
I know DB2 will start multiple write engines to write the data from the buffer pool. However all the writes will go to the same volume.
�
My questions are about PAV.
�
1. The DASD controller will serialise writes to the same extent - as specified in the define extent CCW. What does DB2 specify for the extent?
Is the the DASD extent or just the area it is writing to? If so what area is specified?
I cannot find this documented anywhere.
The many write engines will be trying to build the single table.
�
2. We may have many write engines started to the same DB2 table on a single volume.
There may be many of these.What is the maximum number of PAVs that WLM will give it?

The reason for the question is to evaluate the benefit (if any) of running multiple JOBs which will all contribute to the data Db2 will write to the single tablespace (i.e. dataset).

�
Terry Draper
zSeries Performance Consultant

----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to list...@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html

[Bill Fairchild]

1. DB2 tries very hard to minimize the size of the extent defined in the Define Extent CCW for each I/O that it does to a Shark-type DASD that supports Multiple Allegiance (MA). E.g., if an I/O references only one track, then that channel program will have only that one track within the defined extent. To verify, run GTF and trace I/Os to that one volume from DB2, then look at the defined extents that will appear in the Prefix CCW for SSCH trace records and in the Define Extent for I/O interrupt trace records.

2. I don't know for sure, but I imagine that WLM will allow the device to have as many PAVs as its controller microcode support. E.g, for the 2105s available in 2000 when I last looked at this issue, the maximum number of simultaneous I/Os allowed by the microcode to any one device was eight regardless of how many PAVs were assigned. Eight was the maximum MA level. The number of PAVs can be less than, equal to, or greater than the max MA number, but any more than the max will result in queued I/O requests if the workload produces simultaneous I/Os fast enough.

Bill Fairchild

Software Developer
Rocket Software
275 Grove Street � Newton, MA 02466-2272 � USA
Tel: +1.617.614.4503 � Mobile: +1.508.341.1715
Email: bi...@mainstar.com
Web: www.rocketsoftware.com

-----Original Message-----
From: IBM Mainframe Discussion List [mailto:IBM-...@bama.ua.edu] On Behalf Of Terry Draper
Sent: Tuesday, November 24, 2009 2:32 AM
To: IBM-...@bama.ua.edu
Subject: DB2 and PAV

1. The DASD controller will serialise writes to the same extent - as specified in the define extent CCW. What does DB2 specify for the extent?
Is the the DASD extent or just the area it is writing to? If so what area is specified?
I cannot find this documented anywhere.
The many write engines will be trying to build the single table.
�
2. We may have many write engines started to the same DB2 table on a single volume.
There may be many of these.What is the maximum number of PAVs that WLM will give it?

Terry Draper

[John Baxter]

Bill is correct in that DB2 will minimize the extent sizes for write operations, therefore increasing the likelihood of multiple, parallel operations to the same device. But also remember that DB2 will try to maximize the effectiveness of write I/O's, endeavoring to externalize as many pages as possible in one I/O operation (which may contain many chained CCW's).

Another point to remember is that if the aliases are managed by WLM, a given DASD volume may become "starved", and IOSQ builds up because of the long WLM process cycle. There are threshold controls for the BP's, and generally one tries to set the pageset level thresholds fairly low to discourage write bursts and maintain a steady trickle of writes. Obviously it would be ideal to strike a balance between write efficacy and keeping the write I/O's from overly bunching-up.

Another strong recommendation is to implement HyperPAV aliasing, which we found to almost totally mitigate the concerns mentioned above. These aliases are assigned to I/O operations as needed and released for reuse (within the same LCU or associated CSS, if configured) after the extent-level I/O has completed. The maximum number of aliases assigned to any one base address can be very high (and vendor-dependent, I believe).

This area of DB2 performance management is challenging but extremely interesting and needs to pull together expertise from the DBAs, sysprogs and DASD specialists.

John Baxter

Bill Fairchild

The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

[Terry Draper]

Bill and John,
�� Thanks for your reply.
�
��� You said that eight was the maximum for MA. Did you mean Multiple Allegiance, i.e. accesses from multiple systems. Does this also apply when all I/Os are form one system?
�
�� I understand how things work, its just the undocumented bits that are difficult. It was easier to get these when I was in IBM doing this job.

Terry Draper
zSeries Performance Consultant

w....@btopenworld.com
mobile:� +966 556730876

--- On Tue, 24/11/09, John Baxter <John....@ATCOITEK.COM> wrote:

[Scott Rowe]

I think that was just the limit for the Shark (2105), it varies by
subsystem architecture.

>>> Terry Draper <w....@BTOPENWORLD.COM> 11/24/2009 11:50 AM >>>

John Baxter

Bill Fairchild

CONFIDENTIALITY/EMAIL NOTICE: The material in this transmission contains
confidential and privileged information intended only for the addressee.
If you are not the intended recipient, please be advised that you have
received this material in error and that any forwarding, copying,
printing, distribution, use or disclosure of the material is strictly
prohibited. If you have received this material in error, please (i) do
not read it, (ii) reply to the sender that you received the message in
error, and (iii) erase or destroy the material. Emails are not secure
and can be intercepted, amended, lost or destroyed, or contain viruses.
You are deemed to have accepted these risks if you communicate with us
by email. Thank you.

[Bill Fairchild]

No. I said that eight was the maximum MA capabilitiy in the year 2000. It may be larger now. Ask your vendor.

Yes, I meant Multiple Allegiance. It also applies when all I/Os are from one system. Or if they come from eight different systems. The microcode checking for extent collisions involving a write does not care from where the I/O comes.

And DB2 also minimizes the size of the defined extent for read-only I/Os. If you minimize only the extents on write I/Os, you can still have plenty of I/O collisions that will cause extra-long service time. If you minimize the extent on ALL I/Os to a device, then you minimize the probability of an extent collision.

[Ted MacNEIL]

>This area of DB2 performance management is challenging but extremely interesting and needs to pull together expertise from the DBAs, sysprogs and DASD specialists.

Too many people get hung up on the technical aspects.
Rather than worrying about:

How many write engines?
How is the Defined Extent used/sized?
How many (HIPER)PAVs are allocated/usable?
VSAM chaining?
ETC?

The real issue is:
Are my Service Levels being met?
AND:
Is my resource consumption appropriate?

If the answer to the above two questions is YES -- relax.

If NO, then use your understanding of the technology to fix it.

-
Too busy driving to stop for gas!

[John Baxter]

Fortunately (?), our developers ensure that we (the three groups
mentioned) are always kept on our toes, despite efforts at education,
etc.

-----Original Message-----
From: IBM Mainframe Discussion List [mailto:IBM-...@bama.ua.edu] On
Behalf Of Ted MacNEIL
Sent: Tuesday, November 24, 2009 12:14 PM
To: IBM-...@bama.ua.edu
Subject: Re: DB2 and PAV

The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

----------------------------------------------------------------------

[Terry Draper]

Ted,
�� I agree with you about SLA's etc.
�
�� But this is an investigation about how we partition a huge tablespace that is reaching the size limit.
�� The tablespace will be unavailable to the users during the copying�and we are trying to find the best copy processing to reduce the outage. The current suggested process writes to each partition and fills it before going to the next. Hence the questions about concurrent write I/O.
�
�� Sometimes you do need to get down into the details.

�

Terry Draper
zSeries Performance Consultant
w....@btopenworld.com
mobile:� +966 556730876

--- On Tue, 24/11/09, Ted MacNEIL <eama...@YAHOO.CA> wrote:

From: Ted MacNEIL <eama...@YAHOO.CA>
Subject: Re: DB2 and PAV