WLM & DSNDIST high volume & batch

David Lawrence

WLM & DSNDIST high volume & batch

Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.

We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.

What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.

We have not changed WLM except to add more  functional id's and the new stored procedures.

Thanks

Lizette Koehler

WLM & DSNDIST high volume & batch
(in response to David Lawrence)
I think you will find this is an "it depends" type of question

It depends on
1) What SLAs you need to meet
2) What memory you have on the CPU (CEC)
3) How many mips you have available at any given time
4) What your capacity planners have to say
5) What your z/OS sysprogs have to say
6) What caused your spike
   a) Is that an every day occurrence
   b) is it an abnormal event
7) how well tuned your queries are
8)  What other workload you need to share on the CPU (CEC)

And probably other thoughts.

If you have an LPAR dedicated to just DB2, then the WLM settings can be in favor of DB2.  If the LPAR needs to share resources, everyone needs to play nice in the sandbox

Lizette



-----Original Message-----
From: David Lawrence
Sent: Jul 6, 2017 10:50 AM
To: [login to unmask email]
Subject: [DB2-L] - WLM & DSNDIST high volume & batch


Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.
We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.
What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.
We have not changed WLM except to add more  functional id's and the new stored procedures.
Thanks

Wayne Campbell

WLM & DSNDIST high volume & batch
(in response to David Lawrence)
H David,

Tuning WLM is an art, or more akin to voodoo. It’s a balancing act of available resources (CPU). In WLM, you set goal that it tries to meet by adjusting dispatch priority of the different addresses spaces. There are various way to set these goals one is response time (online transactions) another is throughput (Batch). One thing about WLM is if WLM determines that if it cannot meet the goal, it will stop trying and allocate the resources to other places.

As a rule, you want to set the priority in the following order:


1) At the highest priority, you want the system address spaces. RACF, OMVS, JES modules that actually run the computer. You also want yourDB2 IRLM at this Level.


2) The next highest level – should be the other db2 subsystem address spaces MSTR, DBM1, DIST.


3) Below these top two levels, the applications should be assigned to meet the SLA.

If you assign an application above the DB2 address spaces, you can get thrashing and slow thing down. Then when you include all the other stuff that is running on the box, CICS, WebSphere, whatever, everything needs to be balanced out. Changing one thing can kill you or make everything flow fine. Don’t think about going out and changes WLM unless you really know what you’re doing. Even the people that are WLM expert change thing vary carefully, and watch to see what the results are.



Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]<mailto:[login to unmask email]>

From: David Lawrence [mailto:[login to unmask email]
Sent: Thursday, July 06, 2017 10:51 AM
To: [login to unmask email]
Subject: [DB2-L] - WLM & DSNDIST high volume & batch


Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.

We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.

What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.

We have not changed WLM except to add more functional id's and the new stored procedures.

Thanks

-----End Original Message-----

David Lawrence

WLM & DSNDIST high volume & batch
(in response to Wayne Campbell)
Wayne thanks for your reply on WLM.

I have a question about buffer pools , they have had the number hear for many years , they main change was adding more and more storage to BP0, From what I have read in many white papers and red books that is a bad idea.
You really need to spread things out and separate index’s from tables spaces, and most of all stay away from BP0 for user table’s and index’s. Here is what ours looks like now, please let me know what you think, maybe I can convince our DBA’s and out management to change things.
Your input will be appreciated.

NAME ,ID , USE , VPSIZE , HPSIZE , VPSEQT , VPPSEQT
BP0 , 0 , 16335 , 7340032 , N/A , 80 , 50 ,
BP1 , 1 , 33 , 5242880 , N/A , 80 , 50 ,
BP16K0 ,20 , 42 , 20000 , N/A , 80 , 50 ,
BP16K2 ,22 , 5 , 1048576 , N/A , 80 , 50 ,
BP2 , 2 , 30 , 9437184 , N/A , 80 , 50 ,
BP32K ,80 , 299 , 24000 , N/A , 80 , 50 ,
BP32K1 ,81 , 224 , 24000 , N/A , 80 , 50 ,
BP32K7 ,87 , 19 , 24000 , N/A , 90 , 50 ,
BP7 , 7 , 19 , 120000 , N/A , 95 , 50 ,
BP8K0 ,00 , 29 , 15000 , N/A , 80 , 50 ,


David Lawrence
z/OS Systems Programming
Email: [login to unmask email]<mailto:[login to unmask email]>

1 Primerica Parkway
Duluth GA 30099
(470) 564-7850

From: Campbell, Wayne [mailto:[login to unmask email]
Sent: Wednesday, July 19, 2017 8:11 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: WLM & DSNDIST high volume & batch

H David,

Tuning WLM is an art, or more akin to voodoo. It’s a balancing act of available resources (CPU). In WLM, you set goal that it tries to meet by adjusting dispatch priority of the different addresses spaces. There are various way to set these goals one is response time (online transactions) another is throughput (Batch). One thing about WLM is if WLM determines that if it cannot meet the goal, it will stop trying and allocate the resources to other places.

As a rule, you want to set the priority in the following order:


1) At the highest priority, you want the system address spaces. RACF, OMVS, JES modules that actually run the computer. You also want yourDB2 IRLM at this Level.


2) The next highest level – should be the other db2 subsystem address spaces MSTR, DBM1, DIST.


3) Below these top two levels, the applications should be assigned to meet the SLA.

If you assign an application above the DB2 address spaces, you can get thrashing and slow thing down. Then when you include all the other stuff that is running on the box, CICS, WebSphere, whatever, everything needs to be balanced out. Changing one thing can kill you or make everything flow fine. Don’t think about going out and changes WLM unless you really know what you’re doing. Even the people that are WLM expert change thing vary carefully, and watch to see what the results are.




Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]<mailto:[login to unmask email]>

From: David Lawrence [mailto:[login to unmask email]
Sent: Thursday, July 06, 2017 10:51 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - WLM & DSNDIST high volume & batch


Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.

We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.

What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.

We have not changed WLM except to add more functional id's and the new stored procedures.

Thanks

-----End Original Message-----

-----End Original Message-----

David Lawrence

WLM & DSNDIST high volume & batch
(in response to Wayne Campbell)
Wayne we also run a ca product PTXMAN that captures ALL SQL every 15 minutes no matter how long it runs.
I think we should only capture long running SQL, when it kicks off it runs at high priority and if the CPU is running around 95% there is a slowdown in DB2 until PTXMAN unloads all the SQL.
What are your sugjestions ?
Thanks

David Lawrence
z/OS Systems Programming
Email: [login to unmask email]<mailto:[login to unmask email]>

1 Primerica Parkway
Duluth GA 30099
(470) 564-7850

From: Campbell, Wayne [mailto:[login to unmask email]
Sent: Wednesday, July 19, 2017 8:11 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: WLM & DSNDIST high volume & batch

H David,

Tuning WLM is an art, or more akin to voodoo. It’s a balancing act of available resources (CPU). In WLM, you set goal that it tries to meet by adjusting dispatch priority of the different addresses spaces. There are various way to set these goals one is response time (online transactions) another is throughput (Batch). One thing about WLM is if WLM determines that if it cannot meet the goal, it will stop trying and allocate the resources to other places.

As a rule, you want to set the priority in the following order:


1) At the highest priority, you want the system address spaces. RACF, OMVS, JES modules that actually run the computer. You also want yourDB2 IRLM at this Level.


2) The next highest level – should be the other db2 subsystem address spaces MSTR, DBM1, DIST.


3) Below these top two levels, the applications should be assigned to meet the SLA.

If you assign an application above the DB2 address spaces, you can get thrashing and slow thing down. Then when you include all the other stuff that is running on the box, CICS, WebSphere, whatever, everything needs to be balanced out. Changing one thing can kill you or make everything flow fine. Don’t think about going out and changes WLM unless you really know what you’re doing. Even the people that are WLM expert change thing vary carefully, and watch to see what the results are.




Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]<mailto:[login to unmask email]>

From: David Lawrence [mailto:[login to unmask email]
Sent: Thursday, July 06, 2017 10:51 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - WLM & DSNDIST high volume & batch


Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.

We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.

What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.

We have not changed WLM except to add more functional id's and the new stored procedures.

Thanks

-----End Original Message-----

-----End Original Message-----

Javier Estrada Benavides

RE: WLM & DSNDIST high volume & batch
(in response to David Lawrence)

Hello:

  I've also used PTXMAN and I have often seen that in such high volume moments the PTXMAN CPU gues up in an undesired way, so you might also want to restrict that with a proper WLM service class (I've also seen cases in which both the PTXMAN task and the CA Sysview task IDB2xxxx go up in CPU consumption in the less convenient moment possible).

 

 I liked the voodoo analogy :) so I'll say that tuning bufferpools is another kind of voodoo magic (choose your kind of magic, haha). The "magic" is not completely on the sizes, and I also see that the thresholds you're showing are the defaults. I can quickly say that tuning bufferpools is about checking what kind of prefetching they're doing (random or sequential), redirecting your work files to BP7 and customize the thesholds just for that, see what tablespaces are monopolizing a particular bpool, separating allocations (all manuals tell you that you should NEVER forget you have a lot of bufferpools to distribute among, and different page sizes) and adjusting those thresholds according to all the previous points. Last but not least, you might also want to check what happens on your RID and SORT pools during those times (that's a bit of extra help). That's all an ongoing process.

 

Hope that helps :)

Regards,

Javier Estrada Benavides

Certified DB2 for z/OS System Admin, Mexico.

Joel Goldstein

WLM & DSNDIST high volume & batch
(in response to David Lawrence)
Without knowing anything about your applications, it certainly appears that the system would probably benefit from some serious pool tuning.



The only objects that should be in BP0 are the catalog and directory.



That being said, we don’t have any idea about your overall system performance, application performance, and IO rates just by looking at pool sizes.



Regards,

Joel



Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Lawrence, David [PRI-1PP] [mailto:[login to unmask email]
Sent: Thursday, July 20, 2017 4:08 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: WLM & DSNDIST high volume & batch



Wayne thanks for your reply on WLM.



I have a question about buffer pools , they have had the number hear for many years , they main change was adding more and more storage to BP0, From what I have read in many white papers and red books that is a bad idea.

You really need to spread things out and separate index’s from tables spaces, and most of all stay away from BP0 for user table’s and index’s. Here is what ours looks like now, please let me know what you think, maybe I can convince our DBA’s and out management to change things.

Your input will be appreciated.



NAME ,ID , USE , VPSIZE , HPSIZE , VPSEQT , VPPSEQT

BP0 , 0 , 16335 , 7340032 , N/A , 80 , 50 ,

BP1 , 1 , 33 , 5242880 , N/A , 80 , 50 ,

BP16K0 ,20 , 42 , 20000 , N/A , 80 , 50 ,

BP16K2 ,22 , 5 , 1048576 , N/A , 80 , 50 ,

BP2 , 2 , 30 , 9437184 , N/A , 80 , 50 ,

BP32K ,80 , 299 , 24000 , N/A , 80 , 50 ,

BP32K1 ,81 , 224 , 24000 , N/A , 80 , 50 ,

BP32K7 ,87 , 19 , 24000 , N/A , 90 , 50 ,

BP7 , 7 , 19 , 120000 , N/A , 95 , 50 ,

BP8K0 ,00 , 29 , 15000 , N/A , 80 , 50 ,





David Lawrence

z/OS Systems Programming

Email: David.Lawrence <mailto:[login to unmask email]> @primerica.com



1 Primerica Parkway

Duluth GA 30099

(470) 564-7850



From: Campbell, Wayne [mailto:[login to unmask email]
Sent: Wednesday, July 19, 2017 8:11 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: WLM & DSNDIST high volume & batch



H David,



Tuning WLM is an art, or more akin to voodoo. It’s a balancing act of available resources (CPU). In WLM, you set goal that it tries to meet by adjusting dispatch priority of the different addresses spaces. There are various way to set these goals one is response time (online transactions) another is throughput (Batch). One thing about WLM is if WLM determines that if it cannot meet the goal, it will stop trying and allocate the resources to other places.



As a rule, you want to set the priority in the following order:



1) At the highest priority, you want the system address spaces. RACF, OMVS, JES modules that actually run the computer. You also want yourDB2 IRLM at this Level.



2) The next highest level – should be the other db2 subsystem address spaces MSTR, DBM1, DIST.



3) Below these top two levels, the applications should be assigned to meet the SLA.



If you assign an application above the DB2 address spaces, you can get thrashing and slow thing down. Then when you include all the other stuff that is running on the box, CICS, WebSphere, whatever, everything needs to be balanced out. Changing one thing can kill you or make everything flow fine. Don’t think about going out and changes WLM unless you really know what you’re doing. Even the people that are WLM expert change thing vary carefully, and watch to see what the results are.









Wayne Campbell

DB2 DBA

Administrative Office of the Courts

(360) 705-5268

Email: [login to unmask email]



From: David Lawrence [mailto:[login to unmask email]
Sent: Thursday, July 06, 2017 10:51 AM
To: [login to unmask email]
Subject: [DB2-L] - WLM & DSNDIST high volume & batch



Last Monday we had very high volumes coming in to DSNDIST and then around 6:12 the batch schedule kicked off.

We had WLM lower the dsndist tranactions performance to handle the batch load, we did not have enough CPU for both.

What is the best practices for WLM and DB2 tasks, I have been searching for a manual have not found one yet.

We have not changed WLM except to add more functional id's and the new stored procedures.

Thanks



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Russell Peters

RE: WLM & DSNDIST high volume & batch
(in response to David Lawrence)

At the very least you should have a bufferpool designated for your sort tablespaces only, a 4k and a 32k. BP0 should contain the db2 catalog structures only. I also have bufferpools that contain only tables that have very high access rates with little or no I/O, set with PGSTEAL(NONE). I have a total of 32 active bufferpools defined. Also, I just started working with bufferpool simulation. Per IBM support, the simulation notes page I/Os for pages not currently in the bufferpool, then records the number of times any page is re-accessed to show how many I/Os could have been avoided if the bufferpool was larger, and any avoidable I/O delay. I'm looking at data for business hours only. We have memory to spare so will likely make some changes.