Service Units for a Dynamic Bind

Jay Reavill

Service Units for a Dynamic Bind
Anyone have a ROT on the amount of service units it takes to perform a
dynamic bind? I'm sure it fluctuates depending on the complexity of the
SQL, but we're trying to make sure that we have enough juice being
allotted to our period 1 WLM service classes for the distributed work
coming in so that they don't get "stuck" due to CPU constraints and end
up holding locks on system objects while in the middle of a dynamic
bind. We also don't want to thru too much juice their way because we
don't want them to dominate unnecessarily. We're just trying to avoid
system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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



_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Adam Baldwin

Re: Service Units for a Dynamic Bind
(in response to Jay Reavill)
Jay, I can't give you a ROT but this may be of interest to you:

http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/FLASH10609

I imagine that you're already aware of this but, if not, it could be of use to you.

Cheers, Adam

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Walter Jani&#223;en

AW: Service Units for a Dynamic Bind
(in response to Adam Baldwin)
Ray

I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?


regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I'm sure it fluctuates depending on the complexity of the SQL, but we're trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don't get "stuck" due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don't want to thru too much juice their way because we don't want them to dominate unnecessarily. We're just trying to avoid system level contention.

As always, Thanks!
Jay

------------------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd., St. Petersburg, FL. 33716
Office: 727-227-2144 | Cell: 727-215-5794
[login to unmask email]<mailto:[login to unmask email]>
------------------------------------------------------------------------

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jay Reavill

Re: AW: Service Units for a Dynamic Bind
(in response to Walter Janißen)
Hi Walter,



Assuming you meant me (Jay)...



No, I wasn't saying that it is performed by the DDF address space, but that is a good question. Is it? I was under the impression that it was being performed by the SQL's distributed thread coming in and thereby under the priority of its service class. If that's not the case then we need to take a different look at it. We're just trying to make sure that the DDF threads coming in have enough juice to get thru the dynamic bind (if needed) without getting stalled. Some of the work is report type stuff which we want to have a low priority, but need it to get throw any bind activity quickly. It's a third party product so we don't have much, if any, control over the SQL coming in.



Thanks,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind



Ray



I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?



regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email] <mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996








________________________________


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I'm sure it fluctuates depending on the complexity of the SQL, but we're trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don't get "stuck" due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don't want to thru too much juice their way because we don't want them to dominate unnecessarily. We're just trying to avoid system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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



_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________




________________________________


Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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: AW: Service Units for a Dynamic Bind
(in response to Jay Reavill)
This is a question for IBM so you should really open an ETR. If you can’t do
that, and if memory serves me correctly (it has been several years since I
investigated this), I believe dynamic binds get a higher priority. I could
be wrong so double check with IBM or assume that your priority settings will
not affect any prepare/bind.



Cheers,

Dan



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Reavill, Jay
Sent: Friday, December 03, 2010 7:50 AM
To: [login to unmask email]
Subject: [SPAM] Re: AW: Service Units for a Dynamic Bind



Hi Walter,



Assuming you meant me (Jay)…



No, I wasn’t saying that it is performed by the DDF address space, but that
is a good question. Is it? I was under the impression that it was being
performed by the SQL’s distributed thread coming in and thereby under the
priority of its service class. If that’s not the case then we need to take
a different look at it. We’re just trying to make sure that the DDF threads
coming in have enough juice to get thru the dynamic bind (if needed) without
getting stalled. Some of the work is report type stuff which we want to
have a low priority, but need it to get throw any bind activity quickly.
It’s a third party product so we don’t have much, if any, control over the
SQL coming in.



Thanks,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind



Ray



I am not sure, if I understand, what you were telling. Do you mean, that the
dynamic bind is performed by the DDF-address-space?



regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996








_____


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a
dynamic bind? I’m sure it fluctuates depending on the complexity of the
SQL, but we’re trying to make sure that we have enough juice being allotted
to our period 1 WLM service classes for the distributed work coming in so
that they don’t get “stuck” due to CPU constraints and end up holding locks
on system objects while in the middle of a dynamic bind. We also don’t want
to thru too much juice their way because we don’t want them to dominate
unnecessarily. We’re just trying to avoid system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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



_____________

The information contained in this message is proprietary and/or
confidential. If you are not the intended recipient, please: (i) delete the
message and all copies; (ii) do not disclose, distribute or use the message
in any manner; and (iii) notify the sender immediately. In addition, please
be aware that any message addressed to our domain is subject to archiving
and review by persons other than the intended recipient. Thank you.
_____________



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.

_____________

The information contained in this message is proprietary and/or
confidential. If you are not the intended recipient, please: (i) delete the
message and all copies; (ii) do not disclose, distribute or use the message
in any manner; and (iii) notify the sender immediately. In addition, please
be aware that any message addressed to our domain is subject to archiving
and review by persons other than the intended recipient. Thank you.
_____________



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3289 - Release Date: 12/01/10
01:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Mike Vaughan

Re: AW: Service Units for a Dynamic Bind
(in response to Daniel Luksetich)
Just for clarification, when you say "Dynamic Bind" do you really mean the PREPARE? If so, then you are correct that the complexity of the SQL greatly impacts the service units required to complete the PREPARE. Specifically what I have found in the past is the number of tables being joined has a huge impact. A few years ago I ran some traces to determine how the number of joins impacts PREPARE times and found that the cputime increased exponentially once you reached around 12 tables in the join. This test was a worst-case scenario of joining the same table to itself on the same column x-times, so the number of possible join permutations was huge. "Real world" SQL likely wouldn't see the same impact and I know there are also some zparms to tame this down a little (and I believe the default for those under DB2 9 eliminates, or at least greatly reduces the "exponential" impact I mentioned). Unfortunately trying to give a ROT for PREPARE time overhead is very difficult due to the wide variance in sql. What I have done in the past is just try to setup an isolated test run of the app in question with a trace running and pick out the PREPARE times. You also probably need to take dynamic caching into account here if you are looking at PREPARE overhead.
Regarding the WLM service classes, occasionally we do experience the exact impact you mention -- a process does not have enough "juice" to complete the PREPARE in the time allocated for period-1, and it has degraded before it can even start it's "real" work, and on very busy days they do occasionally get "stuck" for an extended period of time.

Thanks,
Mike.
________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Daniel Luksetich
Sent: Friday, December 03, 2010 8:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind

This is a question for IBM so you should really open an ETR. If you can't do that, and if memory serves me correctly (it has been several years since I investigated this), I believe dynamic binds get a higher priority. I could be wrong so double check with IBM or assume that your priority settings will not affect any prepare/bind.

Cheers,
Dan

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Reavill, Jay
Sent: Friday, December 03, 2010 7:50 AM
To: [login to unmask email]
Subject: [SPAM] Re: AW: Service Units for a Dynamic Bind

Hi Walter,

Assuming you meant me (Jay)...

No, I wasn't saying that it is performed by the DDF address space, but that is a good question. Is it? I was under the impression that it was being performed by the SQL's distributed thread coming in and thereby under the priority of its service class. If that's not the case then we need to take a different look at it. We're just trying to make sure that the DDF threads coming in have enough juice to get thru the dynamic bind (if needed) without getting stalled. Some of the work is report type stuff which we want to have a low priority, but need it to get throw any bind activity quickly. It's a third party product so we don't have much, if any, control over the SQL coming in.

Thanks,
Jay

------------------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd., St. Petersburg, FL. 33716
Office: 727-227-2144 | Cell: 727-215-5794
[login to unmask email]<mailto:[login to unmask email]>
------------------------------------------------------------------------
________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind

Ray

I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?


regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind
Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I'm sure it fluctuates depending on the complexity of the SQL, but we're trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don't get "stuck" due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don't want to thru too much juice their way because we don't want them to dominate unnecessarily. We're just trying to avoid system level contention.

As always, Thanks!
Jay

------------------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd., St. Petersburg, FL. 33716
Office: 727-227-2144 | Cell: 727-215-5794
[login to unmask email]<mailto:[login to unmask email]>
------------------------------------------------------------------------

</pre><br>-----Message Disclaimer-----<br><br>This e-mail message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended recipient, any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply email to [login to unmask email] and delete or destroy all copies of the original message and attachments thereto. Email sent to or from the Principal Financial Group or any of its member companies may be retained as required by law or regulation.<br><br> Nothing in this message is intended to constitute an Electronic signature for purposes of the Uniform Electronic Transactions Act (UETA) or the Electronic Signatures in Global and National Commerce Act ("E-Sign") unless a specific statement to the contrary is included in this message.<br><br>While this communication may be used to promote or market a transaction or an idea that is discussed in the publication, it is intended to provide general information about the subject matter covered and is provided with the understanding that The Principal is not rendering legal, accounting, or tax advice. It is not a marketed opinion and may not be used to avoid penalties under the Internal Revenue Code. You should consult with appropriate counsel or other advisors on all matters pertaining to legal, tax, or accounting obligations and requirements.<br><pre>

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jay Reavill

Re: AW: Service Units for a Dynamic Bind
(in response to Mike Vaughan)
Thanks Mike, you've hit exactly what I'm referring to. Sounds like we need to run some traces and go from there.



Thanks again,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Vaughan, Mike
Sent: Friday, December 03, 2010 10:02 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind



Just for clarification, when you say "Dynamic Bind" do you really mean the PREPARE? If so, then you are correct that the complexity of the SQL greatly impacts the service units required to complete the PREPARE. Specifically what I have found in the past is the number of tables being joined has a huge impact. A few years ago I ran some traces to determine how the number of joins impacts PREPARE times and found that the cputime increased exponentially once you reached around 12 tables in the join. This test was a worst-case scenario of joining the same table to itself on the same column x-times, so the number of possible join permutations was huge. "Real world" SQL likely wouldn't see the same impact and I know there are also some zparms to tame this down a little (and I believe the default for those under DB2 9 eliminates, or at least greatly reduces the "exponential" impact I mentioned). Unfortunately trying to give a ROT for PREPARE time overhead is very difficult due to the wide variance in sql. What I have done in the past is just try to setup an isolated test run of the app in question with a trace running and pick out the PREPARE times. You also probably need to take dynamic caching into account here if you are looking at PREPARE overhead.

Regarding the WLM service classes, occasionally we do experience the exact impact you mention -- a process does not have enough "juice" to complete the PREPARE in the time allocated for period-1, and it has degraded before it can even start it's "real" work, and on very busy days they do occasionally get "stuck" for an extended period of time.



Thanks,

Mike.

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Daniel Luksetich
Sent: Friday, December 03, 2010 8:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind

This is a question for IBM so you should really open an ETR. If you can't do that, and if memory serves me correctly (it has been several years since I investigated this), I believe dynamic binds get a higher priority. I could be wrong so double check with IBM or assume that your priority settings will not affect any prepare/bind.



Cheers,

Dan



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Reavill, Jay
Sent: Friday, December 03, 2010 7:50 AM
To: [login to unmask email]
Subject: [SPAM] Re: AW: Service Units for a Dynamic Bind



Hi Walter,



Assuming you meant me (Jay)...



No, I wasn't saying that it is performed by the DDF address space, but that is a good question. Is it? I was under the impression that it was being performed by the SQL's distributed thread coming in and thereby under the priority of its service class. If that's not the case then we need to take a different look at it. We're just trying to make sure that the DDF threads coming in have enough juice to get thru the dynamic bind (if needed) without getting stalled. Some of the work is report type stuff which we want to have a low priority, but need it to get throw any bind activity quickly. It's a third party product so we don't have much, if any, control over the SQL coming in.



Thanks,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind



Ray



I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?



regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email] <mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996








________________________________


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I'm sure it fluctuates depending on the complexity of the SQL, but we're trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don't get "stuck" due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don't want to thru too much juice their way because we don't want them to dominate unnecessarily. We're just trying to avoid system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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




-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended recipient, any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply email to [login to unmask email] and delete or destroy all copies of the original message and attachments thereto. Email sent to or from the Principal Financial Group or any of its member companies may be retained as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature for purposes of the Uniform Electronic Transactions Act (UETA) or the Electronic Signatures in Global and National Commerce Act ("E-Sign") unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction or an idea that is discussed in the publication, it is intended to provide general information about the subject matter covered and is provided with the understanding that The Principal is not rendering legal, accounting, or tax advice. It is not a marketed opinion and may not be used to avoid penalties under the Internal Revenue Code. You should consult with appropriate counsel or other advisors on all matters pertaining to legal, tax, or accounting obligations and requirements.



________________________________


Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Myron Miller

Re: AW: Service Units for a Dynamic Bind
(in response to Jay Reavill)
But Jay,
Understand that the SQL and dynamic BIND are both executed at the same
priority. You can't specify one priority for the bind and another for the sql.
It doesn't work that way.

What ever priority you have for the application, set by WLM for the DDF address
functions (and you can specify in WLM different priorities for different
authids) is what the bind runs at. So if an app has a very low priority, it's
dynamic bind will run at that priority and get whatever cycles everything for
that app gets.

Myron



________________________________
From: "Reavill, Jay" <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 3, 2010 8:50:29 AM
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind


Hi Walter,

Assuming you meant me (Jay)…

No, I wasn’t saying that it is performed by the DDF address space, but that is a
good question. Is it? I was under the impression that it was being performed
by the SQL’s distributed thread coming in and thereby under the priority of its
service class. If that’s not the case then we need to take a different look at
it. We’re just trying to make sure that the DDF threads coming in have enough
juice to get thru the dynamic bind (if needed) without getting stalled. Some of
the work is report type stuff which we want to have a low priority, but need it
to get throw any bind activity quickly. It’s a third party product so we don’t
have much, if any, control over the SQL coming in.

Thanks,
Jay

------------------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd., St. Petersburg, FL. 33716
Office: 727-227-2144 | Cell: 727-215-5794
[login to unmask email]
------------------------------------------------------------------------

________________________________

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind

Ray

I am not sure, if I understand, what you were telling. Do you mean, that the
dynamic bind is performed by the DDF-address-space?

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



>
________________________________

>Von:IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
>Gesendet: Mittwoch, 1. Dezember 2010 19:31
>An: [login to unmask email]
>Betreff: [DB2-L] Service Units for a Dynamic Bind
>Anyone have a ROT on the amount of service units it takes to perform a dynamic
>bind? I’m sure it fluctuates depending on the complexity of the SQL, but we’re
>trying to make sure that we have enough juice being allotted to our period 1 WLM
>service classes for the distributed work coming in so that they don’t get
>“stuck” due to CPU constraints and end up holding locks on system objects while
>in the middle of a dynamic bind. We also don’t want to thru too much juice
>their way because we don’t want them to dominate unnecessarily. We’re just
>trying to avoid system level contention.
>
>As always, Thanks!
>Jay
>
>------------------------------------------------------------------------
>Jay Reavill
>DBA
>Fidelity National Information Services, Inc.
>11601 Roosevelt Blvd., St. Petersburg, FL. 33716
>Office: 727-227-2144 | Cell: 727-215-5794
>[login to unmask email]
>------------------------------------------------------------------------
>
>_____________
>
>The information contained in this message is proprietary and/or confidential. If
>you are not the intended recipient, please: (i) delete the message and all
>copies; (ii) do not disclose, distribute or use the message in any manner; and
>(iii) notify the sender immediately. In addition, please be aware that any
>message addressed to our domain is subject to archiving and review by persons
>other than the intended recipient. Thank you.
>_____________
>
>
________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
>already an IDUG member, please register here.
>
_____________

The information contained in this message is proprietary and/or confidential. If
you are not the intended recipient, please: (i) delete the message and all
copies; (ii) do not disclose, distribute or use the message in any manner; and
(iii) notify the sender immediately. In addition, please be aware that any
message addressed to our domain is subject to archiving and review by persons
other than the intended recipient. Thank you.
_____________

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jay Reavill

Re: AW: Service Units for a Dynamic Bind
(in response to Myron Miller)
Thanks Myron,



Yes, that is the way I understand it. We're just trying to make sure they can get thru the bind without getting stuck and then drop down shortly after.



Thanks again,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Friday, December 03, 2010 11:14 AM
To: [login to unmask email]
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind



But Jay,

Understand that the SQL and dynamic BIND are both executed at the same priority. You can't specify one priority for the bind and another for the sql. It doesn't work that way.



What ever priority you have for the application, set by WLM for the DDF address functions (and you can specify in WLM different priorities for different authids) is what the bind runs at. So if an app has a very low priority, it's dynamic bind will run at that priority and get whatever cycles everything for that app gets.



Myron



________________________________

From: "Reavill, Jay" <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 3, 2010 8:50:29 AM
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind

Hi Walter,



Assuming you meant me (Jay)...



No, I wasn't saying that it is performed by the DDF address space, but that is a good question. Is it? I was under the impression that it was being performed by the SQL's distributed thread coming in and thereby under the priority of its service class. If that's not the case then we need to take a different look at it. We're just trying to make sure that the DDF threads coming in have enough juice to get thru the dynamic bind (if needed) without getting stalled. Some of the work is report type stuff which we want to have a low priority, but need it to get throw any bind activity quickly. It's a third party product so we don't have much, if any, control over the SQL coming in.



Thanks,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind



Ray



I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?



regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email] <mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996








________________________________


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I'm sure it fluctuates depending on the complexity of the SQL, but we're trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don't get "stuck" due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don't want to thru too much juice their way because we don't want them to dominate unnecessarily. We're just trying to avoid system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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



_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________




________________________________


Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________



________________________________

Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



________________________________

Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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: AW: Service Units for a Dynamic Bind
(in response to Jay Reavill)
Everyone sure about this. In previous conversations with DB2 developers they stated that the bind/prepare was done at a higher priority in order to minimize lock duration. This was quite a number of years ago so I may be wrong. Best to open at ETR, no?

Dan



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Friday, December 03, 2010 10:14 AM
To: [login to unmask email]
Subject: [SPAM] Re: AW: Service Units for a Dynamic Bind



But Jay,

Understand that the SQL and dynamic BIND are both executed at the same priority. You can't specify one priority for the bind and another for the sql. It doesn't work that way.



What ever priority you have for the application, set by WLM for the DDF address functions (and you can specify in WLM different priorities for different authids) is what the bind runs at. So if an app has a very low priority, it's dynamic bind will run at that priority and get whatever cycles everything for that app gets.



Myron



_____

From: "Reavill, Jay" <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 3, 2010 8:50:29 AM
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind

Hi Walter,



Assuming you meant me (Jay)…



No, I wasn’t saying that it is performed by the DDF address space, but that is a good question. Is it? I was under the impression that it was being performed by the SQL’s distributed thread coming in and thereby under the priority of its service class. If that’s not the case then we need to take a different look at it. We’re just trying to make sure that the DDF threads coming in have enough juice to get thru the dynamic bind (if needed) without getting stalled. Some of the work is report type stuff which we want to have a low priority, but need it to get throw any bind activity quickly. It’s a third party product so we don’t have much, if any, control over the SQL coming in.



Thanks,

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind



Ray



I am not sure, if I understand, what you were telling. Do you mean, that the dynamic bind is performed by the DDF-address-space?



regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996








_____


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
Gesendet: Mittwoch, 1. Dezember 2010 19:31
An: [login to unmask email]
Betreff: [DB2-L] Service Units for a Dynamic Bind

Anyone have a ROT on the amount of service units it takes to perform a dynamic bind? I’m sure it fluctuates depending on the complexity of the SQL, but we’re trying to make sure that we have enough juice being allotted to our period 1 WLM service classes for the distributed work coming in so that they don’t get “stuck” due to CPU constraints and end up holding locks on system objects while in the middle of a dynamic bind. We also don’t want to thru too much juice their way because we don’t want them to dominate unnecessarily. We’re just trying to avoid system level contention.



As always, Thanks!

Jay



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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



_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3289 - Release Date: 12/03/10 01:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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: AW: Service Units for a Dynamic Bind
(in response to Daniel Luksetich)
Hi

Distributed workload and WLM is one of the emerging problem in DB2 (and z/OS) area, expecially if you're using WLC or your machine is capped. We had some times this problem in production, due to WLC capping and some absurd goals (a velocity goal of 90 for ALL types of CICS when the maximum achieved velocity is 70-75......).

Anyway measuring PREPARE msu consumption is difficult and not so significant as it strongly depends on SQL as other listers pointed out. You could try to evaluate the highest MSU usage (if you are able to measure it. I never tried it) and giving enough MSUs in your first WLM service class period to perform dyn bind + run your SQL expressed as n times your PREPARE MSUs, but it's a rough estimation and it's not so good with big workloads.
You've to classify your DDF transactions considering the whole transaction MSU consumption (which is varying as it's dynamic by definition) and not to perform a micro-management, in WLM environment is not recommended.

If you've MXG (for instance) you can evaluate the biggest transaction(s) from SMF records, look at their MSUs consuption and tuning your Service Class accordingly for DDF type workload. Consider that in WLM MSUs and period isn't the only parameter you've to consider, but there's priority,storage protection and some other things in a busy environment. Just an idea as I used MXG to obtain DDF transactions consuption for accounting in the past. But if you've problem during peak period probably you'd review your policy.

AFAIK PREPARE is processed (at least a part of the process) by the executives in RDS (see OWEN, IDUG 2003 Europe).

Just some thoughts

Max Scarpa
"Work is hunting me, but I'm faster"


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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: AW: Service Units for a Dynamic Bind
(in response to Max Scarpa)
Sorry Forgot:

DB2 process transaction according priority so 'if you're a king your Db2 work wil be very fast, if you're a poor devil your DB2 work will run very slow' (Horacio Terrizzano, various seminars). The whole Db2 transaction, not a part (apart some Db2 generated processes). So if your work for some reason isn't classified it'll run as DISCRETIONARY, the lowest priority.

Max Scarpa

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Ted MacNEIL

Re: AW: Service Units for a Dynamic Bind
(in response to Max Scarpa)
>You've to classify your DDF transactions considering the whole transaction MSU consumption (which is varying as it's dynamic by definition) and not to perform a micro-management, in WLM environment is not recommended.

1. It's not MSU consumption.
MSUs are Millions of Service Units -- a CPU value.
2. It's Service Units -- which includes two types of CPU, I/O, and (some times) Storage Occupancy.
3. These days of large storage configs tend to make memory contention a thing of the past.
4. It's not necessarily micro-management to tune DDF.

I once worked at a shop where customers were making dynamic queries for pricing.
Our DBA's had enabled the governor, so many queries were terminating before completion.
After analysis, we got rid of the governor, introduced two periods for DDF, with 90ish% completing in the first period.
I used MXG, a little knowhow, and a couple of hours of analysis.
We had happier customers, fewer retries, and better CPU usage.
Letting a transaction complete cost less than constantly terminating them before completion.

We didn't worry about preparation versus execution.
Rather, what did the majority use.
And, we did have some preferential customers run without falling into a second period.
The latter was a business decision, not a technical one.

-
Ted MacNEIL
[login to unmask email]

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Myron Miller

Re: AW: Service Units for a Dynamic Bind
(in response to Ted MacNEIL)
I can't say for sure. All I'm pretty sure about is that unless they've changed
it recently, there was no way to classify BIND/PREPARE with a different WLM
classification than the execution of the SQL for that user. I tried some time
ago with the systems i was responsible for setting up.

We did characterize some of the workloads (DDF processing) so that there were
several periods (just like TSO) so that the quick ones got thru faster and the
resource hogs got service at a much lower priority. In addition, certain
critical app's got preference, of course. Then we didn't worry about
contention. In fact, except for batch binds, I can't remember when we ever had
contention between a DDF thread Bind/prepare and anything else.

Myron



________________________________
From: Daniel Luksetich <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 3, 2010 12:31:03 PM
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind


Everyone sure about this. In previous conversations with DB2 developers they
stated that the bind/prepare was done at a higher priority in order to minimize
lock duration. This was quite a number of years ago so I may be wrong. Best to
open at ETR, no?
Dan

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Friday, December 03, 2010 10:14 AM
To: [login to unmask email]
Subject: [SPAM] Re: AW: Service Units for a Dynamic Bind

But Jay,
Understand that the SQL and dynamic BIND are both executed at the same
priority. You can't specify one priority for the bind and another for the sql.
It doesn't work that way.

What ever priority you have for the application, set by WLM for the DDF address
functions (and you can specify in WLM different priorities for different
authids) is what the bind runs at. So if an app has a very low priority, it's
dynamic bind will run at that priority and get whatever cycles everything for
that app gets.

Myron


________________________________

From:"Reavill, Jay" <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 3, 2010 8:50:29 AM
Subject: Re: [DB2-L] AW: Service Units for a Dynamic Bind
Hi Walter,

Assuming you meant me (Jay)…

No, I wasn’t saying that it is performed by the DDF address space, but that is a
good question. Is it? I was under the impression that it was being performed
by the SQL’s distributed thread coming in and thereby under the priority of its
service class. If that’s not the case then we need to take a different look at
it. We’re just trying to make sure that the DDF threads coming in have enough
juice to get thru the dynamic bind (if needed) without getting stalled. Some of
the work is report type stuff which we want to have a low priority, but need it
to get throw any bind activity quickly. It’s a third party product so we don’t
have much, if any, control over the SQL coming in.

Thanks,
Jay

------------------------------------------------------------------------
Jay Reavill
DBA
Fidelity National Information Services, Inc.
11601 Roosevelt Blvd., St. Petersburg, FL. 33716
Office: 727-227-2144 | Cell: 727-215-5794
[login to unmask email]
------------------------------------------------------------------------

________________________________

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Friday, December 03, 2010 5:03 AM
To: [login to unmask email]
Subject: [DB2-L] AW: Service Units for a Dynamic Bind

Ray

I am not sure, if I understand, what you were telling. Do you mean, that the
dynamic bind is performed by the DDF-address-space?

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



>
________________________________

>Von:IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Reavill, Jay
>Gesendet: Mittwoch, 1. Dezember 2010 19:31
>An: [login to unmask email]
>Betreff: [DB2-L] Service Units for a Dynamic Bind
>Anyone have a ROT on the amount of service units it takes to perform a dynamic
>bind? I’m sure it fluctuates depending on the complexity of the SQL, but we’re
>trying to make sure that we have enough juice being allotted to our period 1 WLM
>service classes for the distributed work coming in so that they don’t get
>“stuck” due to CPU constraints and end up holding locks on system objects while
>in the middle of a dynamic bind. We also don’t want to thru too much juice
>their way because we don’t want them to dominate unnecessarily. We’re just
>trying to avoid system level contention.
>
>As always, Thanks!
>Jay
>
>------------------------------------------------------------------------
>Jay Reavill
>DBA
>Fidelity National Information Services, Inc.
>11601 Roosevelt Blvd., St. Petersburg, FL. 33716
>Office: 727-227-2144 | Cell: 727-215-5794
>[login to unmask email]
>------------------------------------------------------------------------
>
>_____________
>
>The information contained in this message is proprietary and/or confidential. If
>you are not the intended recipient, please: (i) delete the message and all
>copies; (ii) do not disclose, distribute or use the message in any manner; and
>(iii) notify the sender immediately. In addition, please be aware that any
>message addressed to our domain is subject to archiving and review by persons
>other than the intended recipient. Thank you.
>_____________
>
>
________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
>already an IDUG member, please register here.
>
_____________

The information contained in this message is proprietary and/or confidential. If
you are not the intended recipient, please: (i) delete the message and all
copies; (ii) do not disclose, distribute or use the message in any manner; and
(iii) notify the sender immediately. In addition, please be aware that any
message addressed to our domain is subject to archiving and review by persons
other than the intended recipient. Thank you.
_____________


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3289 - Release Date: 12/03/10
01:34:00
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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: AW: Service Units for a Dynamic Bind
(in response to Myron Miller)
>1. It's not MSU consumption. MSUs are Millions of Service Units -- a CPU value.
>2. It's Service Units -- which includes two types of CPU, I/O, and (some times) Storage Occupancy.
- Service unit is the value you specify in Service class periods, obvious. It's a value you use in WLM, which is different from what your customer pays.

> 3. These days of large storage configs tend to make memory contention a thing of the past.
True if your z/OS sysprog gives you all memory she/he has and this is not always true, both in big shops and in small shops. Example: we tried to enlarge our BPs to do some BP tuning (they where quite small) but our z/OS sysprog didn't give us more memory. We had 4 GB of storage and 4 Gb *unused* (I mean 'offline') and we change our machine without using that 4 Gb. Still wondering why we bought that memory :-(((. On the other hand having big memory in some cases can raise new problems, see 'Large Memory Performance Studies' IBM, 2007

> 4.It's not necessarily micro-management to tune DDF.
Absolutely true, it's absurd to tune DDF in this way, it's mainly an application/DB2 tuning. In some case it's better only one period but it depends on application: if it's an order usually you're requested to process it as quick as possible, if it's an inquiry to see if the order is being processed you can go 'slower'. What I meant was WLM micro-management is useless, for example it's useless to manage DDF using 2 periods having very close goals (see Samson,Enrico, various papers). It seems logical, but I saw it some times. We never used governor for DDF transactions, for instance.

I used MXG as well and it's a great tool not too much expensive. The problem is sometimes you've collect too many SMF records
Max Scarpa

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** 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