By Craig S. Mullins
Automation is a key aspect of modern IT development and administration. More and more processes are being automated to make software systems run more effectively and efficiently. A key target for automation of Db2 processes is utility scheduling and processing.
Db2 utility automation refers to the process of automating the execution and management of Db2 utilities, such as REORG or RUNSTATS, to optimize database performance, reduce manual intervention, and ensure efficient database maintenance. This is especially relevant for Db2 for z/OS, where automation can significantly enhance operational efficiency.
A Quick Overview of Db2 Utilities
First of all, let’s be clear on what is meant by a Db2 utility. A utility is generally a single purpose program for moving and/or verifying database pages; examples include LOAD, UNLOAD, REORG, RUNSTATS, CHECK, COPY, and RECOVER. The Db2 utilities provide core functionality to ensure the effective operation and performance of Db2 databases and programs.
The LOAD utility is used to populate Db2 tables with data. It is faster than inserting data row-by-row. It is ideal for initially populating a table with data, bulk data ingestion, migrating data, and refreshing environments.
The UNLOAD utility is essentially the opposite of the LOAD utility. It is used for extracting data from Db2 tables into external files, more easily and effectively than writing a program to SELECT and write the data. UNLOAD is useful for data migration, reporting, and moving data between environments.
The REORG, or Reorganization utility, rearranges data in indexes and table spaces to improve access efficiency. Over time, data fragmentation causes performance degradation. REORG reclaims space, improves data clustering, and enhances access speed.
The RUNSTATS utility collects metadata statistics about Db2 database objects and their data, storing information on things like column cardinality, column distribution, clustering, and fragmentation to be used by the Db2 Optimizer for query optimization. Running RUNSTATS is crucial to enable Db2 to generate the best possible SQL access paths, thereby improving query performance.
There are three variations of the CHECK utility: CHECK DATA, INDEX, and LOB. CHECK DATA is used to validate table and referential integrity constraints, detecting corruption. CHECK INDEX reviews index structures for inconsistencies or corruption to prevent access issues and performance degradation. CHECK LOB reviews Large Object (LOB) data for integrity issues ensuring that the data is not corrupted, which could cause access errors.
The COPY utility is used to backup Db2 data. It creates image copies of table spaces or index spaces for recovery purposes. It is essential to ensure that your Db2 data is backed up so that it can be recovered in case of corruption, hardware failure, or human errors.
The RECOVER utility is used to restore table spaces or index spaces to a specific point in time using image copies and logs. It can be used for disaster recovery, undoing accidental changes, and restoring lost data. The REBUILD INDEX utility can be used to restore indexes, but instead of using image copies and log data, it rebuilds the index using the actual data in the Db2 table.
There are other Db2 utilities, such as DIAGNOSE, MODIFY RECOVERY, and QUIESCE, but these generally do not need to be automated.
Why Automate Db2 Utilities?
So, why is it important to automate Db2 utility processing? Current industry trends are making it more important to automate. The first consideration is the combination of data growth coupled with stagnant DBA growth. Organizations amass and use more data every year. According to Statista Big Data Analytics News, global data volume is increasing 22.5% year over year.
At the same time, organizations are not adding more DBAs & data management professionals to their staff. In a recent report, Computer Economics Avasant Research found that data management staff represents about 5% of the typical IT staff. Although this is a growing amount, it still pales in comparison to the growth in the amount of data over the same period.
So, there is an ever-growing, complex data environment that DBAs are expected to manage, but with fewer resources as data volume rise. Surely, automation can help.
Another important trend impacting DBAs is the increasing speed of application development, primarily due to the adoption of agile development techniques and DevOps. DevOps changes the application delivery lifecycle to a continuous loop, with continuous development, integration, and delivery of code. This continuous development activity causes DBA workload to become more continuous, too.
As DevOps automates the process of software delivery and infrastructure changes, the objective is to deliver any change anywhere throughout the organization into production quickly, safely, predictably, and in a controlled manner. But for DevOps to work for both development and operations (the DBA job is part of Ops) the entire development pipeline must be automated, not just for application development and delivery but also for operational implementation and support.
Again, the DevOps trend dictates additional automation of DBA activities.
Furthermore, there are general, overall benefits of Db2 utility automation, regardless of the prevailing industry trends. These include:
- Reduced manual effort for DBAs.
- Improved database performance and availability.
- Lower operational costs by optimizing system resources.
- Minimized downtime with intelligent scheduling and execution.
Key Qualities of Effective Db2 Utility Automation
So, why does automating Db2 utilities deliver advantages to organizations and DBA staffs? Using a tool to automate Db2 utility processing, such as Log-On Software’s Total Utility Control, can address several key automation opportunities and in doing so deliver many benefits.
Automated Scheduling
Using an automation tool to schedule utilities based on database workload and activity can ensure that you are running utilities when they need to be run. Automated scheduling can be integrated with industry-leading job schedulers such as IBM Workload Scheduler or BMC’s Control-M.
Intelligent Execution
Another key benefit of Db2 utility automation is ensuring utilities run only when necessary. Utility automation that is accomplished intelligently by reviewing Db2 statistics and system activity can rely on thresholds (e.g., percentage of disorganization for REORG, stale statistics for RUNSTATS, percentage of data changed for COPY).
Parallelism & Performance Optimization
Intelligent Db2 utility automation tools can also run utilities in parallel to reduce downtime. For example, running at the partition-level for REORG or COPY can optimize processing time. Furthermore, taking options such as Fast Log Apply (FLA) and Index Parallelism can be used to speed up the execution of RECOVER.
Cost & Resource Optimization
Automating utilities intelligently can help to balance CPU and I/O consumption, avoiding peak-hour execution. By running utilities during off-peak timeframes – that is, not during the peak monthly rolling four-hour average – it is possible to more cost-effectively run Db2 utilities.
Simplified Utility Execution
Using automation tools for Db2 utilities, DBAs can define automation policies for different objects, reducing manual intervention. Furthermore, tooling with self-healing capabilities can minimize DBA involvement by automatically restarting failed utilities when possible.
The Bottom Line
DBAs are being asked to do more. There are larger volumes and more types of data that need to be accessed more rapidly and from more sources. Everything must be done without prolonged downtime, while using and supporting new database types and capabilities, and with fewer DBAs as a percentage of IT staff than ever before. Clearly, automating Db2 utilities can help to alleviate these issues.
Recent Comments