IBM DB2 - The Ultimate Database for Cloud, Analytics & Mobile
This blog entry describes the journey that we recently took at the Pennsylvania Department of Transportation (PennDOT) to modernize our DB2 utility environment. Like all journeys, there were twists and turns along the way. But we knew that we had to find a better way of running our DB2 object maintenance utilities that would replace our existing inefficient process.
DB2 environments are increasing in size and complexity, and are placing greater demands on efficiency and true 24x7 processing. This places greater demands on IT, especially database administrators, who are faced with the challenges of managing these environments. This was true at PennDOT, and so to achieve this greater efficiency we wanted to re-examine our DB2 object maintenance utilities to see if we could modernize our approach.
We wanted to apply “best practices” using available technology to reduce our weekend DB2 utility maintenance window, which had grown to more than 9.5 hours every month. There was also a focus on reducing the CPU consumption of our DB2 utility jobs. We realized that many of the DB2 maintenance utility jobs that we were executing every month had been set up years, if not decades, ago and had never been updated to reflect the current application environment/workload.
One of the biggest obstacles we faced at PennDOT was moving from a trusted and stable (albeit very inefficient) DB2 object maintenance process that relied on the expertise of our DBAs, to a system-architected, automatic, and policy-driven process that was perform by DB2 Tooling software. New software and learning curves are not necessarily readily embraced, and we were moving into unfamiliar territory.
We decided to install IBM’s new Data Server Manager along with the IBM DB2 Utilities Solution Pack, and start down the road of implementing DB2 Autonomics. These were new products and components for us to learn, new processes to create and adopt, and new interfaces to navigate.
The next challenge that we faced as a team, was to determine what objects we wanted to monitor for maintenance and come up with a plan for what exception criteria, i.e. policies, to use that would trigger the maintenance of these objects. Since we had not thought about this for years, it also gave us the chance to re-examine and re-prioritize our maintenance process based on our current business needs with a focus on our critical applications.
The DB2 autonomics solution provided by the combination of Data Server Manager and the IBM DB2 Utilities Solution Pack has two basic modes of operation: passive and active. Starting with passive mode autonomics, we implemented a process to capture and save real-time statistics to build an historical record of object statistics. We then created the necessary object, utility, exception, and job profiles required by autonomics in the DB2 Automation Tool. These profiles all work together to determine what actions to take on what database objects using the triggering criteria we specified.
The IBM DB2 Utilities Solution Pack also enabled the ability to automatically capture the execution data of all our utilities jobs and record it to a Utility History Table.
Active autonomics, then, takes it to the next level. This mode allows the system to automatically take the necessary corrective actions as determined by the triggering exception criteria that we specified. The system monitors and analyzes the specified metrics to proactively make recommended actions. We defined a DB2 maintenance window during which the recommended maintenance actions can execute. These actions are subsequently executed by another component of the DB2 autonomics solution called the Autonomics Director for DB2 during this pre-defined maintenance window. The actual scheduling of all recommended actions can be accomplished using either the DB2 Administrative Task Scheduler, or an external scheduler of your choice, such as TWS.
One of the challenges in moving from passive to active mode autonomics is the element of trust. We needed to be able to trust that our DB2 autonomics solution was correctly choosing the right objects for maintenance – the same objects that the DBA would have previously chosen manually. This can be quite a leap of faith, but once we saw for ourselves that the autonomic object selection and action triggering process does indeed work as advertised, we were hooked. By freeing our DBAs from having to continually perform this maintenance task every week, they now have more time to focus on other higher value DBA tasks.
Progress and results
Since implementing DB2 autonomics a few months ago, we have been actively monitoring our new DB2 object maintenance process, and the results we are seeing are very impressive. I’d like to invite you to hear more about how to set up and implement a DB2 autonomics solution, and, most importantly, the results that we have been able to achieve at PennDOT. Please join me on June 20th at 11:00 AM EDT for an IBM webcast titled “How DB2 Autonomics is Helping PennDOT Innovate” along with a live Q&A session where I’ll cover all of this and more.
Please use this URL to register: http://bit.ly/2mWdeSY
Perry Shindle is a DB2 for z/OS Consultant currently providing DB2 support services to the Pennsylvania Dept. of Transportation (PennDOT), and has been working with DB2 for more than 30 years. Perry has been a IBM Systems Engineer supporting DB2 customers in the field, an application DBA on several large, multi-year custom application development projects, as well as a systems DBA supporting, installing and migrating both stand-alone DB2 subsystems, DB2 data sharing groups, and several IBM DB2 Tools. Perry has also been one of the team members that developed the DB2 for z/OS DBA and System Administration certification tests for several recent versions of DB2 for z/OS.