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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles