In order to make the DBA's job quicker and more professional, many database
administrators are using third party solutions. At the same time,
IBM is working hard to improve their own tools. Marin Komadina lines them up,
giving us the pros and cons to each.
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:
you please, share some experience with third party query analysis
tools for DB2?"
is the best, IBM or third party tools for DB2 database administration"
am looking for tools allowing accurately find unique sql statements with high
sort, CPU, or elapsed time cost"
need something that can help to understand tuning with DB2 UDB."
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.
Database Tool Expectations
IBM's Offering to the
Third Party Database Tools for DBA
Tool for Production DBA
Performance Diagnostic Module -Tool for Hybrid DBA
-Tool for Development DBA
General Database Tool Expectations
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:
- very easy and
short installation procedure
- 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
- users, roles,
and object permissions administration
Performance Monitoring & Tuning - graphical
real-time database and instance monitoring, with advanced tuning and storage
integrated SQL engine which can execute and generate comprehensive execution
plan for the statement
Transparent Database support - unified console for universal database administration (Oracle, DB2, Sybase, MS SQL Server)
learning - syntax engine are creating
command syntax for any chosen action
Reverse Engineering -possibility for reverse generation of DDL
for all database objects
Support - Web
database reports generation in HTML or PDF format
integrated, GUI database table editor
of special interest is Performance Monitoring. We should pay very
close attention to how well a tool covers the following:
manager agents activity
usage, sorting activity, overflow count)
(small overflows, big overflows),
activity (data and index reads/writes)
statistics, catalog cache statistics
manager performance metrics
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:
High Performance Unload V2.1 - quickly unload and extract data from UDB
DB2 Performance Expert
V1.1 - integration
monitoring, reporting, buffer pool analysis and performance warehouse function
Expert V1.1 - manage targeted, flexible and automated recovery
Table Editor V4.3 - enable table data update and delete across multiple DB2
Web Query Tool V1.3 - secure web based access tool
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.
Overview of Third Party
DB2 Performance Management Tools
Many different tools for DB2 administrators,
developers and support personnel exist on the market. All of the tools have graphical
intuitive interfaces, with lots of charts, graphs or colored bars that give a
DBA an overview of what is really happening inside or around the database. Some
of companies that are more prominent along with their competitive products are:
Inc., DBArtisan v7.1
Quest Software, Quest Central for DB2 v126.96.36.1992
BMC,DBXray v2.0.01, PATROL v6.4.0, SmartDBA Cockpit v1.6.01, Space Expert v1.1.00,
Load and Unicenter Fast Recovery for DB2
LECCO, SQL Expert Pro v2.6.1 for DB2 UDB
Software, Precise/Indepth for IBM DB2 v2.0
DGI, Flight Deck v1.4
General characteristics of
the third party tools:
system statistics - collecting and displaying an operating systems statistics
for multiple nodes and instances
management - creating and altering database objects, extracting DLL for objects
and their dependents, generating schema reports, copying objects between
management - schema, users and grants administration
monitoring - key database components monitoring with real-time diagnostic and
management - space utilization monitoring
management - data editing, between instances exchanging, query and script execution
support - one consistent interface for Oracle, Microsoft SQL Server, Sybase and DB2 Universal
Database and in special cases for Interbase, MS Access (95/97/2000), Paradox, dBase,
Informix, SQL-Anywhere, MySQL, PostgreSQL
interface - web interface for database navigation and administration
Collecting - server based agents, running in the background for collecting
statistics information and writing collected data inside special database
Some tools are highly specialized in some areas while others are
more generalized, universal tools. For example, for faster data loading we can
use Unicenter Fast
Load for DB2, which can load large amounts of data into DB2 tables very quickly,
while the standard IBM load utility is slow and limited. For speed recovery, the
DBA can use Unicenter
Fast Recovery for DB2, when is necessary to quickly recover the DB2 database data.
However, my favorites are DBArtisan, which is a great tool for regular and advance database
administration, Quest Central, Performance Diagnostic Module with
intuitive graphical database interface and SQLExpert Pro with special tailored
SQL rewrite engine.
DBArtisan - Tool for Production DBA
is an often-used tool for regular a DBA job, where a novice or junior DBA can
act as a master on a production system. He can even be
faster than the master DBA, who is used to working on a terminal
window controlling the DB2 database with the CLP command window. However, this doesn't mean that
we do not need a master.
operations, like the create, drop or alter
tables, constraints, indexes, views, users, procedures, functions, triggers,
groups, roles, profiles, and packages are all there, just one mouse click away.
You will love DBArtisan because it can help in extracting a database object
definition, migration, and all of the other boring and constant, tedious work.
One DBArtisan feature, which is on the
top of my list, is the ability to manage multiple databases from the single
In this example, we have DB2, Oracle
and MS SQL databases just one mouse click away. There is a single interface for
all database types. Therefore, when you are familiar with one, adding a new
database type is just a connectivity problem; the internal structure is already
on your familiar screen.
DBArtisan provides support for
- IBM DB2 UDB 6.x & 7.x
- IBM DB2 for OS/390 6 & 7
- Oracle 7.3, 8.x & 9i
- Microsoft SQL Server 6.5, 7.0 and 2000
- Sybase System 11.9.2 & 12.x
The DBA that has to move from project
to project, working on the customer's laptop or PC will need to make only one
software installation to be ready to start working in a multidatabase
environment. Without DBArtisan, he would spend the first few days just
installing the necessary database tools for all of the database types before he
could start work.
Another good DBArtisan feature is the
integrated internal scheduler. Some will say, "But we have already
one. On Unix systems we can use cron and on Windows, daemon." The internal
DBArtisan scheduler brings a separate
scheduler, dedicated only to database administration tasks, to the DBA.
With it, the DBA can run a script when he needs without
the need to consult Unix administrators, check timing with others, or remind
himself to delete the entry after testing or finishing a one time procedure.
With DBArtisan, just set the job and it will act as a normal timed client
connection coming from your desktop.
Even if you close DBArtisan, your job
will start and complete regularly.
Did you hear that DBAs are very, very
A DBA likes to dig in the database and
explore the data. A Data Editing component, called DataGrid, fills that
purpose. Using DataGrid, a DBA can look at the content of the HR application "SALARY"
table and sort table data by the name.
Imagine that you have some logical
corruption and you need to make fast check for all of the 3000 values in a table
with more than 40 columns. You can use SQL and play for several hours to get the
result, or you can use DataGrid and browse around the table.
The Schema Publication option creates HTML_based schema referenced WEB sites and
many HTML Web reports about the database. You can generate completely database
documentation in HTML format, and later publish it on the Web, as a good
example of how to make detailed database documentation. With DBArtisan, the
database documentation is ready in a few minutes allowing the DBA time to spend
on IBM educational programs.
Reports are just
of generated HTML report
The last interesting DBArtisan
feature is "No database touch on
Most of the tools that exist on the market today need some agents, or helper
objects (so-called repositories) inside the database. Usually we need to set up
a new user to install the objects inside of the database. However, the customer
doesn't always want that the DBA to install anything in the database. For
example, if the database has a warranty, or is shared among several projects,
the customer might be reluctant to allow the DBA to install objects in the
database. DBArtisan can be installed without touching the database.
Included are some other features for
the hybrid and development DBA such as Visual SQL Statement Creation, graphical
supported data exchange between databases via Schema Migration, Performance Center for graphical expedition inside the database and
SQL Tuner to teach developers how to write application code.
Cons: The only bad word I have to say about DBArtisan is the lack of
support. Based on the quality of the product, you would expect better support. However,
while support is rarely necessary, when you do need it, they are very
slow and removed from reality.
Central, Peformance Diagnostic Module -Tool for Hybrid DBA
Quest Software has made history with Instance Monitor, then Spotlight
and now Quest Central. With each new installation, the application package is larger
and larger. With the last software incarnation, Quest Central provided a unified
console for a set of tools where instance monitoring is provided through an integrated
module, Performance Diagnostic Module.
This module is a great tool for DB2 activity monitoring and
You can watch real time database activity, compare
graphical charts for various resource utilizations, and get very good
interactive help, rich with advice and hints. Visual representation identifies
database areas with red, yellow and green colors. Critical events are marked
red, warning events yellow and green represents regular, normal activity.
DBAs used to keep the interactive graphical console open on the
desktop all of the time. This allowed a problem to be spotted before the
customer made an emergency call or we started the problem detection with event
monitors. The hybrid DBA, armed with database
activity charts and with good tuning experience can solve extremely complicated
situations in a very short time.
has the capability to collect all information from the OS such as CPU,
memory, disk utilization and virtual memory as a snapshot of overall system
of the resources from OS side will be identified; collected system information
can be compared with the database activity charts.. Armed with this
information, the DBA can ascertain if the system is well dimensioned for the
actual type of load.
If all of
the performance diagnostic module charts show lots of database reads, full
table scans, and huge sort and lock activity, then the DBA knows that something
was executed, some heavy SQL. The performance diagnostic module can call
another integrated module (SQL tuning module), which can uncover problematic
SQL from the database global memory. You can sort all of the cached SQL
commands by the user, time, disk reads, logical reads and many other ways.
and explaining problematic SQL query is very easy. Information collected by the
tool is enough for the DBA to open a call to the developers for correction of
the code or DBA can along with SQL Tuning module, make error corrections and
optimizations of the SQL code. The SQL Tuning module is very easy, intuitive
and has many advanced features, which can help in code optimization.
Cons: The performance diagnostic
module does not have any historical features. You can collect almost 24 hours
on your charts, however, after 24 hours, additional data collection is continued
by overwriting the previous data. During the tool installation procedure, we
need to know the database user with DBA rights, and we must have enough space
in the database for installation numerous proprietary objects. On every
upgrade, the repository must be rebuilt from scratch. You should consider
allocating separate tablespace for that.
-Tool for Development DBA
A development DBA does not have much choice,
when code optimization comes into play. Quest Central has a very good module
for optimization and development, but Lecco SQLExpert for DB2 UDB is one of the coolest
tools for the development DBA.
query, start query optimization and let artificial intelligence,
together with rewrite technology lead you to the light. It can generate every
possible semantically SQL alternative and unique access plan as well as provide
several scenarios which can later be executed as a run test to obtain the
execution times for comparison. All run tests are tested without an actual physical
execution. SQL tuning procedure is fully automatic, and the DBA can become an SQL
optimization expert very quickly. With SQLExpert, the DBA gets a better
understanding about the internal behavior of the database optimizer.
is suitable environment for development and for application code testing. The
tool helps the development DBA in development tasks or in testing new
or modified application SQL code such as COBOL, Java, C/C++, Perl, HTML and more. The
DBA can scan a source code (static or dynamic) and
DB2 event monitor files for problematic and inefficient code. Problematic SQL code can be identified and
access plans can be compared between bound and current.
tuning session, the tool will provide a new index list and new index
simulation options. SQLExpert will offer for SQL optimization a list of new
indexes. When you agree with the suggested new indexes, SQLExpert will start the
query simulation using the new indexes without physically creating them.
Complete SQL syntax is shown on the screen, and the DBA has just to click and
Cons: From time to time, it is good to check that the AI
is giving a true picture. Bad statistics or a combination of indexes can lead to
a erroneous conclusion. In such cases, and the actual run time of the tested
SQL may be much longer than indicated. For the DBA who would like to assess the software functionality,
Lecco will provide trial serial keys. After
the trial period expires, you have to unlock it with the real license keys. There
are no options to extend the trial period. If this is the tool of choice, the DBA will need
additional SQL Scanner and SQL Inspector modules, which are part of the very
expensive Professional Edition.
The database is at the core of today's critical business
applications and the DBA, as a data architect, programmer and administrator
needs To be armed with tools that enable him to easily meet the expectations of
the end users.
DBAs still dream about one super tool capable of making:
for allocated and used tablespace storage
Database housekeeping for automatic report and
removal of unused views and indexes from DB2 catalog
number optimization for installed database on a given hardware
detailed info about
have timed out
See All Articles by Columnist Marin Komadina