Streamlining Operations of IBM DB2 Utilities with IBM DB2 Utilities Solution Pack for z/OS
IBM Redbooks Solution Guide
Published 19 September 2013
Authors: Tom Crocker, Paolo Bruni
The IBM® DB2® for z/OS® utilities and utility management tools are the most comprehensive on the market. They are unique in that they provide critical solutions to today’s most complex business problems across many functional areas to deliver the highest ROI from a customer’s software investment.
The DB2 Utilities Solution Pack gives customers the ability to manage their DB2 Utility processing and manage their data growth challenges. The DB2 utilities and DB2 Utilities Solution Pack provide immediate support for new releases of DB2 for z/OS and allow customers to accelerate their time to value for migration. This support allows customers to use the new features with confidence, and within a shorter timescale and with fewer errors.
The IBM® DB2® for z/OS® utilities and utility management tools are the most comprehensive on the market. They are unique in that they provide critical solutions to today’s most complex business problems across many functional areas to deliver the highest ROI.
The DB2 Utilities Solution Pack gives customers the ability to manage their DB2 Utility processing and manage their data growth challenges. The DB2 Utilities and DB2 Utilities Solution Pack provide immediate support for new releases of DB2 for z/OS and allow customers to accelerate their time to value for migration. This support allows customers to use the new features with confidence, and within a shorter timescale and with fewer errors.
In today’s IT environments, data growth remains one of the biggest challenges facing businesses, along with scalability, performance, and management, with all of these challenges needing to be achieved within tight cost constraints. Coupled with these challenges, data management must drive competitive advantage, business continuity, and availability while meeting strategic business plans.
Critical to these requirements is maintaining and improving service level agreements (SLAs) and user satisfaction, all at a reasonable cost.
DB2 tools assist customers in managing these challenges. There are four specific areas that IBM helps customers with managing data growth on DB2 for z/OS, as shown in Figure 1:
- Data Access and availability
- Performance and TCO
- Automation and standardization
- Continuity and resilience
Figure 1. Managing data growth
Did you know?
By investing in these areas, DB2 tools enable customers to manage their growth in data while maintaining the performance and resilience of their applications and ensuring that their businesses can make full use of all their data without a degradation in service.
In the DB2 environment, customers are looking at integrated solutions, where specific related requirements can be addressed with a “single” solution, making it easier for customers to purchase and use the tools, and to help them address all these challenges. With this in mind, IBM constructed four solution packs that contain related products to help address customers business needs. These solution packs give customers a complete solution in each of the areas addressed.
Here are the four solutions:
- DB2 Administration Solution Pack
- DB2 Fast Copy Solution Pack
- DB2 Performance Solution Pack
- DB2 Utilities Solution Pack
All the Solution Packs address specific business requirements, ranging from modernization to managing change in the business and applications, as shown in Figure 2. The common strategy of the Solution Packs, and the individual tools, is to maximize the ROI from resources that are required by DB2, ranging from TCO to staff efficiency, without impacting the business applications. By addressing these issues, the Solution Packs allow your businesses to explore getting more insight out of its data by using advanced analytics techniques and to potentially identify further opportunities for the business.
Figure 2. The business requirements that are addressed by Solution Packs
This IBM Redbooks® Solution Guide describes the DB2 Utilities Solution Pack and how it can benefit IT environments by streamlining DB2 Utilities operations.
Underlying the operation of any database management system are the utilities. With the number of database objects growing exponentially, the effect of managing utility jobs, meeting service level agreements, and ensuring recoverability can be overwhelming. As the data grows and either changes in, or new business applications, then the pressure on the management of utilities grows. This pressure can reduce your ability to run utilities with additional effects on performance, complexity, and cost. See Figure 3.
Figure 3. The DB2 Utilities Solution Packs
The management of utilities becomes more critical as the volume and required availability of data grows. IBM DB2 Utilities Solution Pack can help you achieve higher availability and better performance by optimizing the DB2 Utilities performance and increasing productivity of operations for both database administrator and system programmers. The DB2 Utilities Solution Pack combines several products that assist in the DB2 Utilities management process, providing everything that you need to manage, optimize, control, and automate data, while reducing the operational complexity and costs.
As shown in Figure 4, the tools within the DB2 Utilities Solution Pack help with the following tasks:
- Manage: Build processes to optimize data maintenance tasks.
- Optimize: Minimize utility resource consumption and reduce utility execution time.
- Control: Enforce preferred practices and optimum utility parameters, and affect decisions by setting return codes.
- Automate: Run DB2 Utilities on demand and in a timely manner by creating thresholds to trigger utilities when they are required, and by using the optimum utility characteristics to match the selected objects.
Figure 4. Automating DB2 data maintenance
To achieve the efficient and timely management of DB2 Utilities, use the following DB2 tools of the DB2 Utilities Solution Pack:
- IBM DB2 Sort for z/OS
- IBM DB2 High Performance Unload for z/OS
- IBM DB2 Utility Enhancement Tool for z/OS
- IBM DB2 Automation Tool for z/OS
Features and benefits of DB2 Sort for z/OS
DB2 Sort provides high speed utility sort processing for data that is stored within DB2 for z/OS. It improves sort performance while optimizing overall system efficiency by using the advanced facilities of IBM System z® and the z/OS features and speciality engines where available.
DB2 Sort delivers significant savings in elapsed time and processor time during utility sort processing of the following utilities:
- REBUILD INDEX
During utility execution, DB2 Sort monitors and adjusts the allocation of system resources to optimize processing, I/O performance, and memory usage, and provides zIIP redirection of utility sort processing. By choosing the most efficient usage of resources for each sort, DB2 Sort can increase the number of simultaneous sorts that can be run, thus lowering the utility without necessarily increasing other resource usage.
Figure 5 shows the performance benefits that you can achieve.
Figure 5. A scenario of DB2 Sort improvements
Features and benefits of DB2 High Performance Unload for z/OS
DB2 High Performance Unload (DB2 HPU) provides a fast and efficient method of extracting large amounts of data across the enterprise. DB2 HPU can run outside of DB2 if necessary, and therefore does not compete for the same DB2 resources. It typically maximizes performance by reducing processor usability and increases availability.
DB2 HPU supports the following features:
- Unloading from table space or image copies
- Full SQL SELECT statements
- Sampling (unload every nth row)
- Generating LOAD utility control cards
- Multiple unload formats
- Running outside of DB2 control
DB2 HPU integrates with DB2 Automation Tool and can call DB2 Sort when required. It can be used by DB2 Administration Tool for z/OS when unloading data.
Features and benefits of DB2 Utility Enhancement Tool for z/OS
DB2 Utility Enhancement Tool for z/OS (UET) extends the functionality of the DB2 utilities and enables the control of DB2 utilities to fit business needs. It provides superior control of DB2 threads for both local and remote systems. By providing these features, UET enables customers to have greater control over how their utilities perform and to control the availability of the data during utility processing.
UET provides a syntax checker, and reporter, to ensure that preferred practices are used by the utilities, and to determine which utilities are being run by which user and from which jobs. This allows changes to any parameters to match preferred practices, enforces customer standards, and stops utilities. For example, customers can ensure that the KEEPDICTIONARY parameter is always present in all DB2 Reorgs or that all LOAD utilities run LOG NO.
UET extends the functionality of the following utilities:
- REORG TABLESPACE
- CHECK DATA
Features and benefits of DB2 Automation Tool for z/OS
The DB2 Automation Tool (AUT) allows you to set up recurring utility jobs for conditional and routine DB2 maintenance tasks. AUT automates common utility tasks and allows customers to define criteria to determine if and when utility maintenance should occur, saving valuable system and staff resources.
AUT can act against over 180+ exceptions to ensure that housekeeping is run only when required and can identify any potential problems before they become critical. These exceptions can be based upon various sources, including real-time statistics, an IBM MVS™ catalog, and the DB2 Catalog. Acting on exceptions increases data availability and ensures that performance does not degrade and become an issue to users by undertaking only the housekeeping that is critical to meet the business requirements. For example, an object may be being REORGed weekly as part of a housekeeping suite whether it requires it or not. By using AUT exceptions, this object is REORGed only when its disorganization starts to affect the business application. This increases data availability and stops the unnecessary consumption of resources.
To use AUT, define the following profiles:
- Object Profile: Defines the objects that you want to act upon.
- Utility Profile: Defines utility parameters.
- Exception Profile: Defines triggers for the housekeeping of objects.
- Job Profile: Combines all the other profiles and set job parameters.
The relationship between the profiles is shown in Figure 6.
Figure 6. Combining profiles for utility execution
After the Job Profile is defined, you can run it to generate all the housekeeping that is selected by the Object, Utility, and Exception profiles. This simple process of defining and running the housekeeping improves the productivity of the DBA staff, as they do not need to maintain housekeeping JCLs and can take advantage of new utility features as they become available. In addition, AUT can be used by new employees, who become productive within a short period.
AUT integrates with the following DB2 features:
- Autonomic statistics stored procedures
- Creating statistics profiles
- Defining maintenance windows for the RUNSTATS utility
- Viewing results
- DB2 administrative task scheduler
- Automatically running exception jobs at user define intervals
- Automating execution of utility jobs to avoid manual intervention
With the Solution Pack, customers can manage their utility environment to ensure that they get the greatest business benefit possible. Here are three possible scenarios that show how you can use the tools within the Solution Pack to achieve resource savings and enforce standards:
- Fitting REORG into a small batch window
- The Automation Tool identifies the objects requiring REORG (and ignores those objects that do not require REORG).
- REORG invokes DB2 Sort to reduce processor and Elapsed Time.
- The Utilities Enhancement Tool ensures that the REORG utility completes by setting the FORCE parameter.
- Improving data unload / load operation
- High Performance Unload takes advantage of performance options to minimize processor consumption.
- Utility Enhancement Tool uses the LOAD PRESORT option to invoke DB2 Sort to sort data before LOAD and reduces processor and elapsed time while increasing availability.
- Establishing standards to fit business needs
- The Automation Tool ensures that only those objects requiring housekeeping are acted upon.
- The Utilities Enhancement Tool uses user-defined policies to enforce corporate standards and ensure that preferred practices are implemented.
- The Utility Enhancement Tool provides a reporting Utility Monitor function for utilities execution, auditing purposes, or general trend analysis.
The DB2 for z/OS Utilities and utility management tools are the most comprehensive on the market. They are unique in that they provide critical solutions to today’s most complex business problems across many functional areas to deliver the highest ROI.
The DB2 Utilities Solution Pack gives customers that ability to manage their DB2 Utility processing and manage their data growth challenges.
The DB2 Utilities and DB2 Utilities Solution Pack provide immediate support for new releases of DB2 for z/OS and allow customers to accelerate their time to value for migration and use new features. The tools provide support for the new release of DB2 for z/OS. This support allows customers to use the new features with confidence, and within a shorter timescale and with fewer errors.
DB2 Utilities Solution Pack runs on DB2 for z/OS environments with the DB2 Utilities Suite for z/OS installed, and complies with their prerequisites in terms of System z resources.
You can request a quote for IBM DB2 Utilities Solution Pack for z/OS V1.1 (program number: 5697-DUM) at the following website:
Alternatively, you can call 1-800-966-9875, and use priority code 109HF03W.
You can find technical details about the Solution Pack by consulting the following resources:
- Managing DB2 for z/OS Utilities with DB2 Tools Solution Packs, SG24-8046
- Solution Pack website:
- IBM Offering Information page (to search on announcement letters, sales manuals, or both):
- On this page, enter IBM DB2 Utilities Solution Pack for z/OS, select the information type, and then click Search. On the next page, narrow your search results by geography and language.
Others who read this publication also read
This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.
Follow IBM Redbooks
Follow IBM Redbooks