For many years now, Db2 systems have been running more and more utilities for the critical Db2 applications or management needs of databases.
Db2 Utilities continue to increase because over the years the number of objects has increased, the volume of data has increased, the number of applications has increased both in the production environments, but also in the Test and Development environments.
The consequence of this continuing growth of db2 application sizes have implications that often find the DBAs unprepared to manage:
- Increase in duration
- Increase in consumption
- Concurrency problems with applications
- Lack of an overview
BMC AMI Utilities for Db2 is a solution designed to best address these issues thanks to exclusive patents that provide unique techniques and solid results. In particular the solution helps customers by recording all the detailed information of each individual AMI Db2 utilities performed, providing DBAs with all the information necessary to have a global overview and the capability to understand and optimize their shop by quickly answering important questions such as:
How long do Db2 utilities last and consume on average?
The trend in durability and consumption is growing ?
How many jobs are executed and by which users ?
How many abends and for which objects ?
How many restart ?
How many objects are treated ?
How many records are processed ?
How many Copy, Reorg, Load, Unload ?
Are there objects too thick or too little reorganized ?
Are there objects without backup ?
Are there objects too unloaded ?
Are there objects with sensitive data downloaded by unauthorized users ?
Being able for a DBA to have the answer to these questions means being able to have a great value for managing the best of db2 utilities. It means being able to understand how many and how the data is loaded, how and where it grows most, how much data is downloaded, better balance the executions, and monitoring the abends by identifying it among thousands of daily executions.
How long would it take to extract that information from SMF Records or from SYSCOPY utilizing IBM standard utilities?
The BIG PICTURE
BMC AMI Utilities for Db2 provides two specific tables that collects important historical information of the execution of the BMC utilities.
The CMN_BMCHIST_STEP table holds job/step information and contains columns such as job name, job ID, step number and name, utility name that was executed, utility ID, auth ID of the individual that submitted the job, start and end times as well as CPU, elapsed, GP CPU as well as zIIP CPU and zIIP Eligible. There are other columns that identify where the job was run as well as how many objects were processed.
The CMN_BMCHIST_OBJECT table provides detailed information regarding each object that was processed. Some of the details include object type, number of parts processed, start and end times, elapsed time, rows and pages processed as well as error code and reason why an object was not processed.
Every AMI utility executed writes into those History tables all the possible significant information may be helpful for any further analysis the analyst can do.
All these data allow DBAs to perform data mining for the most disparate researches producing reports as the following examples below.
BMC AMI Utilities for Db2 History Tables can be easily exported in CSV format using BMC AMI Command Center for Db2 or by any other tool you could have and then imported in an Excel spreadsheet for your personal Data Mining.
Mining the AMI historical data is possible to discover important aspects that can affect the maintenance aspects. Some examples might be:
Same Tablespaces reorganized every week
May be because for certain tablespaces triggered by the Real Time Statistics abends every week, so the reorg will always be repeated for all the following weeks until the abend will be resolved. DBAs don’t have to wait any longer the list of job abended by the schedulers or digging into SDSF outputs, they now can easily look at the Condition Code column, then solve the problem that before was difficult to discover.
Or maybe because the tablespace degrades too fast along the week, so DBA can decide to execute reorg more often before the weekend or lift Real Time Statistics trigger values.
Auditing Unloads
Is very common in several shops to run thousands of data unloads every day in Production for different purposes. Data migration, generation of dataset to share among the different applications, applications that need to process data into sequential dataset, application backups outside Db2 etc… It is important to discover if there are some users that run UNLOAD not entitled to do it, or that different applications downloads the same table more than one time a day. Despite a such complex scenario like this it is however easy extract general information inquiring AMI historical table records producing the audit information you need.
Tuning Backups
Differently from SYSIBM.SYSCOPY where the MODIFY utility eliminates the historical records, in the BMC AMI Utilities for Db2 history tables the backup records remains, therefore it is possible to analyze the distribution of image copies over a long period of time taking the right decision to adjust the backup strategy if needed.
With BMC AMI utilities for Db2, DBAs can now finally have all the information about completed executions of the BMC utilities for DB2 , analyze trends, aggregate consumptions, make decisions to change how jobs are scheduled, understand how much data are moved, if there are unauthorized utilities etc… and have create their own Db2 utilities BIG PICTURE.
