Once you have reviewed the overall system for the aspects that we have discussed over the last few months, it’s time to drill down into your more critical transactions and processes. Critical transactions are those frequently executed transactions that are using excessive CPU or I/O resources. For this analysis using a performance monitor is necessary to research the details of the performance workload.
Find the Heavy Hitters
The first aspect that needs to be researched is to find the programs doing the most DB2 related I/O or CPU. This is done by sorting the performance monitor runtime statistics information by several different categories. The first category to sort by is “Workload by Elapsed Time in DB2.” This will show what transactions or processes spend the most time within DB2.
The next thing to do is sort the elapsed time within DB2 for a specific time period interval. Analysis at this particular client showed that a single process, which we’ll call Daily Reports, spent the most time in DB2, more than four times as much time in DB2 than any other process during the day and substantially more than the online CICS transactions. This single process used 18.4%, or almost double used by any of the other CICS transactions during this online processing time period.
The next workloads that spent the most time in DB2 were the CICS region transactions doing their normal processing throughout the day. These transactions quick processing requests show that they spend substantial time in DB2, but only a fraction of the DB2 processing time was used by the Daily Reports process.
The Daily Reports process also processed a very large number of SQL statements during this same time period. These SQL statements requested a large number of GetPages to process the application requests during this time period interval.
The next aspect examined of the workload was the number of SQL statements executed by each process. The data showed that again Daily Report process was providing the largest number of SQL statements to be executed, substantially more than any other CICS transactions.
Reports also showed a group of repetitive CICS transactions in the top ten doing a large portion of the work also.
Looking at another report showed that the Daily Report process and the same group of CICS transactions were the top DB2 CPU consumers.
Researching to see if there might be a common table that might be causing performance problems for this set of CICS transactions, I queried the DB2 catalog to find their common tables. This list indicated the two most commonly used tables. Researching one table found that it 11 different indexes defined. Having this many indexes is extraordinary especially when the industry best practices says that 4 or 5 indexes per table should be the maximum for an online active table. This best practice is encouraged because of the extra overhead each index causes through updates, insert and delete activity. Discovering why the table had this many indexes needed further research.
Summary
Using performance reports and drilling down into the DB2 catalog can give you valuable information about specific performance problems. Use these tools to discover:
- Processes that use excessive resources
- Underlying data objects that might be causing some performance problems.
- Reasons why data object definition, particularly index definition, might be causing performance problems.
____________________________________________________
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