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 functionDB2 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.