Troubleshooting the IBM DB2 Database Plug-in

3 Troubleshooting the IBM DB2 Database Plug-in

This chapter provides details to help you identify, diagnose, and resolve issues that you may encounter while working with the IBM DB2 plug-inIBM DB2 Database plug-in.

The following topics are provided:

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:

Finding a TCP/IP Port for UNIX Platforms

Open a command prompt and run the following commands:
```
cd /usr/etc
cat services
```
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:

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
```
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
```
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
```

Finding a TCP/IP Port for Microsoft Windows Platforms

Open the DB2 Control Center on the remote Windows server.
Right-click one of the available instances for the local machine.
Click Setup Communications.
Click Properties.

The port number is listed in the Properties window.

Finding a TCP/IP Port for z/OS Platforms

Connect to the z/OS system.
Run the following command:
```
-DISPLAY DDF 
```
The TCPPORT value in the results is the port number.

IBM DB2 Problem Resolution

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

Using a Suitable OS User and Assigning Authorities and Privileges

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

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 IBM DB2 Database Plug-in User's Guide.

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.

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

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.

Lock Waits Metric Not Collecting

In one situation you may not see any data for the Lock Waits metric.

Possible Cause: Your DB2 database was created in Version 9.7 before Fix Pack 1.

Action: Run the db2updv97 command. If your database was created before version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration).

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

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.

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.

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
IBM DB2 Database Applications CPU Usage	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
IBM DB2 Database 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
IBM DB2 Database DB 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
IBM DB2 Database Tablespace Health	Tablespace Health Indicator	Tablespace Health Indicator
IBM DB2 Database Tablespace Statistics	Top 5 Tablespaces by Space Available (%)	Tablespace Storage
IBM DB2 Database Tablespace Statistics	Tablespaces Summary	Tablespace Storage

Support References for IBM DB2 Plug-in

The following support documents are available in My Oracle Support:

https://support.oracle.com

IBM DB2 Status pending: Could not create instance com.ibm.db2.jcc.DB2Driver (Doc ID 1528370.1):
```
https://support.oracle.com/rs?type=doc&id=1528370.1
```