Database Housekeeping
A Database Housekeeping study will assess whether your current strategy is designed to optimise performance, make efficient use of machine resources, and maximise service availability.
Database Housekeeping Module
The Database Housekeeping module is part of Triton’s comprehensive Mainframe Availability and Resilience Service (ZMARS). This module identifies how your database housekeeping practices and procedures could be improved to safeguard the stability and performance of your database applications by making efficient use of the CPU and ensuring reliable response times for critical operations.
What is Database Housekeeping?
Database Housekeeping involves reorganising database objects, collecting access path statistics, and optimising SQL performance through BIND/REBIND and PREPARE operations. These processes help maintain CPU efficiency and ensure good response times for critical transactions and batch processes.
Why it Matters
Automated threshold-driven reorganisation and accurate statistics collection are essential for optimal database performance. Without regular maintenance, databases can suffer from access path instability, inefficient query performance, and increased CPU costs, leading to degraded performance of critical SQL operations.
Database Housekeeping involves several key activities essential for maintaining optimal database performance:
Reorganisation of Database Objects:
For some database objects, frequent tablespace and index reorganisation is crucial for efficient CPU usage and maintaining good response times for SQL queries used in high-volume transactions and batch processes. It is important to choose which database objects to reorganise and when. and to understand the costs of running these processes compared to the benefits they bring, together with the risks of not running them.
Access Path Statistics Collection:
Accurate and timely collection of access path statistics, either during reorganisation or through standalone RUNSTATS utilities, is vital for optimised access path selection for both static and dynamic SQL.
BIND/REBIND and PREPARE Operations:
These processes offer opportunities for more optimal access path selection, although they also come with the risk of access path regression, which can degrade CPU and elapsed time performance. Therefore, you should protect and stabilize the access paths of good performing SQL.
Addressing these issues is essential for maintaining database efficiency, preventing performance degradation, and avoiding exposing the system to stress and the risk of a service outage.
How We Can Help
Our experienced consultants conduct a comprehensive technical study, reviewing your current REORG and RUNSTATS rules, and providing tailored recommendations to enhance your database performance.
We will
- Review the rules applied in determining the need for REORG and RUNSTATS, whether based on DSNACCOX settings or similar standards. Recommendations will be provided to upgrade existing rules and include new ones.
- Advise on which metrics should be collected and analysed via automation to drive REORG and statistics collection . These can help fine-tune physical database design and eliminate unnecessary REORGs.
- Analyse and review the BIND and REBIND options and recommend adjustments to enhance access path selection and stability.
- Evaluate strategies and designs to exploit Db2 continuous delivery (APPLCOMPAT) for both local applications (CICS, IMS/TM, Batch) and distributed applications.
The Triton study team will provide a detailed technical report to your senior technical leaders, detailing all findings and recommendations. A management summary will also be provided to outline key technical findings and recommendations.
 |
Learn more about Database Housekeeping and how it could benefit your business, get in touch |