Wise-GUY™

Monitors Database Manager,
Database, Tables,
SQL Activity, and
System Performance
(CPU, Paging, Memory)

Provides Tuning Suggestions


"[Database-Guys] is great. With Wise-GUY, we were able to improve the insert rate of one of our databases from 11,000 to 55,000 inserts per minute. Keep up the good work!"

Mike Ulm
DBA
CDS Fulfillment


Flight Deck

Palm-GUY

EEE-GUY

SQL-GUY

Connection-MINER

Pool-GUY

Space-GUY

Wise-GUY


Top Reasons
for Database-Guys Tools

Wise-GUY™ V2.0


Features  |   Platforms
Software Requirements
Hardware Requirements
Vision  |   Tell Me More!

Wise-GUY product sheet (PDF)

Sample Database Analysis
Sample Tables Analysis
Sample Tuning Analysis

Features

DB2 Universal Database provides a wealth of raw performance data which only becomes truly useful after a number of calculations. Wise-GUY does the math and tells you what you need to know.

The Newest Features!

Wise-GUY 2.0 includes several new features to maximize the performance of DB2 Version 8.1, including:

  • automatic generation of REORG commands for tables experiencing I/O degradation based on real-time monitoring results and table performance history

  • automatic capture of DB2 snapshot data for locks when too many applications are waiting on locks

  • automatic generation of RUNSTATS commands for tables having out-of-date (stale/obsolete) catalog statistics and for tables with missing statistics

  • new performance metrics and updated displays that will help the DBA pinpoint performance problems even more quickly

  • and more!

Product Highlights

  • Runs interactively or unattended
  • Produces reports to a terminal AND/OR color coded HTML pages
  • Saves monitored results automatically
  • Integrates system utilizations (CPU, Paging, more) with DB2 performance metrics
  • Evaluates your configuration and performance and optionally suggests tuning recommendations
  • Monitors and tunes any remote database from a UNIX client, including DB2/NT.
  • Remotely saves performance history to any DB2 database on your network.

Wise-GUY can save you hours every day by performing tedious, yet critical, calculations for your databases. In a matter of seconds, refreshed as often as you like, you can quickly observe:

  • Machine Utilization Statistics
    • CPU Busy %
    • Paging rates
    • Virtual memory used (MB)
  • Lock Statistics
    • Number of lock waits
    • Average lock waiting time (ms)
    • Percentage of locklist memory used (at that moment)
    • Highest percentage of locklist memory used
  • Sort Statistics
    • Total number of sorts
    • The average sort time (ms) per sort
    • The number of sorts currently active
    • The number of sorts that have overflowed SORTHEAP and been forced to use TEMPSPACE disk
    • The percentage of sorts overflowed
      • Tune SORTHEAP to minimize overflows
        • Wise-GUY™ does this automatically
    • The current SORTHEAP in use for the database
    • The maximum SORTHEAP used by the database
    • The average amount of SORTHEAP in use by the database
    • New!High water mark for maximum number of concurrent sorts
    • The number of piped sorts requested and accepted
    • The percentage of piped sorts accepted
      • Tune SORTHEAP and SHEAPTHRES to achieve 100% acceptance
  • Bufferpool Statistics
    • Number of logical, physical, and asynchronous data reads
    • Number of logical and physical index reads
    • Number of physical and asynchronous data writes
    • Number of physical and asynchronous index writes
    • Percentage of random reads
      • Random read percent generally high for OLTP, low for Data Warehouse
    • Percentage of asynchronous writes
      • Strive for 100%
      • Tune NUM_IOCLEANERS accordingly
    • Average disk read times (ms) - a measure of disk performance
    • Average disk write times (ms) - a measure of disk performance
    • Overall hit ratio for all bufferpools
    • Index hit ratio for all bufferpools
  • Key HEAP measurements
    • Maximum percentage of DBHEAP used
      • A high % used (>80) indicates need for increase
    • Package cache lookups, inserts, and Hit Ratio
    • Catalog cache lookups, inserts, and Hit Ratio
  • Log Statistics- Max Percent Primary Log Space Used
  • SQL Activity
    • Average number of SQL Statements per Transaction
    • Assorted Counts including: Commits, Rollbacks, Dynamic SQL, Static SQL, Select SQL, DML SQL, Binds, Prepares, DDL...
    • Row Counts for: Inserts, Updates, Deletes, Selects
  • Direct Read Data and Statistics
  • Direct Write Data and Statistics
  • Internal Counters
    • Automatic Rebinds
    • Internal Commits
    • Internal Rollbacks and Rollbacks due to Deadlock
    • Rows Selected, Inserted, Updated, Deleted
  • Cleaner Triggers and key percentages
    • Tuning advice for NUM_IOCLEANERS
  • Log space available, used, and percent used (DB2 UDB V6.1+ only)
  • Hash Join Statistics
  • Database Manager Agent Utilization
  • Table Rows Read per transaction and per minute
  • Table Rows Written per transaction and per minute
  • Alert Capabilities
  • And much much more!

All the items highlighted in italics are calculated by Wise-GUY. DB2 UDB doesn't give you this information: It must be calculated and tracked, and Wise-GUY does this for you!


Using all the information monitored, calculated, and displayed by Wise-GUY, you should be able to:

  • Appropriately adjust LOCKLIST
  • Proactively respond to locking delays
  • Tune SORTHEAP to reduce overflows
  • Tune NUM_IOCLEANERS to improve asynchronous writes
  • Tune key HEAPS
    • DBHEAP - Never ever ever ever run out (or bad things happen)!
    • Package CACHE size
    • Catalog CACHE size
  • Be alert to SQL activity trends

And that's just the beginning...

Smartest of the Database-Guys family, Wise-GUY evaluates many of the calculated values against definable thresholds and automatically (always subject to your approval):

  • Proposes increases to DBHEAP
  • Proposes increases to LOCKLIST
  • Proposes increases to cache sizes
  • Generates the commands necessary to implement the proposed changes

In the stock market, past performance may not be a valid indicator of future performance. However, if you believe, as we do, that history repeats itself, then DB2 past performance may indeed be an indicator of future performance.

Wise-GUY can also optionally populate a data mart of database performance history. Counts and calculated values are stored right alongside database configuration values and tracked over time. The end result is a goldmine of valuable performance data that can be used for trend and correlation analysis. One row is stored at the end of each Wise-GUY monitoring session producing, quite possibly, your most valuable small table.


BOTTOM LINE

Wise-GUY helps you:

save time

improve performance

avoid failures


Architecture

Wise-GUY uses the DB2 UDB snapshot monitors to its strategic advantage and performs dozens of important calculations instantly. While Wise-GUY monitoring is active, Wise-GUY tracks key performance data which is not otherwise provided by DB2 (eg. Highest percentage of LOCKLIST used).

When monitoring is complete, calculated values are compared against tunable thresholds and goals, and then tuning changes are proposed.


Wise-GUY automates Tuning!

At the user's discretion, the Wise-GUY monitoring results can be saved in the Database-Guys Performance data-mart database. Over time, the accumulated data will help you make tuning decisions and forecast utilization growth.

That warrants repeating. Wise-GUY's data-mart can help you forecast utilization growth. Herein lies the justification for the next hardware upgrade!

 

Platforms Supported

Wise-GUY is optimized for DB2 UDB versions 6.1, 7.x, and 8.1 for both Enterprise Edition (EE) and Extended Enterprise Edition (EEE).

DB2 UDB Enterprise Edition

DB2 UDB Extended Enterprise Edition

Operating
System
Supported Versions Remarks
AIX®

5.1 (32-bit)

4.3

5.1 (64-bit) coming soon!
Sun Solaris®

8

7

2.6

 
Linux® Intel

OS/390 coming soon!

HP-UX® HP-UX Inquire for details

Software Requirements

  • DB2 UDB per platforms listed
  • Operating System per platforms listed
  • A web browser capable of displaying HTML 3.2 documents
Hardware Requirements
  • Software requires approximately 8MB disk
  • Depending on history retention requirements for performance data, an additional 5-10MB of disk space should be reserved for Wise-GUY data-mart data.

Vision for the Future

We would enjoy the opportunity to discuss our future plans and directions with you. Please complete our additional information request form.

I Want More!

If you'd like more information on Wise-GUY or any of Database-Guys's products, please complete our Tell Me More! form.

Last Updated: Fri, 06 Jun 2003 15:16:17 EDT

Home | Contact Us | Support | Top 10 Tips | Customer Feedback | Careers | Downloads
Live! Monitor Our Database!   |   Live! Monitor Our Tables!
White Papers and Other Resources


Copyright © 2003 Database-Guys. All rights reserved. Privacy and Legal details.