Optimizing Database Administration with IBM DB2 Autonomics for z/OS

We live in an age when data is one of an organization’s most important assets. Companies want the ability to deliver the right information to the right people at the right time. Being agile and applying predictive analytics is the key to the insights needed to grow business so that it remains at a peak competitive edge. The cost of database administration can greatly exceed the cost of the database software and hardware, so it is critical that your database administrator’s time is used effectively and efficiently.

IBM® DB2® for z/OS® helps lower the cost of managing data by automating administration, increasing storage efficiency, improving performance, and simplifying deployment of virtual appliances. By automating tasks such as memory allocation, storage management, and business policy maintenance, DB2 can perform many management tasks, freeing database administrators (DBAs) to focus on new projects. With the IBM DB2 Automation Tool for z/OS from the IBM DB2 Utilities Solution Pack for z/OS (Figure 1), you can set up recurring utility jobs for conditional and routine maintenance tasks; it helps you consume fewer system and staff resources by automating utility maintenance for objects based on your business needs.

This IBM Redbooks® Solution Guide describes how DB2 for z/OS autonomic features help lower the cost of managing data by automating administration, increasing storage efficiency, improving performance and simplifying the deployment of virtual appliances.


Figure 1. IBM DB2 Utilities Solution Pack


Did you know?

Today, more alignment exists between IT and the business side of an organization. This alignment is placing greater complexity on the IT organization. An enormous explosion of data is predicted to grow exponentially. A rapid growth in the volume, variety, and complexity of data has occurred due to the explosion of smart devices, mobile applications, cloud computing, and social media. Consider DB2 for z/OS and how it has changed to become the database of choice for big data and critical business analytics, capable of dealing with this huge amount of complex data that is currently arising.


Business value

DB2 for z/OS is able to self-regulate, including the ability to self-configure and self-optimize, and also self-protect and self-heal without human intervention. Leveraging the database with automatic functions for statistics creation, reorganization and backup is a further step to greater flexibility and intelligence. Instead of actively collecting information about the database status and manually collecting statistics, running reorganization and backup jobs, you simply let the database do the job by automating the routine collection and simple analysis of data, the obvious decisions based on this analysis and the straight-forward execution of these decisions. All you have to do is to define maintenance windows and configure your database for the use of the automatic features. As a result of using DB2 autonomic features, you receive a smart, flexible database that is running at a minimal cost level. It relieves the database administrator from running standard tasks and enables them to use their knowledge to further improve the support for the business processes. This is creating more value for less cost.


Solution overview

IBM provides several components that, when combined, can create an autonomic database environment. All these respective components cover certain aspects of autonomics, which can collaborate into one coherent solution. In our evolution of autonomics and the need to move to smarter systems, a bigger drive to the concept of “Active” versus “Passive” autonomics has occurred. With the inclusion of the IBM Management Console for IMS™ and DB2 for z/OS, and the Autonomics Director, making that transition is easier than ever by leveraging the strength of the DB2 Utilities Solution Pack for z/OS all in one standardized and centralized interface.

The Autonomics Director for DB2 function provides the framework for a comprehensive DB2 autonomics environment. With Autonomics Director for DB2, DBAs can schedule utilities to run autonomically in a maintenance window. You can define the maintenance window and the priority of the tasks to run in that window. When you build a job profile, instead of generating JCL, IBM DB2 Automation Tool for z/OS automates running of utilities against a specified set of objects, generates tasks, called actions, that are stored in a data repository. The Autonomics Director autonomically runs the actions that are in the repository when the maintenance window opens. In addition, you can specify the application objects that are most important and that are to be run first in the next maintenance window. The Management Console streamlines the enterprise wide monitoring of “Symptoms” and viewing the suggested “Actions” and allows for a phased approach toward Active Autonomics.

With IBM DB2 Autonomics, combining all these single components, these features are provided:

• Automatic Maintenance optimizes your recovery time objective (RTO) by recommending when database backups, current statistics, and maintenance tasks such as reorganizing table and index spaces need to run by exception, when required. Well-organized table and index data is critical to efficient access paths and optimal application workload performance.
• Health Monitoring proactively monitors situations or changes in your database environment that might result in performance degradation or potential outages. You can set up thresholds for warnings and alarms, and can recommend a course of action to resolve problems, helping to prevent problems before they happen.
• Self-Configuration automatically configures memory, storage, and maintenance operations in DB2 databases. This allows users to be confident that DB2 databases and instances are configured correctly, enabling better ready-to-use operation.

DB2 Autonomics is controlled by managing the various profiles that define autonomic actions and the associated maintenance windows. Figure 2 shows a maintenance window that repeats at regular times every weekday.


Figure 2. IBM Management Console: Maintenance Window


Solution architecture

IBM DB2 Autonomics consists of several components that work together to provide an autonomic database environment. It contains the IBM DB2 Utilities Solution Pack, which has the DB2 Automation Tool, the IBM Management Console for IMS and DB2 for z/OS, and the Autonomics Director (Figure 3).


Figure 3. Autonomics overview

The Management Console consolidates information from IBM IMS and IBM DB2 for z/OS systems in a single interface that is accessible from a standard web browser. It also simplifies the presentation of system health data across the enterprise, including autonomic symptoms, exceptions, and recommended actions. The application server for the Management Console can be installed on Windows or z/OS. The Management Console can then connect to z/OS through Secure Sockets Layer (SSL) TCP/IP connections.

The Autonomics Director adds a new capability to allow for a user to view the various actions the automation tool is recommending; and as the user builds trust in the given actions, these can be altered from Passive to Active and be run as needed.

With the Management Console, you can create a window that the DB2 Automation Tool can use by allowing for the creation, modification, and viewing of the various DB2 Automation Tool Profiles. These profiles describe the way utilities are built (Utility Profile), which exceptions metrics to use (Exception Profile), and how utility jobs are built (Job Profiles). The Management Console architecture consists of the following elements:

• A client machine with Internet connectivity and a supported web browser. The client connects to the Management Console server by using HTTP or HTTPS.
• The Management Console web server. It can be installed on a Windows workstation, a Windows server, or z/OS.
• A connection interface that provides access to z/OS resources, such as the DB2 distributed data facility (DDF), which is part of DB2 for z/OS. DB2 DDF provides TCP/IP access to DB2 data.

The architecture of the Management Console is shown in Figure 4.


Figure 4. Management Console architecture


Usage scenarios

Many changes have occurred in the past 25 years in our IT world that have led to the need for autonomics in our environment. We look at a hypothetical company, Fictional Company H, and the changes that it has experienced, to illustrate some of the problems that have arisen based on the changes in business and IT that are challenges faced by all of us right now.

Company H had a very successful growth period during 1990-2000. This was a dynamic time for the business and IT with lots of new DB2 application development; that development helped support growth for the company in revenue and new business opportunities. The IT department expanded with new DBAs, application teams implemented many new applications in DB2 and there was significant growth in the knowledge required to develop and implement new DB2 applications in production. During the years 2000-2008, there was a slowdown in that initial burst of new DB2 application development but the existing applications continued to be enhanced with new features and capabilities. After the financial crisis in 2008, there was a change in IT where costs were scrutinized more closely and budgets were flat or reduced and there was a loss of expertise due to restructuring and retirement of skilled workforce.

These changes increased the complexity for some of the DB2 staff at Company H. The production batch application and utility jobs that were created in the 1990s run DB2 utilities unconditionally. The expertise that was around when these applications were initially developed is no longer there. The DBA team believes that DB2 tables are being reorganized but do not need to be, and no one has the time to analyze this to prove that this is the case. Also, some of the new DB2 tables that were added during 2000-2008 are not being reorganized (REORG) as frequently as some of the original DB2 tables and the DBA team is concerned that some of them should be reorganized more frequently. The DBA manager is asking whether some of the workload (such as RUNSTATS) can be moved out of the peak CPU period in order to help other batch jobs complete faster and to reduce costs.

Finally, during the 1990s, Company H developed a “homegrown” DB2 utility and control card generator that have not kept up with changes to some of the utilities; the support person who developed this homegrown solution is planning to retire soon but no one else knows how to maintain it.

Company H faces some real-world challenges that are common to many companies. DB2 autonomics can address the problems that Company H and other companies face. Here are examples of these common challenges and how autonomics can help solve them:

• DB2 batch utilities are run unconditionally without intelligence. DB2 Automation Tool can help apply intelligence to the running of DB2 utilities. This covers both the situation where DB2 tables might be reorganized too often or not often enough.
• Some utilities (such as RUNSTATS) are being run during peak CPU workloads, slowing the execution of the production batch cycle and increasing costs. By having DB2 Real-Time Statistics (RTS) externalized to a history table using the function provided with the Management Console can help save CPU costs (versus running expensive RUNSTATS).
• DB2 Utility leading practices and standards were first developed in the 1990s and have not kept up with the changes introduced in the past few releases of DB2 tools, and Company H expert on the homegrown utility generator is retiring soon. DB2 Utilities Enhancement Tool (UET) can help implement and enforce best practices for utility jobs so the company does not have to rely on one DBA for support of the homegrown tool.

DB2 for z/OS has evolved over several releases to provide more information on the health of the objects. RUNSTATS and real-time statistics can provide invaluable information that can be mined to help determine the need for utility maintenance. In addition, IBM DB2 tools permit users to set up utilities based upon predefined conditions that can trigger what maintenance is needed and on which objects. This trigger is based on which criteria has been met or exceeded.

The size of the DB2 for z/OS environment has grown exponentially and so has the size of the DB2 utility maintenance window. During this time, more competition exists to share the batch window and also the cycles. Also, more demands exist for accessibility and availability of business-critical data. The DBA must know when the best time is to perform utility maintenance and negotiate with the systems programmer to share the window and resources available.

All of these factors help to drive the need for applying autonomics when it comes to DB2 utilities. The ideal setting might be to allow the DBA to set up the environment and then allow the “autonomics” to determine when, where, and how maintenance is applied.


Supported platforms

To determine if your hardware or other key prerequisites are supported by IBM DB2 Autonomics, see the Program Directory for DB2 11 for z/OS:
http://www.ibm.com/support/docview.wss?uid=swg27039165#db2-pd


Ordering information

This product is available only through IBM Passport Advantage®. Detailed ordering information is available in the IBM announcement letters (see the “Related information” section). Ordering information is shown in Table 1.

Table 1. Ordering part numbers and feature codes

Program name	PID number
DB2 11 for z/OS	5615-DB2
IBM DB2 Utilities Solution Pack for z/OS 2.2.0	5697-DUP

Related information

For more information, see the following documents:

• IBM Offering Information page (to search on announcement letters, sales manuals, or both):
  http://www.ibm.com/common/ssi/index.wss?request_locale=en

  On this page, enter DB2 for z/OS, select the information type, and then click Search. On the next page, narrow your search results by geography and language.
  

• Product page: DB2 for z/OS
  http://www.ibm.com/software/data/db2/zos/family/

• Product page: DB2 Utilities Solution Pack for z/OS
  http://www.ibm.com/software/products/en/db2-utilities-solution-pack-zos

• IBM Announcement Letter: DB2 11 for z/OS
  http://ibm.co/1Gp4q4O

• IBM Knowledge Center: DB2 Automation Tool
  http://www.ibm.com/support/knowledgecenter/SSAUWB_4.2.0/topics/haahome.html

• IBM Knowledge Center: Autonomics Director
  http://www.ibm.com/support/knowledgecenter/SSS8US_1.5.0/dyx/dyx_landing.dita