Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 8, 2003

IBM Utilities vs. Third Party Tools for DB2 - Page 4

By Marin Komadina

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.

DB2 Archives