By Bob Bersano, Kate Wheat, and Mac Gupta
IBM Db2 Automation Expert for z/OS was recently released as a new product offering to help automate and simplify Db2 maintenance. One of those maintenance tasks is, of course, running the REORG utility. The challenge is figuring out when a REORG operation is necessary. This timing is especially important for large objects with many partitions and terabytes of data for which REORG can take significant time and resources and potentially require an outage.
Typically, the goal of running REORG is to maintain application performance. REORG re-establishes data clustering and free space. However, you might have a significant amount of SQL that is not affected by the level of clustering and amount of free space. Thus, running REORG does not result in improved application performance. How can you tell?
Db2 Automation Expert offers a solution: intelligent REORG avoidance. This solution is based on comparing key performance indicators (KPIs), such as CPU, elapsed time, and GetPages, during performance windows. You set up a performance window when a set of objects will be accessed by an application. The first time this performance window opens, it will recommend a REORG to make sure each object is optimized. Then, the next time the window opens, the KPIs for each object will be recorded to create a baseline, and again, a REORG will be recommended. Then, each time after that, when the performance window opens, it will compare the current KPIs to the baseline, and if they have exceeded the percentage of each of the KPIs, it will recommend a REORG; if it hasn’t exceeded the percentages, it will not recommend a REORG. If it recommends a REORG, the baseline will be cleared, and the process starts over.
The comparison is based on certain threshold values that you can customize. For example, you might have the following REORG condition:
DB2_CPU performance must degrade by 10% or more, or DB2_ELAPSED performance must degrade by 20% or more.
When this condition is met, REORG is recommended, as shown in the following screenshot:
And yes, intelligent REORG avoidance is intelligent enough to perform this comparison only if there’s enough overlap between the SQL statements running in the baseline window and the comparison window. In other words, a significant chunk of SQL running in each window is the same SQL.
Note that to use intelligent REORG avoidance, you must have both IBM Db2 Query Monitor and Db2 Automation Expert installed.
Intelligent REORG avoidance is just one feature of Db2 Automation Expert. For more information about Db2 Automation Expert, see https://www.ibm.com/docs/daefz/1.1.0 and check out this video about Db2 Automation Expert.
Also, see other blogs in this series:
Bob Bersano is a Principal Solutions Advisor at Rocket Software.
Kate Wheat is a Senior Information Developer at Rocket Software.
Mac Gupta is Senior Director in Marketing at Rocket Software.
#IBMChampion#ibmchampions-highlights-home#ibmchampions-highlights