DB2 Performance Tuning

With this post we are going to delve deeper into problems and recommendations to improve DB2 performance tuning and DB2 security.

DB2 Performance Discoveries and Recommendations

The operating system, CICS and DB2 systems were operating efficiently and managed well within the overall environment. As the number of transactions and systems continue to grow, the systems will require more CPU and performance tuning attention. As the number of different diverse workloads, the software packages and number of transaction increases the amount of time and resources to manage and tune the efficiency of the systems will continue to increase regardless of the platform, database or application.

DB2 CICS RDO Thread Reuse

The connection between DB2 and CICS is very important. These connections settings are handled through the RDO interface that has many DB2 thread reuse and security settings. Based on my experience, these settings can have a dramatic impact on performance and CPU consumption.

The settings were reviewed for the DB2 workload transactions running through the systems CICS environments. These environments averaged approximately over a million transactions per day and had extensive DB2 workloads.

The current CICS and DB2 connection settings did not fully leverage the performance settings that were available to improve connection security, thread and DB2 package or plan reuse. My recommendation was that the settings should be monitored and updated monthly to reflect the transaction workload and dedicated threads defined for many of the larger volume CICS DB2 transactions.

Given that the company has just begun implementing these RDO settings and improvements, there were still opportunities for RDO tuning through the priority, security and plan settings. The dedicated threads were recommended to be run at the normal priority setting, with a security group profile that allows users to reuse their DB2 plan execution authorizations. These settings along with the number of threads allocated, protected and use in the CICS DB2 Pool needed to be fined tuned for each of the environments.

Reviewing and improving these settings through the CICS Statistics DB2 Connection reports that are buried inside the overall CICS Statistics report provides great details on the transactions and thread usage. Changing these RDO settings had an immediate positive effect by reducing CPU consumption. The company could save more by fine tuning all their RDO settings.

DB2 Security Improvements

The DB2 environment had security settings that were used in conjunction with the security system, Top Secret. The company was using both DB2 and Top Secret security extensively, causing additional system-checking overhead. DB2 and Top Secret should be configured to leverage the use of secondary authorization security verification. The use of secondary authorization security checking is common at many large government and banking clients and should be implemented as soon as possible. By using secondary security, the system checking security is done once and reused because of the grouping that allows CICS transaction threads to be reused. This will save the extra system overhead of double-checking security for every transaction execution and save CPU.

Summary

If your system is heavily dependent on CICS transactions:

Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.

Update the RDO settings so that the DB2 workload is using mainly dedicated and reused threads.

Insure that your RDO settings use a minimum of pool threads.

Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.

Make sure that you aren’t duplicating security checking with both DB2 and another security product.

Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.

AnalysisIn order to do a complete performance analysis of the system, the system and application statistics were reviewed using the standard DB2 performance reports. This data provided a basis of the various system, database, application and SQL observations and improvement recommendations. These statistics along with system, process, and application documentation, interviews with application programmers and observations of the workload guided the investigation of the CPU consumption and CPU reduction effort.

Current Enterprise ArchitectureThe enterprise architecture had evolved over the years to support many diverse database systems. This caused several databases to be cloned and their transactions workloads to be intermixed. This combination of CICS transactions provided a diverse workload of different data requirements, run time durations and application types.

This combination of workloads runs on a single CPU mainframe environment that supports both the test and production environments. Workloads come into the system through a variety of interfaces: CICS, Visual Basic and Web applications using MQ Series Connect and batch jobs throughout the day. These applications access a variety of database tables that support the corporation’s nation-wide business needs.

The enterprise applications environment with a mix of applications operates efficiently experiencing occasional dramatic CPU application requirement spikes. These application CPU requirement spikes manifest themselves throughout the day when CICS program errors occur and dumps are created. These dumps cause the system to pause and dump the transaction situation. This occurs too frequently; almost once every 15 minutes in the production CICS region. Busy business periods of multiple concurrent transactions with a large memory footprint also show stress within the systems.

Work Load Manager

The architecture of the system and its performance are controlled through a variety of software with Work Load Manager (WLM) playing a central role in overall system performance.WLM controls CPU and provides priorities of the different subsystems, online workload and batch processes.

Analysis of the WLM settings needed to be done to determine the optimum and most efficient workload software settings and determine whether the DB2, CICS, and batch transaction have the compatible settings to maximize throughput.

Observing the system processing discovered that the workflow accomplished is fluctuating when the systems has errors or dumps occurring in the various CICS regions. These dumps against the system workflow showed that the system CPU peaked and workflow was severely impacted.

When an on-line program error or dump occurs its core dump documentation and resolution are the highest priority within the system stopping or pausing all other work. An example of the problem occurred by 10:30 a.m. on a summer day. Five regions had 27 errors/dumps occur by that time, which is one every four minutes (27/150 minutes) during the production work day. Industry standards typically have a very small number of these errors or dumps occur in their production regions. This problem directly related to the application quality assurance testing and this situation will only continue to degrade the overall workflow and overall performance of the systems.

CICS Region Configuration and Allocations

The architecture of the CICS systems and the on-line programs reflects how additional data and capabilities have been added. New CICS regions and databases have been added to the workload as additional systems were added to the workload and additional features added to the applications.

These workloads were each separated into their own regions. To improve the overall workflow and provide further room to efficiently grow the CICS transaction workload a Sysplex architecture could be considered. The CICS Sysplex architecture separates the workload out to terminal owning regions (TOR), application owning regions (AOR) and data owning regions (DOR) that can be finely tuned to service each specific type of workload. These regions work together to spread and balance the peak transaction workloads.

Summary

All of these architecture, system, database, application and SQL considerations provide the opportunity for CPU cost reductions. These cost reductions could be achieved through system tuning, database design analysis, application SQL documentation and application coding standards and reviews. Implementing these has the potential of saving tremendous CPU capacity and delaying a CPU upgrade.

Analyze the number of abends, deadlocks and the number of dumps within different parts of your applications. These deadlocks and dumps take a tremendous amount of CPU resources at critical times within your system.

Make sure that your Work Load Manger (WLM) is set up properly to distribute the CPU resources adequately and properly to the various database systems and applications. Having the database at the same or below the applications can cause performance and overall throughput problems.

Validate the settings between your CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.