Over the last two posts, I gave you an overview of a DB2 system that needed performance improvements.Here we are going to delve deeper into the problems and recommendations.
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 RCT Thread Reuse
The connection between DB2 and CICS is very important.These connections settings are handled through the RCT/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 RCT settings and improvements, there were still opportunities for RCT 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 RCT settings had an immediate positive effect by reducing CPU consumption. The company could save more by fine tuning all their 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/RCT settings so that the DB2 workload is using mainly dedicated and reused threads.
- Insure that your RDO/RCT 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.
Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:
5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits
________________________________________
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