We’ve talked about CICS transaction performance improvements and security performance improvements. Now let’s check into the Sort Work, EDM, RID and Buffer Pools.
DB2 Sort Work Pool
The DB2 system work area consists of a number of DB2 DSNDB07 work tablespace data sets to handle the DB2 SQL sort requirements.The DB2 sort work pool data sets were in many extents in the system I examined. They needed to be consolidated to minimize the extra I/Os using these extended data sets.Additionally, these sort work data sets needed to have larger primary allocations and a zero secondary allocations quantity to avoid secondary extents.
The 32k sort pool needed its extents consolidated also.The usage of the pool should also be monitored to discover poor program performance or programs that are executing poor SQL that is referencing result sets greater than 4k. Those sorts are forced into the 32k sort pool.
Buffer Pool Sizing and Settings
One of the company’s systems used a number of buffer pools to cache the various database data and index information and improve the overall efficiency of the database activity.The number, size, and mixture of different database objects allocated to the number of buffer pools can have a dramatic positive performance impact on the processing.
By starting to cache similar database objects, objects with similar read or write access processing patterns, the data can better cached and improve performance immediately.By caching the correct data in the buffer pools and combining or isolating different database objects, more data can be cached and CPU and I/O requirements reduced.
Further evidence that the buffer pools should be analyzed was that some buffer pools were very busy while some were not being used at all.For example, one buffer pool supported a number of database objects, making it very busy throughout the day, while two other buffer pools with high allocations were not being used at all.Additionally, the buffer pools needed to be sized according to their usage, taking buffers from the less busy pools and adding to the busiest buffer pools.
Finally, the company needed to consider having different buffer pool configurations at different points of time. Some clients benefit tremendously by having different buffer pool configurations for on-line daytime workloads and then modifying their buffer pool for a night time batch workload.Since the system had very distinct database tables that were active during each of these periods, having different buffer pool allocations for daytime and nighttime processing cut 1.5 hours of elapsed time and CPU demand off their DB2 processing.
EDM Pool Sizing and Settings
The DB2 environment has many caching mechanisms to improve system and application performance.One of these pools is the EDM pool which helps handle transactions in the system.It consistently had 5-10% free memory space.It is vital that free space be maintained in the EDM pool but only having this much free space consistently indicates some issues.
Because the company was behind in maintenance, I recommended that they bring their DB2 maintenance up to date. This was particularly important since there were a number of EDM pool fixes (PTFs) in the DB2 maintenance.
Once the maintenance was applied, the size of the EDM pool size needed to be monitored and analyzed. The EDM pool memory was rather large and the maintenance and freeing of some old DBRMS within some big DB2 Plans helped reduce EDM workload and add some needed free space.
RID Pool Sizing and Settings
The row id (RID) pool is used for the RID sorts that accompany optimizer access path techniques such as list pre-fetch, hybrid join, and multi-index access. These access paths were very common within the company’s environment and the RID pool was overflowing with work.When these overflow conditions occurred, the SQL access method changed to a tablespace scan causing a huge increase in the number of pages accessed and the required resources to retrieve the information.This bad situation happened 613 times in one day, causing many RID limit failures, leading to thousands of additional I/Os and high CPU usage.
I recommended that the RID pool size should be increased as soon as possible. The RID Pool can be defined up to 1 GB for Version 7 systems and much larger now that it is above the line in DB2 Version 8 and Version 9. Once that was done, further analysis would detail the transaction frequency and the number of rows referenced through the pre-fetch, hybrid join and multi-index access.
Summary
It is important to monitor and analyze the various pools in your systems. Check for the following:
- Sort work pools aren’t going into secondary extents.
- Buffer pools are sized correctly for their activity.
- Data objects and index objects are in different buffer pools.
- Data objects with similar access characteristics are defined to the same buffer pools.
- EDM pools maintain enough free space.
- RID pool is adequately sized and RID pool overflows are not happening in my environment.
I’ll post more information in a few days that will help you improve your own DB2 systems.
________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Leave a Reply