DB2 Built-in Performance & Health Tools

20 May 2008

 

There are quite a few free tools built into DB2 for monitoring and tuning the performance and health of your databases.  In fact there are so many that I often loose track of the ones that are available and in which versions they exist Therefore, I decided to write this db2Dean article about them to provide a summary.  In this document I will list them and provide a short description about the tool and when it would be used.  In later articles I will expand on individual tools or groups of related tools.  This article only describes the tools.  It does not tell you what you need to do to make your database run well.  There are many articles about that and you can easily find them by searching Google.  For each tool or feature I indicate which version that it first became available if they became available since v5.2.  I have no experience with earlier versions, so features that came during or before v5.2 will be shown as v5.2.  If you have a favorite tool that I did not mention here, please email me at dean@db2Dean.com and let me know.  I would like to make this list as complete as possible.

 

For each tool, I provide a link to the 9.5 documentation about it.  If you are using an earlier version of DB2, I would highly recommend that you use the documentation for your version of the database.  Often the tools have had options added over time and trying to use an option that is not available in your version of the database can be a frustrating experience.  I have posted links to the information centers and technical libraries for all versions DB2 starting with v7.2 on my “Favorite Links” page that can be found at:  http://www.db2dean.com/Previous/DeanLinks.html

 

Monitor Switches

 

Available Since:           V5.2

Further Reading:        Chapter 2 of the System Monitor Guide and Reference

 

I highly recommend turning on all of the monitoring switches in all new databases and on all existing databases as soon as you can test them with the switches enabled.  In my experience the small overhead of having the switches on is not noticeable to any applications.  These switches tell DB2 to collect information about itself such at time spent in sorts and other important tuning information.  Often, if DB2 is already collecting this information and you experience a problem that problem can be diagnosed.  However, if the switches were off you have to turn them on and wait for the problem to occur again.  Many of the tools described below use the monitor switch information to show useful data.  To turn them on you can execute the following commands and then restart your instance:

 

update dbm cfg  using DFT_MON_BUFPOOL   ON

                      DFT_MON_LOCK      ON

                      DFT_MON_SORT      ON

                      DFT_MON_STMT      ON

                      DFT_MON_TABLE     ON

                      DFT_MON_UOW       ON

                      DFT_MON_TIMESTAMP ON;

 

Snapshot Monitoring

 

Available Since:           V5.2

Further Reading:        Later part of Chapter 3 of the System Monitor Guide and Reference

 

Getting a command line snapshot is the old tried and true way to get performance information from DB2.  Now that they are available I usually prefer to use the snapshot monitor administrative views that are described in the next section, but sometimes seeing all of the raw data displayed is still useful.  It is good to have an understanding of the snapshot because many of the other tools that show you system performance information use the snapshot information behind the scenes.  When you issue a GET SNAPSHOT command you see a bunch of information in a report format.  Some things are useful by themselves such “Dirty page steal cleaner triggers”, but many of the really useful things like Bufferpool Hit Ratio need to be calculated from the values of several different counters.  This is when you get out the spread sheet and start pasting in numbers or write yourself a nice little AWK script.  If you didn’t turn on your monitor switches like mother hen db2Dean told you to then you will see a value of “Not Collected” for many of the most useful parameters.  You should also keep in mind that the values of most parameters are cumulative since the instance, database, or application started.  This means that to be useful you may need to run the snapshot a few times over the period of time that you want to measure and calculate the delta values because the average since the system started a month ago may not be all that useful.  Here is an example of taking a snapshot on everything for the SAMPLE database:

 

get snapshot for all on sample;

 

 

Snapshot Monitor Administrative Views

 

Available Since:           V9.5

Further Reading:        Early part of Chapter 3 of the System Monitor Guide and Reference

                                    Full Descriptions of Views are in Administrative Routines and Views

 

Finally!  We now have views that can be queried that let you look at the snapshot information with easy SQL, with some of these views calculating basic performance information for you!  There are lots of great things about being able to look at these performance measurements in SQL.  With this it becomes really easy because you don’t need to calculate many of the basic measurements yourself, and for the ones that you do still need to calculate you can put the formula right into the SQL.  This also makes it easy to write a script to capture the performance information that is important to you on some regular interval (hourly, daily, etc.) and stick it into a table or group of tables.  With such a collection mechanism it becomes very easy to track your performance measurements over time.  The full list of snapshot monitor snapshot views is in the Further Reading links noted above.  Here are two examples: the first is of a regular view that just shows the snapshot information and the second is one of the “Convenience” views that have both basic snapshot information along with calculated values:

 

select * from sysibmadm.snapbp

select * from sysibmadm.bp_hitratio

 

Snapshot Table Functions

 

Available Since:           V8.1

Further Reading:        Early part of Chapter 3 of the System Monitor Guide and Reference

Full Descriptions of Functions are in Administrative Routines and Views

 

With the snapshot table functions, you can also get snapshot data through SQL, but the syntax is not as convenient as the administrative views.  They also do not calculate anything for you.  However, there is nothing stopping your from writing your own views based on these table functions if you want them in an earlier version of DB2.  Just DO NOT use the SYSIBMADM schema for your views.  This would probably do all sorts of odd things to your upgrade when you go to v9.5.  The snapshot table functions are listed in the “Further Reading” noted above.  To use these table functions in a select statement you must cast the output of the function as a table data type as shown in this example:

 

SELECT * FROM TABLE( SNAPSHOT_BP (CAST (NULL AS VARCHAR(1)),

                                  CAST (NULL AS INTEGER)))

           as SNAPSHOT_BP

 

Activity Monitor

 

Available Since:           V8.2

Further Reading:        Chapter 5 of the System Monitor Guide and Reference

 

The Activity Monitor is a GUI tool accessed from the Control Center used to monitor application performance and concurrency, resource consumption, and SQL statement usage of a database or database partition. The Activity Monitor provides a set of predefined reports based on a specific subset of monitor data. It also provides generic recommendations for most reports. Activity Monitor allows you to see what is going on in your system.  When you start the Activity Monitor it starts taking snapshots of the database and shows you the output in a convenient GUI based report.  By clicking on different column headings you can see the same information sorted different ways.  For example, if you want to see what SQL statements are the most used, you can click on the “executions” column and see the SQL statements currently being executed the most frequently.  Or you might be interested in the ones consuming the most resources, so you might click on the “user CPU time” column to see the statement consuming the most CPU time.  If you want to tune, these might bear the most fruit.  It is also good for resolving current problems.  You can look at what applications are holding or waiting for locks and view the tree of locks. 

 

Event Monitors

 

Available Since:           V5.2

Further Reading:        Chapter 4 of the System Monitor Guide and Reference

 

Event Monitors are used to collect all information about events that happen in your system.  For, example you can turn on an event monitor on STATEMENTS to collect all of the SQL issued.  Once you create an event monitor you can turn it on and off to collect events that happen during the timeframe that interests you.  They can be particularly useful in capturing information for problems that happen only sporadically.  Be careful with event monitors as some of them can put a significant load on your system and can consume a significant amount of disk space with the information that they collect.  In addition to statements you can collect information on transactions, deadlocks, tables and a number of other things.  To see the full list of event monitors and how to use them please see the Further Reading link above.

 

Explain

 

Available Since:           V5.2

Further Reading:        Visual Explain Tutorial

                                    For the “db2exfmt” command see Chapter 5 of the Command Reference

                                    For the explain command see the “Statements” chapter of the SQL Reference Volume 2

 

The explain facility allows you to see how your query will cause DB2 to access the data including how it will use indexes, sorting, etc., to get to the data it needs.  It will also provide a relative cost estimate of the query.  You can then modify the query and see how that will affect the access plan and the cost of the query.  The explain is especially useful if a particular query seems to take an excessive amount of time or if you see through the Activity Monitory or other means that a query is taking excessive resources.  It does not hurt to explain all SQL planned to be put into production, but this is often not practical.  The two main explain tools offered in DB2 are the Visual Explain that can be access through the Control Center or the command line explain tools.  When you find interesting SQL in the Activity Monitor, you can just click on it to see it in the Visual Explain.  Whether you use the Visual Explain or explain using the command line, the DB2 explain tables must exist in the database.  More recent Visual Explain tools will create them automatically if they do not exist.  If you need to create them you need to execute the “explain.ddl” file which is under the <instance_home>/sqllib/misc directory of the database instance. 

 

To explain SQL from the command line you must first put the explain information into the explain tables using the “explain” command and then view the results using the db2exfmt command.  Here is an example using the standard sample database:

 

EXPLAIN ALL WITH SNAPSHOT FOR

        select *

          from employee

         where firstnme = 'JAMES';

 

db2exfmt -d sample -e dcompher -g TIC -l -n % -w -1 -s % -# 0 –o expfile.txt

 

 

Design Advisor

 

Available Since:           V8.1 (Some components were available in previous versions)

Further Reading:        Chapter 17 of Tuning Database Performance

                                   

The Design Advisor is a nifty tool that will examine a set of SQL (a workload) and recommend indexes, materialized query tables (MQTs), clustering indexes, Multidimensional Clusters (MDCs), and the repartitioning of DPF tables if you have them. It can now also tell you which indexes and MQTs would not be used by DB2 for any queries in the workload.  The workload that you specify can be a file containing SQL or you can tell Design Advisor to use the contents of the Package Cache (dynamic SQL snapshot).  The package cache is where DB2 keeps copies of SQL that it has executed.  Once a statement has been executed it is kept in the package cache until the database shuts down, or the statement becomes the oldest one that has not been used and room is needed for a new one.  So if you are seeing that your “Package Cache Hit Ratio” is near 100% then your package cache is a very representative workload for Design Advisor.  If you want only the statements run during a certain period of time, then you can use the Event Monitor to collect the statements during that time and use that for the Design Advisor.  You can also use Query Patroller or Workload Manager to gather SQL for your Design Advisor workload.

 

The Design Advisor actually generates DDL for the objects that it recommends that you create.  You can then use that DDL to generate the objects in anyway that you see fit.  Since workloads tend to change over time, it is probably not a bad to run Design Advisor on your package cache quarterly to see if some new indexes or other objects could help keep your work load running at peak efficiency.  This is great tool for making packaged applications perform better by determining which indexes and other objects will improve performance.   You can run the Design Advisor from the Control Center or the command line.  Here is a command line example that gets its workload from the package cache (-g):

 

db2advis -d sample -g -m IMCP -o advisfile.txt

 

 

Automatic Statistics Collection

 

Available Since:           V8.2

Further Reading:        Chapter 20 of Tuning Database Performance

 

The Explain facility and Design Advisor are very powerful tools.  The DB2 Optimizer is the industry leading way to get the best query performance available.  However, all of these tools are worthless if there are no recent statistics about the database objects.  The good news is that DB2 will now collect these statistics automatically or you can still execute the RUNSTATS command on each table in a script.  There are also some new features that you can read more about that can enhance the benefit of statistics.  To enable automatic statistics, you can enable the following DB CFG parameters:

 

AUTO_MAINT

AUTO_TBL_MAINT

AUTO_RUNSTATS

 

Health Monitor and Health Center

 

Available Since:           V8.1

Further Reading:        Chapters 11-16 of the System Monitor Guide and Reference

 

The Health Monitor is a process that runs on your database server that monitors several aspects of the health of your database and alerts you of problems.  The Health Center is the GUI that you use to tell the Health Monitor how to contact you (e-mail, pager, etc.) in the event of a problem and to set the thresholds that indicate that there may be problems.  There are many health indicators that can be checked including how full your tablespaces are, if your instance is down, if your bufferpool hit ratio dropped below a threshold, and many, many others.  For threshold based indicators, you can set warning and alarm levels with different actions being taking at each, such as an e-mail at the warning level and a page at the alarm level.  This is a great tool to ensure that your system is running within bounds. 

 

 

Configuration Advisor

 

Available Since:           V8.1 (A similar function was available earlier)

Further Reading:        Chapter 3 of the Data Servers, Databases, and Database Objects Guide

 

The Configuration Advisor is a tool that will recommend values for all of Database Manager (DBM) and Database (DB) configuration settings.  It gathers information about your system such as CPU and RAM and asks 10 questions about how you will use the database.  It then recommends the best settings for these configurations.  I highly recommend using the Configuration Advisor to do the initial configuration of all new databases since its output is way better than the defaults.  It has been so successful, that starting in v9.1 when you create a new database the Configuration Advisor is run by default to set your configurations.  You can run the Configuration Advisor interactively from the Control Center or run it from the command line as shown in the following example.  This example will show the recommended changes, but not apply them because of the “apply none” option specified.

 

autoconfigure using

      mem_percent   85

      workload_type simple

      num_stmts     20

      tpm           3000

      admin_priority performance

      is_populated  yes

      num_local_apps 0

      num_remote_apps  500

      isolation     cs

      bp_resizeable yes

apply none;

 

 

Self Tuning Memory Manager

 

Available Since:           V9.1

Further Reading:        Self-tuning memory section of Chapter 13 of Data Servers, Databases, and Database Objects Guide

Self-tuning memory sections of Chapter 14 of Tuning Database Performance

 

The Self Tuning Memory Manager (STMM) is a fantastic new feature where DB2 will tune the main memory parameters that impact performance on the databases dynamically ensuring that RAM is allocated between those heaps and buffers for best performance.  This includes bufferpools.  DB2 monitors itself and reallocates the memory as necessary.  Even though there is some insignificant load put on the system to perform the monitoring, the pay back in performance will typically far exceed the cost of monitoring.  Starting in v9.5 on all Operating Systems the databases can also grow and shrink their total memory used as workloads change.  So one of the really cool things about this dynamic memory allocation is that if you have multiple databases that have peak workloads at different times, some the RAM will be dynamically reallocated to the databases with the highest loads.  DB2 strives for the best overall throughput, so none of the databases will be starved for RAM. 

 

In v9.1 databases, the dynamic allocation and de-allocation of memory between databases could only be done on Windows and AIX.  In v9.1 on other operating systems you would typically assign a static amount of memory to each database (DATABASE_MEMORY) and let the database allocate the memory between the internal buffers and heaps.  If you want to allocate a fixed amount of memory to each database and let the database pick the best use of it between the various heaps and buffers, you can still do that in v9.5.  The following fragment of the output of a “GET DB CFG SHOW DETAIL” command shows a database with STMM turned on and shows all of the DB CFG settings that can participate in STMM:

 

Description                            Parameter          Current Value     Delayed Value

--------------------------------------------------------------------------------------------

Self tuning memory                     (SELF_TUNING_MEM) = ON (Active)       ON

Size of database shared memory (4KB)   (DATABASE_MEMORY) = AUTOMATIC(37200)  AUTOMATIC(37200)

Max storage for lock list (4KB)        (LOCKLIST)        = AUTOMATIC(7456)   AUTOMATIC(7456)

Percent. of lock lists per application (MAXLOCKS)        = AUTOMATIC(98)     AUTOMATIC(98)

Package cache size (4KB)               (PCKCACHESZ)      = AUTOMATIC(5600)   AUTOMATIC(5600)

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR)  = AUTOMATIC(5000)   AUTOMATIC(5000)

Sort list heap (4KB)                   (SORTHEAP)        = AUTOMATIC(256)    AUTOMATIC(256)

 

To participate in the automatic memory management with the other heaps and buffers, the bufferpool page sizes must be set to automatic.  The following examples show how to make an existing bufferpool automatic and then how to query the status.  And yes, I do realize that a value of -2 in the NPAGES column is a highly intuitive way of telling us that the bufferpool is set to automatic.   Whatever!

 

alter bufferpool IBMDEFAULTBP size automatic;

 

select BPNAME, NPAGES from syscat.bufferpools;

 

BPNAME        NPAGES

------------  ------

IBMDEFAULTBP  -2

 

Memory Visualizer

 

Available Since:           V8.1

Further Reading:        Chapter 6 of the System Monitor Guide and Reference

 

The Memory Visualizer is GUI tool lets you monitor the memory of an instance and all of its databases.  You can see a live display of the memory use of the various heaps and buffers organized in a hierarchical tree.  It can be used to find performance problems.  You can change the configuration parameter settings for a memory component and access the effect of that the changes have.   You can access the Memory Visualizer from the control center by right clicking the instance name and choosing “View memory usage” from the pop-up menu.

 

 

db2mtrk – Memory Tracker Utility

 

Available Since:           V8.1

Further Reading:        Chapter 5 of the Command Reference

 

Are you curious about how much total memory your instance and databases are consuming on your system and how that memory is allocated to the various heaps and buffers?  Well db2mtrk is the utility for you.  This fine little tool will show you to the level of detail that you specify.  You can also specify a repeat interval like you do with vmstat to if you want to see if the memory consumed is changing.  Until you get used to the command I highly recommend always using the verbose (-v) option to see a useful description of the various heaps and buffers.  As an example, to see all of the instance and database memory heaps along with the amount of memory that each connection is consuming you would issue the following command:

 

db2mtrk -a -v -i -d  

 

 

db2pd – Problem Determination and Monitor Tool

 

Available Since:           V8.2

Further Reading:        Chapter 5 of the Command Reference

 

 

This tool has a bevy of options to show many different aspects of your database from the command line.  Since this is a summary article, I can’t list them all here, so I highly recommend reviewing the command in the above recommended reading.  In a partitioned environment you can specify that it will show you the information about a specified partition or all partitions.  A short and incomplete list of features of things it can display includes latch, lock, memory, instance, and connection information.  Again you really want to read all about this useful command.  It can even be used to run a variety of user commands against specified partitions of a database.  This example would show you all database and instance-scope information for the current partition:

 

db2pd -inst -alldbs

 

 

I hope that you find this article useful.  As new versions are released more tools become available, and as they do it becomes difficult to remember which tools your current version has.  If this describes your situation, then I believe this page will help by showing you at a glance which ones are available in your environment.

 

 

HOME | Search