Skip Headers
Oracle® Enterprise Manager System Monitoring Plug-in Troubleshooting Guide
Release 12.1.0.3.0 for Third-Party Database Plug-ins

E35212-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Troubleshooting IBM DB2 Plug-in

The following sections help you identify, diagnose, and resolve issues that you may encounter while working with the IBM DB2 plug-in:

3.1 Finding a TCP/IP Port

The communication port to access the remote IBM DB2 instance can be configured. The default value is 50000.

To find the port for a particular IBM DB2 instance, perform the instructions for the following platforms:

3.1.1 Finding a TCP/IP Port for UNIX Platforms

  1. Open a command prompt and run the following commands:

    cd /usr/etc
    cat services
    
  2. Start of changeScroll through the list of services until you find the connection port number for the database instance of the remote database.

The instance name is usually listed as a comment. If it is not listed, then complete the following steps to find the port:

  1. Open a DB2 command prompt, and run the following command to verify that you are on the correct instance. IBM DB2 will report the current instance.

    get instance
    
  2. Run the following command to find the service name for your instance:

    get dbm cfg | grep SVCE
    

    IBM DB2 will report the service name.

    For example:

    TCP/IP Service name SVCENAME) = db2cdb2inst24
    
  3. Use the service name to find the port number in the services file.

    For example, enter the following command:

    grep service_name /etc/services
    

    IBM DB2 will return the information.

    For example:

    service_name   50012/tcp   # Connection port for DB2 instance instance
    

3.1.2 Finding a TCP/IP Port for Microsoft Windows Platforms

  1. Open the DB2 Control Center on the remote Windows server.

  2. Right-click one of the available instances for the local machine.

  3. Click Setup Communications.

  4. Click Properties.

    The port number is listed in the Properties window.

3.1.3 Finding a TCP/IP Port for z/OS Platforms

  1. Connect to the z/OS system.

  2. Run the following command:

    -DISPLAY DDF 
    

    The TCPPORT value in the results is the port number.

3.2 IBM DB2 Problem Resolution

The following sections help you fix issues that you may encounter while working with the IBM DB2 plug-in:

3.2.1 Using a Suitable OS User and Assigning Authorities and Privileges

You might see the following error on the Monitoring Configuration page of Enterprise Manager Cloud Control:

oracle.sysman.emSDK.emd.comm.CommException: Connection refused

Error while executing query, DB2 SQL error:  SQLCODE -443, SQLSTATE: 38553, SQLERRMC: SNAPSHOT_DBM;SNAPSHOT_DBM;SQL1092 Reason code or token: USRWOSYSMON

Possible Cause: You are not using a user or the user you are using does not have the correct privileges.

Action: Use a user that has at least the minimum privileges. For information about creating a suitable operating system user and assigning authorities and privileges to that user, see Enterprise Manager System Monitoring Plug-in Installation Guide for IBM DB2 Database available at:

http://docs.oracle.com/cd/E24628_01/install.121/e25215/toc.htm

3.2.2 Incorrect Credentials Used

You might see the following error:

Failed to contact the target to be added. Following errors were received while testing the connection to the target. Update the properties accordingly, try Test Connection for testing the properties before saving. 

Response - oracle.sysman.emSDK.emd.fetchlet.FetchletException: Error while obtaining connection.Connection authorization failure occurred. Reason: password invalid. 

Possible Cause: You are using incorrect credentials.

Action: Use the correct credentials.

3.2.3 Failure in Loading Classpath: Could Not Create Instance

You might see the following error:

Failure in loading Classpath: Could not create instance: com.ibm.db2.jcc.DB2Driver

Possible Cause: You are using a newer version of the JDBC drivers which does not contain the db2jcc_javax.jar file.

Action: You can confirm possible cause this by listing the files in the following directory:

$AGENT_BASE_DIR/plugins/dependencies/oracle.em.sidb/jdbcdriver

If you have only the db2jcc.jar and db2jcc_license_cu.jar files, then you have version 3.5 drivers or above, which has deprecated the db2jcc_javax.jar file.

To resolve this issue:

  • For UNIX:

    touch $AGENT_BASE_DIR/plugins/dependencies/oracle.em.sidb/jdbcdriver/db2jcc_javax.jar
    
  • For Microsoft Windows:

    • Create an empty text file in the following directory:

      $AGENT_BASE_DIR/plugins/dependencies/oracle.em.sidb/jdbcdriver/
      
    • Rename the file to db2jcc_javax.jar

See IBM DB2 Status pending: Could not create instance com.ibm.db2.jcc.DB2Driver (Document ID: 1528370.1) in My Oracle Support:

https://support.oracle.com

3.2.4 No Data for Health Indicator Metrics

In some cases, you may not see any data for Health Indicator metrics.

Possible Cause: You have not enabled the HEALTH_MON database manager configuration parameter.

Action: For data to be collected for the Health Indicators metric, you have to enable the HEALTH_MON database manager configuration parameter. Once enabled, the table functions (for example, HEALTH_TBS_HI, HEALTH_DB_HI, and HEALTH_DBM_HI) will be populated.

Note:

Enabling these settings may result in some overheads, such as CPU and memory. Therefore, follow these troubleshooting steps only if you want to view the Health Indicator metrics.

To enable or disable the HEALTH_MON by CLP (Command Line Processor), run the following command:

db2==> update dbm cfg using HEALTH_MON [on;off]

To check if your changes are effective, run the following command:

db2==> get dbm cfg

The following is the output:

.....
.....
.....
Monitor health of instance and databases (HEALTH_MON) = ON
.....
..........

For more information, access the IBM Web site.

3.3 Database Monitoring Metric Collection Error Messages

Table 3-1 shows common error messages and their appropriate resolution you may encounter when collecting database monitoring metrics. See Table 3-2 for a summary of the report elements and dependent metrics for IBM DB2.

Table 3-1 Database Monitoring Metric Collection Error Messages

Error Message Possible Cause Action
oracle.sysman.emSDK.emd.fetchlet.FetchletException: 
Error while executing query. DB2 SQL error:
 SQLCODE: -204, SQLSTATE: 42704, SQLERRMC:
 SYSTOOLS.STMG_DBSIZE_INFO

The table STMG_DBSIZE_INFO is not created.

For data to be collected for the Database Monitoring metric, make a call to the GET_DBSIZE_INFO package so that the STMG_DBSIZE_INFO table gets created and populated with the required data.

The GET_DBSIZE_INFO procedure calculates the database size and maximum capacity. The calculated values are returned as procedure output parameters and cached in the SYSTOOLS.STMG_DBSIZE_INFO table. The procedure caches these values because the calculations are costly.

The SYSTOOLS.STMG_DBSIZE_INFO table is created automatically the first time the procedure runs. If there are values cached in the SYSTOOLS.STMG_DBSIZE_INFO table and they are current enough, as determined by the snapshot-timestamp and refresh-window values, then these cached values are returned.

If the cached values are not current enough, new cached values are calculated, inserted into the SYSTOOLS.STMG_DBSIZE_INFO table and returned, and the snapshot-timestamp value is updated. The last parameter in the GET_DBSIZE_INFO call is refresh window.

Default value refresh window (time difference between successive calls) is 30 minutes. If your database is growing at a faster rate, then you can set a lower value.

To make a call to GET_DBSIZE_INFO by CLP (Command Line Processor), run the following command:

db2==>CALL GET_DBSIZE_INFO(?, ?, ?, -1)

In this case, the refresh window is 30 minutes.

For more information, access the IBM Web site.

Target - <target_name>
      Type - IBM DB2 Database
      Metric - DB2 Diag Log File Monitoring
      Collection - Timestamp Apr 1, 2009 10:19:59 AM
     Error Type - Collection Failure
     Message - Use of uninitialized value in string eq at
 e:\oracle\agent10g/sysman/admin/scripts/
emx/ibm_db2_database/logmine.pl line 188. 
The DB2 Database Manager Configuration Parameter DIAGPATH needs to be set

Following could be the causes of the error:

  • DIAGPATH is not set

  • Database is down

If DIAGPATH is not set, set the DIAGPATH run from the command line as follows:

db2 update dbm cfg using  diagpath <path>

On Windows systems, the path is <db2 installation directory>\<instance name>

On Linux or AIX systems, the path is <instancehome>/sqllib/db2dump

If the DIAGPATH is already set, metric collection errors will occur if the database is down.


3.4 Report Elements and Dependent Metrics

Table 3-2 summarizes the report elements and dependent metrics for IBM DB2.

Table 3-2 IBM DB2 Report Elements and Dependent Metrics

Report Report Element Metric

IBM DB2 Database Applications CPU Usage

Top 10 Applications Based on Total CPU Usage (ms)

Agent Monitoring

Top 10 Applications Based on Total Idle Time (ms)

Agent Monitoring

IBM DB2 Database Applications Lock Performance

Top 10 Applications Based on Average Lock Wait Time (ms)

Agent Monitoring

Top 10 Applications Based on Number of Locks Held

Agent Monitoring

Top 10 Applications Based on Number of Lock Timeouts

Agent Monitoring

IBM DB2 Database Applications Row Accesses and Sorts Performance

Top 10 Applications Based on Rows Read

Agent Monitoring

Top 10 Applications Based on Rows Written

Agent Monitoring

Top 10 Applications Based on Time Spent in Sorts (ms)

Agent Monitoring

IBM DB2 Database Bufferpool and Non-Buffered IO Statistics

Bufferpool Activity Summary

Database Performance

Non Buffered IO Activity Summary

Non Buffered IO Performance

Data Read Rate

Database Performance

Index Read Rate

Database Performance

Index and Data Write Rate

Database Performance

Non Buffered IO

Non Buffered IO Performance

IBM DB2 Database Cache Statistics

Package Cache Summary

Cache Performance

Catalog Cache Summary

Cache Performance

Package Cache Hit Ratio

Cache Performance

Package Cache Overflows

Cache Performance

Catalog Cache Hit Ratio

Cache Performance

Catalog Cache Overflows

Cache Performance

Catalog Cache Heapfull

Cache Performance

IBM DB2 Database Container Health

Container Health Information

Container Health

Container Health Indicator

Container Health Indicator

IBM DB2 Database DB Disk Storage Statistics

Disk Space Utilization

Database Monitoring

Disk Space Utilization Summary

Database Monitoring

Disk Space Utilization Details

Database Monitoring

IBM DB2 Database DB Health

Database Health Information

Database Health

Database Health Indicator

Database Health Indicator

Database Collection Health Indicator

Database_health_collection

IBM DB2 Database DB Manager Agents and Connections Statistics

Agent Configuration

DB Manager Configuration: Capacity (Configuration Metric)

Agent Pool Activity

Agents Connection

Agents Creation to Assignment Ratio

Agents Connection

Idle Agents

Agents Connection

Agents Waiting on Token

Agents Connection

Remote Connections

Application Connection

Remote Connections Summary

Application Connection

Local Connections

Application Connection

Local Connections Summary

Application Connection

IBM DB2 Database DB Manager Configuration

DB Manager Capacity

DB Manager Configuration: Capacity (Configuration Metric)

DB Manager Database Instance

DB Manager Configuration: Database Instance (Configuration Metric)

DB Manager Log and Recovery

DB Manager Configuration: Logging and Recovery (Configuration Metric)

DB Manager Partitioned DB Environment

DB Manager Configuration: Partitioned Database Environment (Configuration Metric)

DB Manager Connections

DB Manager Configuration: Connection (Configuration Metric)

IBM DB2 Database DB Manager Health

Database Manager Health Information

Database Manager Health

Database Manager Health Indicator

Database Manager Health Indicator

IBM DB2 Database DB Manager Sorts Statistics

Database Manager Sorts Summary

SortHeap Performance

Post Threshold Sorts and Joins

SortHeap Performance

Piped Sorts Rejection Rate

SortHeap Performance

IBM DB2 Database Locks Statistics

Locks Summary

Database Monitoring

Average Lock Wait Time

Database Monitoring

Locks Held and Waiting

Database Monitoring

Application Escalations and Timeouts

Database Monitoring

Deadlocks and Internal Deadlock Rollbacks

Database Monitoring

IBM DB2 Database SQL Statement Performance

Top 10 Statements Based on Rows Read

Agent Monitoring

Top 10 Statements Based on Rows Written

Agent Monitoring

Top 10 Statements Based on Average Sort Time (ms)

Agent Monitoring

Top 10 Statements Based on CPU Usage (ms)

Agent Monitoring

IBM DB2 Database Sort Heap and Hash Join Statistics

Total Sorts and Hash Joins

SortHeap Performance

Active Sorts

SortHeap Performance

Active Sorts Summary

SortHeap Performance

Average Sort Space Used

SortHeap Performance

Average Sort Time Per Sort

SortHeap Performance

Sorts Overflow Ratio

SortHeap Performance

Hash Join Small Overflows and Overflows

SortHeap Performance

Hash Join Small Overflows to Overflows Ratio

SortHeap Performance

IBM DB2 Database System Configuration

System Configuration

DB2 System (Configuration Metric)

Product Overview

DB2 Product (Configuration Metric)

Instances

DB2 Instance (Configuration Metric)

Partitions

DB2 Partitions (Configuration Metric)

Registry Settings

Registry Settings (Configuration Metric)

IBM DB2 Database Tablespace Health

Tablespace Health Information

Tablespace Health

Tablespace Health Indicator

Tablespace Health Indicator

IBM DB2 Database Tablespace Statistics

Top 5 Tablespaces by Space Available (%)

Tablespace Storage

Tablespaces Summary

Tablespace Storage


3.5 Support References for IBM DB2 Plug-in

The following support documents are available in My Oracle Support:

https://support.oracle.com
  • Doc ID 1528370.1: EM12C: IBM DB2 Status pending: Could not create instance com.ibm.db2.jcc.DB2Driver