IBM Utilities vs. Third Party Tools for DB2

Thursday May 8th 2003 by Marin Komadina
Share:

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:

  • "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.

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:

  • Embarcadero Technologies Inc., DBArtisan v7.1
  • Quest Software, Quest Central for DB2 v2.1.0.262
  • BMC,DBXray v2.0.01, PATROL v6.4.0, SmartDBA Cockpit v1.6.01, Space Expert v1.1.00,
  • Unicenter Fast Load and Unicenter Fast Recovery for DB2
  • LECCO, SQL Expert Pro v2.6.1 for DB2 UDB
  • Precise Software, Precise/Indepth for IBM DB2 v2.0
  • DGI, Flight Deck v1.4
  • ...and many others

General characteristics of the third party tools:

  • Operating system statistics - collecting and displaying an operating systems statistics for multiple nodes and instances
  • Schema management - creating and altering database objects, extracting DLL for objects and their dependents, generating schema reports, copying objects between instances
  • Security management - schema, users and grants administration
  • Performance monitoring - key database components monitoring with real-time diagnostic and visualization
  • Space management - space utilization monitoring
  • Data management - data editing, between instances exchanging, query and script execution
  • Multiplatform 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
  • Web administration interface - web interface for database navigation and administration
  • Statistics Collecting - server based agents, running in the background for collecting statistics information and writing collected data inside special database repository

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

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

Daily 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 graphical console.

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 following databases:

  • 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 curious?

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


Part of generated HTML report

The last interesting DBArtisan feature is "No database touch on installation." 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.

Quest 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 performance optimization.

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.

The tool 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 behavior.

Any lack 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.

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

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

Find a 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.

SQL Expert 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.

During the 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 watch.

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.

Conclusion

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:

  • Space calculation for allocated and used tablespace storage
  • Database housekeeping for automatic report and removal of unused views and indexes from DB2 catalog
  • Partition number optimization for installed database on a given hardware
  • Timeout/deadlock reporting with detailed info about transactions that have timed out

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved