Free Up Your Time Using Broadcom’s Database Analyzer

By Denis Tronin and Steen Rasmussen

Are you a Db2 DBA who feels overwhelmed by the requirements for new services from your lines of business and the need to keep existing applications running 24/7? In today's fast-paced business environment, freeing up DBA time has a direct impact on the bottom line.

Efficiency and performance are paramount, especially when it comes to managing complex database systems like Db2 for z/OS. Most DBAs have their hands full managing rapidly evolving systems and applications. This lack of bandwidth leaves them with little to no time for the essential tasks of analyzing, tracking, and optimizing Db2 objects that contribute to continuous process improvement.

Enterprises need solutions that can conduct sophisticated analyses and proactively suggest corrective actions to keep Db2 databases operating at peak performance. The key to unlocking the full potential of your Db2 for z/OS subsystems lies in automation, and Database Analyzer for Db2 for z/OS (part of the Broadcom Utility Suite for Db2 for z/OS) is the secret sauce in your Db2 management recipe.

Database Analyzer provides robust database analysis and automation capabilities to help simplify administration. It is designed to increase database administrator productivity by leveraging Db2 Real-Time Statistics (RTS) and optionally collecting additional statistics on Db2 objects. These statistics are used to automate housekeeping, utility scheduling and execution, and to perform space management based on user-defined thresholds and site-specific conditions. Database Analyzer takes action based on parameters you can customize for your specific needs. It automates object maintenance to save you time and effort.

Let’s have a look at what it can offer.

Effortless Utility Management

Database Analyzer makes utility management a breeze by automating the execution of Db2 for z/OS utilities to ensure that essential tasks, such as reorganizations and image copies, are performed seamlessly. This automation saves you time and reduces the risk of errors associated with manual execution.

You can rely on the following Database Analyzer capabilities to simplify Db2 utility management:

Extract Procedures

• Automatically define which objects require processing or analysis using a set of seamlessly integrated components with generic object names and hierarchical dependencies
• Select objects dynamically based on specified maintenance criteria using a comprehensive list of conditions and recommended thresholds with Real-Time Object Selection (RTOS)
• Automatically manage newly created and dropped objects and avoid the need for manual intervention
• Use standard SQL to exclude objects specified in a user-defined exception table with Extended Query Facility (EQF)

Action Procedures

• Run necessary maintenance for either the objects selected by an extract procedure or for a specified object
• Ensure that objects with the highest need for maintenance are processed first, based on specified conditions with Prioritized Object Maintenance (POM)
• Run maintenance jobs immediately, store jobs for manual execution at a later time, or automate execution with a job scheduler with execution options
• Specify additional threshold or condition values to further refine the actions taken for selected objects with exception conditions
• Use intelligent processing to eliminate duplicate index reorgs when a corresponding tablespace (or partition) is selected
• Optionally promote from an index to a tablespace reorg based on your site-specific standards
• Specify job details and run utility jobs in parallel with Job Generation Parms options
• Dynamically limit the number of objects processed per step when objects are defined in a LISTDEF statement
• Create further intelligent automation with the extended IF-ELSE logic available in the JCL models

Extract Procedures and Action Procedures can both be invoked in batch and easily embedded into job scheduling systems, modern CI/CD pipelines, Ansible playbooks, and so on.

The following figure illustrates some frequently used features to define the scope of objects:

1. Extract Object Conditions define the scope of objects based on RTS conditions
2. EQF adds additional SQL filtering (for example, by excluding objects in a DBA exception table)
3. Promote to the tablespace/index level when a partition is selected based on RTS conditions
4. Promote the utility to be generated on the corresponding tablespace/partition when an index is selected

The following figure illustrates the ability to define the scope of objects with RTS thresholds or PDASTATS conditions:

1. The conditions list has been filtered to display only those related to reorganizations

2. Dozens of predefined RTS conditions are available (for example, for index page splits or an increase in the number of index levels that cause additional I/O processing)

3. You can also define your own custom conditions in SQL, based on your site-specific requirements

Simplified JCL Automation

Bid farewell to manual JCL scripting when you use Database Analyzer. Take advantage of automated JCL generation, which frees DBAs from intricate scripting tasks. This simplifies the execution of routine tasks, from backup and recovery processes to maintenance and reporting jobs, allowing DBAs to focus on more strategic initiatives.

You can leverage the following Database Analyzer capabilities to automate JCL generation:

• Flexible scripting language provides customizable predefined rules, conditions, and JCL templates
• Numerous symbolics are populated automatically using Db2 object attributes and user-defined values
• If needed, additional site-specific symbolics can be defined
• Model JCL (templates) are customizable and used to generate JCL for each selected maintenance function
• JCL generated for massive object processing eliminates the need for REXX scripts or file tailoring techniques

The following figure illustrates all the flexibility you need to define your execution options:

1. Specify the number of “action” JCL jobs (in this case "10", to control the number of parallel jobs in the job scheduler of choice)
2. Define the JCL job mask (in this case “PPREO”)
3. Specify the variable part of the job names (in this case three digits 001 to 010 at starting position 3 so that the generated job names will be “PP001REO” through “PP010REO”)
4. If fewer jobs are generated than specified, the remaining jobs will hold IEFBR14 (as defined in model member MJUTLFIL) so that the predefined jobs in your job scheduler won’t break the flow
5. Each generated JCL will be placed in its own PDS member; “Order By Utility Codes” works with LISTDEF processing and allows you to control the number of objects in each generated LISTDEF

Increased Operational Efficiency

By automating utility management and JCL processes, Database Analyzer allows organizations to execute routine tasks consistently and reliably, minimizing the potential for errors. This streamlined approach allows for a more efficient allocation of resources and accelerates overall database operations.

You can employ the following Database Analyzer capabilities to increase operational efficiency:

• Automatic object space adjustments to tune the PRIQTY and SECQTY attributes for underallocated or overallocated table and index spaces based on RTS
• Extended statistics collection offers more than twice the number of statistics available in Db2 RUNSTATS alone, using zIIP-enabled tasks (such as DASD statistics, utility execution history, capturing threshold values, etc.)
• Online query and batch reporting system is helpful for viewing the results of statistics collection in easy-to-read reports
• Sophisticated analysis of table row distribution, space, free space, index, column, frequency values, and more for graphs, trending, and forecasts
• Comprehensive reporting options provide insights into trends and help to forecast events (such as a table space that will go into another extent and reach an out-of-space condition)
• Efficient execution with zIIP enablement, concurrent object processing, multiple buffer allocations, and extended memory usage

Conclusion

Database Analyzer for Db2 for z/OS is your ticket to a more efficient and automated database management experience with effortless utility management, simplified JCL automation, and increased operational efficiency.

Automate your Db2 for z/OS utilities today and take a giant leap forward to a more efficient, reliable, and high-performance database environment. Your business deserves the best, and Broadcom delivers.

If you want to learn more, take a look at the product documentation and additional resources. Please do not hesitate to contact us directly at Denis.Tronin@broadcom.com and Steen.Rasmussen@broadcom.com.