IBM Utilities vs. Third Party Tools for DB2

The DBAs Delima

In today's market, deciding which utility to use can be a major decision for the DBA. Some questions that I hear quite frequently are:

• "Could you please, share some experience with third party query analysis tools for DB2?"
• "What is the best, IBM or third party tools for DB2 database administration"
• "I am looking for tools allowing accurately find unique sql statements with high sort, CPU, or elapsed time cost"
• "I need something that can help to understand tuning with DB2 UDB."

There is no simple answer for all of these questions. In order to make the DBA's job quicker, more professional and fully documented, many database administrators are using third party solutions. At the same time, IBM is working hard to improve their own tools.

This article covers:

• General Database Tool Expectations
• IBM's Offering to the DBA
• Overview of Third Party Database Tools for DBA
• DBArtisan - Tool for Production DBA
• Quest Central, Performance Diagnostic Module -Tool for Hybrid DBA
• SQLExpert -Tool for Development DBA
• Conclusion

General Database Tool Expectations

All database tools cover regular database administration. Additionally, some of them are specialized in certain areas such as SQL tuning, backup and recovery or online monitoring and reporting.

We can expect some or all, of the following elements from one good DBA tool:

• Easy installation - very easy and short installation procedure
• Regular administration - tool has to support all common database management tasks such as creating/modifying/deleting schema objects, DBA script execution facility, export, import, tablespace and schema objects storage administration, space calculation for every particular object, index maintenance, object analyze and rebuild
• Security Administration - users, roles, and object permissions administration
• Performance Monitoring & Tuning - graphical real-time database and instance monitoring, with advanced tuning and storage advisors
• SQL Execution - integrated SQL engine which can execute and generate comprehensive execution plan for the statement
• Multiple Transparent Database support - unified console for universal database administration (Oracle, DB2, Sybase, MS SQL Server)
• Syntax learning - syntax engine are creating command syntax for any chosen action
• Reverse Engineering -possibility for reverse generation of DDL for all database objects
• Reporting Support - Web database reports generation in HTML or PDF format
• Data Editor - integrated, GUI database table editor

One area of special interest is Performance Monitoring. We should pay very close attention to how well a tool covers the following:

• system utilization monitoring
• database manager agents activity
• sorts (sortheap usage, sorting activity, overflow count)
• hash joins (small overflows, big overflows),
• bufferpool activity (data and index reads/writes)
• package cache statistics, catalog cache statistics
• log space
• application sections
• database manager performance metrics
• average response time and locking activity (timeouts, deadlocks, share escalations, exclusive escalations, lock waits, lock list activity).

IBM's Offering to the DBA

IBM has a considerable amount of competition in the field of the database tools. Competitors are offering extended, more functional tools than IBM. Regularly, a great number of IBM developed utilities and tools are selling as a part of the database product. In addition, there is separate tool set, developed by IBM, called DB2 Multiplatform Tools, which targets performance management, availability and data integrity of the DB2 Universal Database running on Linux (Intel32), UNIX, and Windows platform. This new tool set is part of new IBM's Self Managing and Resource Tuning (SMART) program. Multiplatform tools are shipping as a part DB2 UDB Enterprise Server Edition, Version 8 under Try-and-Buy license.

DB2 Multiplatform Tools components:

• DB2 High Performance Unload V2.1 - quickly unload and extract data from UDB database
• DB2 Performance Expert V1.1 - integration monitoring, reporting, buffer pool analysis and performance warehouse function DB2 Recovery Expert V1.1 - manage targeted, flexible and automated recovery
• DB2 Table Editor V4.3 - enable table data update and delete across multiple DB2 platforms
• DB2 Web Query Tool V1.3 - secure web based access tool

IBM is always one-step ahead, introducing new database features on the market, leaving other to follow and include the latest DB2 functionality. DB2 is still far from a true self-tuning nature, and the DBA still needs to configure and tune the remaining non-auto parameters as well as optimize buffer pool sizes and configurations. Many of the third party offerings still provide more functionality than IBM, but the IBM tools are still cheaper than third party tools.