Self-managing DB2 databases
The concept of automating routine database tasks is not new. It has been theorized, discussed and written about for several decades. But, true self-managing or autonomic capabilities have been slow to be developed and adopted and put into practice use. There are factors helping to drive the need for a solution. We discuss some of those distinct pain points and how IBM has helped to solve them leading to innovation for the organization and time savings for the IT staff.
What’s are the driving factors?
We live in the age where data is one of an organization’s most important assets. Companies want the ability to deliver the right information at the right time to the right people. Being agile and applying predictive analytics is key to the insights needed to grow business so that it remains at a peak competitive edge.
Today, there is more alignment between IT and the business side of an organization. This is placing greater complexity on IT organization. There is an enormous explosion of data that is only predicted to grow exponentially. There has been a rapid growth in the volume and complexity of data due to the explosion of smart devices, mobile applications, cloud computing, and social media. Consider DB2 12 for z/OS and how it has improved business insights with up to 100x faster execution of highly concurrent queries, faster mobile support via the RESTful API and greater enterprise scalability and availability with 11.7 million inserts per second, 256 trillion rows per table. And all of this with reduced costs to the business. The constant change in the type of applications and growth of data is also met with the ongoing demands for more traditional administrative tasks, such as executing IBM DB2 Utilities that manage the data stored in DB2 for z/OS. What has changed is that the increased demands placed on those utilities tasks that can impose more demands to IT and over-burdened IT staff.
Learn more about DB2 12 for z/OS: https://ibm.co/2plDxi1
IBM DB2 12 Utilities for z/OS
DB2 12 for z/OS was made generally available on October 25, 2016. The IBM DB2 Utilities were there every step of the way providing complete, unquestionable support, even before the start of the Early Support Program (ESP). But, the IBM DB2 Utilities do not stop with DB2 support. They are constantly evolving and changing to support customer’s demands for availability and performance.
More and more customers are demanding true 24x7 availability for their business applications and by implication, for their business data. This affects how application and schema changes are implemented and of course, it affects how utilities are run. With respect to application and schema changes, IBM has put a stake in the ground and resolved that all application schema changes must be possible to run online, i.e. without application impact. This just doesn’t mean when DB2 implements new DDL capability, it means IBM wants to re-visit existing ALTERs and modify them where they have application impact today. A good example is some column level ALTERs that caused indexes upon that column to be placed in REORG PENDING today. And in DB2 12, IBM utilities have made enhancements in that area to resolve that issue.
The IBM REORG utility is increasingly the vehicle to make those schema conversions without impacting application availability. Changes that use to require an UNLOAD, DROP, RECREATE and RELOAD of the data are now made possible thru ONLINE REORG, reducing the application impact from potentially hours to mere seconds. Using the IBM REORG utility for this purpose is a path that IBM has been on for several of the last DB2 releases, and good examples include:
- conversion to reordered row format
- universal table spaces
- extended format page sets
- DB2 12 relative page numbering
- enablement of inline LOBS
- online alter of limit keys for repartitioning of data
- online change of DSSIZE
Learn more about IBM DB2 Utilities at IDUG:
- Session B08 – What’s new in utilities for DB2 12 for z/OS
Ka-Chung NG, IBM Tuesday, May 2nd 2:10 PM – 3:10 PM
North Ballroom
- Session G16 – DB2 Online Schema Changes: IBM What’s New in DB2 for z/OS V11 and V12
John Lyle, IBM Thursday, May 4th 10:30 AM – 11:30 AM
North Ballroom
Taking control over utility execution
DB2 12 delivered major improvements in statistics management, enabling the use of statistics profiles to help improve and simplify DB2 self-management. IBM removed the restrictions on using statistics profiles by now allowing their use in inline statistics in version 12 of DB2. In DB2 12, DB2 updates the statistics profile directly. This procedure includes not missing statistics that are required by the DB2 optimizer. It also includes support for DDL changes, such as dropping or renaming an index, dropping or renaming a column, or renaming a table. DB2 12 now automatically updates the relevant statistics for the profile for all relevant actions. Furthermore, clean-up is improved with an automatic removal of unneeded distribution statistics when using the DB2 12 RUNSTATS USE PROFILE option.
When a profile has been updated, it can be detected by the IBM DB2 Automation Tool for z/OS, which helps provide greater DB2 self-management capabilities. The Automation Tool can generate the necessary RUNSTATS job to gather the correct statistics that the DB2 optimizer needs. This DB2 12 statistics enhancement fits the infrastructure delivered in the IBM tools portfolio to move from simple automation of tasks to DB2 self-management. Some complex decisions are made to eliminate unnecessary overhead and enable the running of DB2 systems to be more efficient and minimize database administrator (DBA) involvement.
Creating a strategy that automates collecting routine statistics means that maintenance can be utilized for executing priority utilities. For example, performing a REORG during maintenance may be more important than statistics collection when it comes to ensuring business-critical application data remains available. As a result, the RUNSTATS utility might often take a back seat to other utilities, such as COPY or REORG.
But if the data in your business-critical objects varies significantly over time, then keeping statistics current helps DB2 confirm that the most efficient access path is being used to access application data. On the other hand, chronically checking a busy object’s statistics for updates can be time consuming and can take a DBA away from other job responsibilities. Hear how a customer using the IBM DB2 Automation Tool transformed their REORG process.
Learn how to control your REORGs at IDUG:
- Session A09 – Revamp the Tablespace REORG process HCA with the IBM DB2 Automation Tool for z/OS
Bharath Nunepalli, Wednesday, May 3nd 8:00 AM – 9:00 AM
South Ballroom
Too much work, not enough staff
One of the challenges to adopting autonomic computing is the element of change. There are many adages relating to the concept of change. For an IT organization, change is usually driven by business needs and imposed upon IT to come up with a solution that supports the goals and needs of the business. Companies are being asked to do more with less. There are fewer experienced database administrators (DBA) and those that remain, are being tasked with managing more than the traditional role of the DBA to support changing IT needs.
Professionals who manage a DB2 for z/OS environment have some common concerns:
- Demands for 24x7 high performance operation continue to increase
- Allowed outage windows are shrinking and are less frequent
- Demands for non-disruptive object maintenance, keeping CPU cycles out of peak processing periods
- Increasing system complexity makes planning, maintaining, and troubleshooting more difficult and time consuming
IBM is addressing these challenges in several ways. IBM Data Server Manager (DSM) helps administer, monitor, manage and optimize the performance of IBM data management platforms across the enterprise. It provides DBAs and other IT staff with the information they need to manage performance proactively and helps minimize the impact these issues have on your business. DSM is designed to provide a single, simplified interface to manage DB2 systems and databases.
Find out more about Database Server Manager for z/OS:
The IBM Solution
The IBM Utilities Solution Pack extends the value of the IBM DB2 Utilities Suite with additional CPU and elapsed time saving and more flexible options to manage utilities to fit your business needs. The Solution takes advantage of DSM to consolidate and simplify information from various sources. It simplifies the presentation of complex information and shortens the learning curve for staff. Smarter infrastructure automates the collection and analysis of data and automates decisions based on the analysis of data. The shared infrastructure enables IBM to better integrate and take advantage of IBM tools and DB2 engine functions. The following is a screen shot of Data Server Manager as integrated in the IBM DB2 Utilities Solution Pack:
Data Server Manager simplifies the presentation of system health data across the enterprise—including autonomic symptoms and recommended actions. It also provides the following features and benefits:
- A unified interface for viewing and managing the overall health of DB2 for z/OS systems across the enterprise.
- The ability to navigate and drill down from the enterprise or system level to individual objects. You can view, understand, and access identified DB2 for z/OS symptoms and recommended actions.
- Resource dashboards that consolidate information from various sources and tools with data sharing groups as well as individual table space and index space partitions.
- Display resources with the highest severity symptoms and actions so you can navigate directly to those resources.
- Embedded help that includes details and concepts. Helps to reduce the learning curve and enable new system programmers and database administrators to be up and running faster.
Learn more about self-managing IBM DB2 utilities:
- VSP – How self-managing DB2 for z/OS technology gives you more control
Hendrik Mynhart IBM Tuesday, May 2nd 1:00 PM – 2:00 PM
Castle A&B
- How DB2 autonomics helped PennDOT innovate
Perry Shindle, PennDOT Tuesday, June 20th 11:00 AM – 12:00 PM
IBM Webcast Register at: http://bit.ly/2mWdeSY
#hot-topics