This chapter provides details to help you identify, diagnose, and resolve issues that you may encounter while working with the IBM DB2 Database plug-in.
The following topics are provided:
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:
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
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.
Connect to the z/OS system.
Run the following command:
-DISPLAY DDF
The TCPPORT value in the results is the port number.
The following sections help you fix issues that you may encounter while working with the IBM DB2 plug-in:
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
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.
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
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.
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).
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 |
For data to be collected for the Database Monitoring metric, make a call to the The The If the cached values are not current enough, new cached values are calculated, inserted into the 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 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:
|
If db2 update dbm cfg using diagpath <path> On Windows systems, the path is On Linux or AIX systems, the path is If the |
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 |
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