DB2 Sort Work PoolThe 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 SettingsOne 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 within this DB2 Version 8 system 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 or in DB2 10 the RID pool entries overflow to work files within the DSNDB07 sort area 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 since it is above the line as of DB2 Version 8. 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.

SummaryIt 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 I/O activity rate.

Data objects and index objects are in separate buffer pools.

Data objects with similar access characteristics are defined to the same buffer pools.

EDM pools should be defined big enough to maintain 10 to 15% free space if possible

RID pool is adequately sized and RID pool overflows are not happening within the environment.

We’ve checked into the DB2 Sort Work, EDM, RID and Buffer Pools. During this post, I’ll talk about some of the other standard places to check for performance improvements.

DB2 System MaintenanceThe DB2 system software maintenance from IBM contains many fixes and performance adjustments in its software maintenance stream. When investigating this company’s maintenance levels, I discovered that their DB2 system is behind on its maintenance level, which does not allow the latest performance improvements to be leveraged. Maintenance also needs to be coordinated with the implementation of pre-tested Service Packs related to other IBM software products.These Service Packs test the compatibility between z/OS, IMS, MQ Series, CICS and DB2 and can help eliminate maintenance compatibility issues. By evaluating the latest release compatible with operating system, MQ Series, CICS and other software connecting to DB2, the company can apply the correct maintenance level for their DB2 Version. Yearly maintenance plans need to be developed to help all departments understand the dependencies and the need to apply maintenance on a regular schedule.

Dynamic Statement Cache Pool Sizing and Settings

Additional analysis showed that the Dynamic Statement Cache (DSC) was being leveraged for application efficiency. This recently implemented feature was working well and only needed to be fine-tuned. (The DSC holds SQL statements executed frequently and does not have to re-determine the access path, verify object existence or re-check security if various settings are the same in subsequent executions.) A good portion of the SQL statements at the company were being cached letting, DB2 use the previously optimized SQL executing in the system. Leveraging the DSC area has usually shown a 2 to 3% CPU savings per SQL transaction and should be monitored closely to make sure to maintain its efficiency.If your environment executes a large percent of dynamic SQL applications, the savings from leveraging the DSC area deserves on-going attention.

Summary

Checking the various aspects of your DB2 system can have a great impact on the performance of your system. Take a look at these areas to improve system performance:

Is your DB2 System Maintenance at the appropriate level? Do you have maintenance plans that include checking the Service Pack levels to ease the integration with IMS, CICS, MQ Series and other software within your environment?

Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?