IBM DB2

Version 2

    IBM DB2

    This template assesses the performance of an IBM DB2 database by retrieving performance data from the built-in SYSIBMADMtables.

    Prerequisites: IBM DB2 9.7 installed on the target server. IBM DB2 ODBC Driver (The driver can be found on the IBM DB2 installation media).

    You should also have access to SYSIBMADMtables.

    The following commands should be executed in Command Editor before using this template:

    • UPDATE DBM CFG USING DFT_MON_BUFPOOL ON
    • UPDATE DBM CFG USING DFT_MON_LOCK ON
    • UPDATE DBM CFG USING DFT_MON_STMT ON
    • UPDATE DBM CFG USING DFT_MON_UOW on
    • grant execute on function SYSPROC.MON_GET_TABLE to username

    where username is the user that will be used in APM for monitoring IBM DB2.  After these commands have been executed, restart the database server.

    Credentials: Database user name and password.

    Monitored Components

    Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application. For more information, see http://knowledgebase.solarwinds.com/kb/questions/2415.

    Database Used Space (MB)

    This component monitor returns the used space, in MB, of the current database.

    Note: By default, this monitor returns a value for the database named, SAMPLE. To change the database, find the following parameter in the ODBC driver string and replace SAMPLE with your database name:

     

    Database=SAMPLE;

    Log File Used Space in Specified Database (MB)

    This component monitor returns the used space, in MB, of the log file in the specified database.

    Note: By default, this monitor returns a value for the database named, SAMPLE. To change the database, replaceSAMPLE with your database name in the following SQL query:

     

    WHERE DB_NAME = 'SAMPLE'

    Log File Free Space in Specified Database (MB)

    This component monitor returns the free space, in MB, of the log file in the specified database.

    Note: By default, this monitor returns the value for the database named, SAMPLE. To change the database, replaceSAMPLE with your database name in the following SQL query:

     

    WHERE DB_NAME = 'SAMPLE'

    Average Buffer Total Hit Ratio (%)

    This component monitor returns the average Total Hit Ratio (index, XDA, and data reads) for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

     

    SELECT * FROM SYSIBMADM.BP_HITRATIO

     

    If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

     

    SELECT REAL(AVG(TOTAL_HIT_RATIO_PERCENT))
    FROM SYSIBMADM.BP_HITRATIO
    WHERE BP_NAME = 'IBMDEFAULTBP'

    The returned value should be as high as possible.

    Average Data Hit Ratio (%)

    This component monitor returns the average Data Hit Ratio for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

     

    SELECT * FROM SYSIBMADM.BP_HITRATIO

     

    If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

     

    SELECT REAL(AVG(DATA_HIT_RATIO_PERCENT))
    FROM SYSIBMADM.BP_HITRATIO
    WHERE BP_NAME = 'IBMDEFAULTBP'

    The returned value should be as high as possible.

    Average Index Hit Ratio (%)

    This component monitor returns the average Index Hit Ratio for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

     

    SELECT * FROM SYSIBMADM.BP_HITRATIO

     

    If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

     

    SELECT REAL(AVG(INDEX_HIT_RATIO_PERCENT))
    FROM SYSIBMADM.BP_HITRATIO
    WHERE BP_NAME = 'IBMDEFAULTBP'

    The returned vale should be as high as possible.

    Number of Locks Held in Specified Database

    This component monitor returns the number of Locks currently held in the specified database.

    Note: By default, this monitor returns the value for the database named, SAMPLE. To change the database, replaceSAMPLE with your database name in the following SQL query:

     

    WHERE DB_NAME = 'SAMPLE'

    The returned value should be as low as possible.

    Average Read Time (ms)

    This component monitor returns the average read time from the database in milliseconds. The returned value should be as low as possible.

    Connected applications to Specified Database

    This component monitor returns the number of connected applications to the specified database.

    Note: By default, this monitor returns the value for the database named, SAMPLE. To change the database, replaceSAMPLE with your database name in the following SQL query:

    WHERE DB_NAME = 'SAMPLE'

    Number of Long Running Queries

    This component monitor returns the number of Long Running Queries. You should set the threshold according to your environment.

    Number of Table Scans

    This component monitor returns the number of table scans currently in progress in the specified database.

    Table with the Biggest Table Scans Value

    This component monitor returns the table name with the Biggest Value of Table Scans and the value itself.

    Used Space of the Biggest Table (MB)

    This component monitor returns the name of the Biggest Table and its size, in MB.

     

    Portions of this document were originally created by and are excerpted from the following sources:

    IBM “DB2 database software,” Copyright © 2012 IBM Corporation.  All rights reserved. Available at http://www.ibm.com