In October 2010, IBM introduced first-in-market Extended Insight into identifying the source of response-time problems for web-based and distributed applications running with a DB2 for z/OS back end. Extended Insight is packaged with IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS V5.1.0 (OMPE), IBM's premier DB2 z/OS monitoring offering. IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS V5.1.0 allows end-to-end database transaction response-time monitoring for WebSphere Application Server applications with its Extended Insight feature. This capability provides insight into the transaction and SQL statement response-time metrics of a database application throughout all layers of the software stack — from the time the SQL is issued in the application through the network and database server. Support is available for WebSphere Application database applications in the form of additional time-spent metrics for the WebSphere Application Server layer and by recognizing transactions and SQL statements from WebSphere Application Server automatically. This article provides detailed information for installing, configuring, and validating the OMPE Extended Insight feature for WebSphere Application Server applications.
IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS V5.1.0 is the premier DB2 z/OS monitoring solution that contains a web-based user interface that helps you resolve your database performance issues with a guided approach. This user interface is packaged for the first time in V5.1.0 and shares the same web infrastructure as IBM Optim™ Performance Manager V4.1.1 Extended Edition. The Extended Insight feature provides end-to-end database performance monitoring for Java™ and CLI database applications (including .NET® applications), giving you the ability to quickly understand where your database applications are spending their time across the IT stack from the application through the application server, the database client, the database server, and the network.
When a critical business application is not performing the way you expect, you can use Extended Insight for end-to-end visibility from the line of code that issues a database statement through to the database server. This enables you to quickly isolate and address problems by understanding and identifying where the problem is. When the problem is not in the database itself, Extended Insight makes it easier for you to work with the people managing other parts of the infrastructure or with developers to solve the problem.
Value statement for monitoring WebSphere applications
If your database application is a WebSphere Application Server application, Extended Insight provides additional metrics to show how much time the transactions spend within WebSphere Application Server and how the WebSphere Application Server connection pool is set up and used. Extended Insight also provides the ability to compare the setup of different WebSphere Application Servers in situations where transactions from one WebSphere Application Server have a much slower response time than transactions from a another application server.
Extended Insight recognizes transactions coming from WebSphere Application Server automatically and groups them by WebSphere Application Server host name, WebSphere application name, or WebSphere Application Server user. This helps identify problematic WebSphere Application Server machines, specific problematic applications, or users.
This article describes the prerequisites and steps required to set up Extended Insight for WebSphere Application Server applications. It also shows how to validate the setup and trace it if necessary. This article is based on the OMPE package available on Shop Z, which currently includes Optim Performance Manager Extended Edition V4.1.1.
Before you set up Extended Insight on your WebSphere Application Server machine, you need to install the Repository Server and activate the Repository server using the license activation kit. The Repository Server and Activation Kit can be downloaded from Shop Z. Typically, you install the Repository Server on a dedicated machine — in particular, not on a machine where your WebSphere Application Server is running. The license can be activated during installation or by running a separate license activation kit after installation. During license activation, the Extended Insight communication ports are configured. Make sure you specify unused and open ports. Figure 1 shows the installer panel used to specify the communication ports for Extended Insight.
Figure 1. Extended Insight communication port specification
Tip: You can use the Performance Manager Server panel in the Web console to review the specified communication ports, or to change them later. Figure 2 shows this panel.
Figure 2. Extended Insight communication port display
After you have installed and activated the Extended Insight feature, make sure that the repository server and the web console are started. Then use the web console to configure the databases accessed by your WebSphere applications for monitoring.
To configure the databases for monitoring, open the Manage Database Connection panel in the web console, add the database, and then configure the database for monitoring. In step 2 of the configuration wizard, ensure that the Extended Insight monitoring profile is turned on (see Figure 3).
Figure 3. Enabling Extended Insight during configuration
Preparing the Extended Insight setup
Before you install and configure the Extended Insight Client software on your WebSphere Application Server machine, prepare the machine by completing the following steps:
- Ensure that your applications are using a recommended version of
WebSphere Application Server:
- WebSphere Application Server V18.104.22.168 or later with APAR PK98171
- WebSphere Application Server V22.214.171.124 or later
- WebSphere Application Server V126.96.36.199 or later with APAR PK98171
- WebSphere Application Server V188.8.131.52 or later
- Ensure that the WebSphere JDBC providers that correspond to the
monitored databases for which you want to collect Extended Insight
data contain a supported IBM Data Server Driver for JDBC and SQLJ.
- Recommended: IBM Data Server Drivers for JDBC and SQLJ V9.7
Fix Pack 2 or later for Linux®, UNIX®, and
- Corresponds to IBM Data Server Driver for JDBC and SQLJ V3.59 or later, or 4.9 or later
- To check the version of your current driver use the
following Java command from the location where your
driver is located:
java –cp db2jcc.jar com.ibm.db2.jcc.DB2Jcc –version
- Recommended: IBM Data Server Drivers for JDBC and SQLJ V9.7 Fix Pack 2 or later for Linux®, UNIX®, and Windows®
- Ensure that the communication ports that you specified during Optim Performance Manager activation are unused and not blocked by a firewall between the WebSphere Application Server and Optim Performance Manager machine.
Extended Insight setup
Installation and configuration
Optim Performance Manager Extended Insight Client software must be installed on every WebSphere Application Server machine on which you want to collect Extended Insight data. Install the package IBM Optim Performance Manager V4.1.1 Client Software that matches the operating system of your WebSphere machine. All supported platforms installation packages are contained in the ISO image number CD726042.ISO and named Optim Performance Manager Extended Edition for DB2 for Linux, UNIX, and Windows V4.1.1 Client Software DVD.
In most cases, perform the following steps, then select the Open the configuration tool option at the end of the installation. If you use WebSphere Application Server Network Deployment, then instead of opening the configuration tool, you must exit the installation and configure WebSphere manually as described in "Configuring WebSphere Application Server Network Deployment manually for extended insight monitoring:"
- From the Application Types selection panel of the
configuration tool, choose WebSphere applications, as
shown in Figure 4.
Figure 4. Application types selection panel
- From the Optim Performance Manager Server URL panel,
shown in Figure 5, enter the fully qualified host name or IP address
of the server on which the Repository Server is installed.
Additionally, give the Extended Insight communication ports that you
specified during Optim Performance Manager activation (see Figure 1). The first port (default
60000) is the UDP port on which the Extended Insight controller server (part of the Repository Server) is running. Click Next.
Figure 5. Optim Performance Manager Server URL specification panel
If, when you click Next, you get an error message indicating that the connection was unsuccessful, one or more of the following issues may have occurred:
- A personal client firewall is blocking the first communication port.
- The specified host name or IP address of the Repository Server is wrong.
- The Repository Server is not started.
- The specified first port for the Extended Insight controller server is
wrong. To obtain the correct port do one of the following:
- Log onto the Web Console and get the port number from the Performance Manager Server panel (see Figure 2).
- Check the Repository Server log
db2pesrv.logfor the following message to identify the right port:
The Extended Insight controller server is started on port xxx.
- From the WebSphere Application Server Setup panel,
enter the location of the WebSphere Application Server Profile that
contains the database applications for which you want to retrieve
Extended Insight data. If security is enabled for your WebSphere
Application Server, you must additionally specify credentials for the
security user and trust store information. Then click
Figure 6. WebSphere Application Server Setup panel
If you get an error message indicating that the connection to WebSphere Application Server failed, verify that the specified information is correct and that WebSphere Application Server is started.
- The next panel, shown below, lists the available JDBC providers
retrieved from the profile specified earlier.
Select the JDBC providers that correspond to the databases that are
accessed by the WebSphere applications for which you want to retrieve
Extended Insight data and that contain a supported IBM Data Server
Driver for JDBC and SQLJ. After you click Next, the
configuration tool adds the Extended Insight libraries
pdqmgmt.jar, and the directory of the property file
pdq.propertiesto the classpath of the JDBC provider.
Figure 7. JDBC Providers specification panel
- Use the next panel to verify that the database accessed by your WebSphere applications is configured for
monitoring by Optim Performance Manager. Enter the database host name,
the port number, and the database DDF location name, then click
Test connection. You can get the this
information by using the
DISPLAY DDFDB2 command.
Figure 8. Database verification panel
- If you see an error message indicating that the configuration of the
monitored database cannot be verified, check the following items:
- Ensure that you have provided the correct database host name, database port, and database DDF location name of the database that you are monitoring.
- Ensure that your Repository Server is up and running. On UNIX
and Linux, you can use the
OPMstatus.shcommand from the <OPM>/bin directory to verify that Optim Performance Manager is running. On Windows, check the status of the Optim Performance Manager service using the Control Panel.
- Ensure that the database is configured for monitoring. Use the Manage Database Connections dashboard in the Web Console to see the monitoring status of this database.
- Ensure that you have enabled the Extended Insight monitoring profile for the database. Verify this on the Manage Database Connections dashboard in the Web Console by opening the Configure Monitoring wizard for this database.
- If you see a message stating that the database is configured for
monitoring, click Next, then review the listed
summary information and click Configure. When the
configuration is completed, the Finish panel shown
below displays the completion messages and gives you further
- To complete the Extended Insight configuration for your WebSphere applications, restart WebSphere Application Server.
- If you use WebSphere Application Server V184.108.40.206 or
higher, you must add a customer property
enableEndToEndMonitoringFeatureand set it to true in the WebSphere Application Server V6 Administrative Console. The IBM Redbooks® publication "Optim Performance Manager" describes these steps in detail in chapter 3.4.2 of "Configuring Optim Performance Manager Extended Insight Client." After setting this property, restart WebSphere Application Server.
Figure 9. Finish panel
You have now configured the Optim Performance Manager Extended Insight Client software, and the Repository Server will begin to collect Extended Insight data for your WebSphere applications if they execute database transactions. If you need to configure additional applications for Extended Insight later, you can start the configuration tool by using the following commands:
<OPM EI install dir>/configuration/cfgtool.sh
<OPM EI install dir>\configuration\cfgtool.bat
Configuring WebSphere Application Server Network Deployment manually for Extended Insight monitoring
To install and configure Extended Insight in a WebSphere Application Server Network Deployment environment, install Extended Insight on each physical WebSphere Application Server machine, but do not launch the configuration tool at the end of the Extended Insight client installation.
Then edit the <OPM EI install dir>\pureQuery\pdq.properties file on
each physical WebSphere Application Server machine and add the property
pdq.cmx.controllerURL=<hostname>:<port>, where <
hostname > is the host name or
IP address of the machine where the Repository Server runs and <
port > is the UDP port on which the Extended
Insight controller server is running (default
After the Extended Insight client installation, open the WebSphere Application Server Administrative Console of the Deployment Manager. For each database for which you want to retrieve Extended Insight data, manually add the following entries to the classpath of the corresponding JDBC providers:
<EI_installation_dir>/pureQuery/pdq.jar <EI_installation_dir>/pureQuery/pdqmgmt.jar <EI_installation_dir>/pureQuery/
If you use WebSphere Application Server V220.127.116.11 or higher, you
must add the
customer property and set it to True in the WebSphere
Application Server V6 Administrative Console of the Deployment
Manager. The Redbooks publication "Optim Performance
Manager" describes these steps in detail in chapter 3.4.2:
"Configuring Optim Performance Manager Extended Insight Client."
After you add this property, synchronize the updates in the Deployment Manager Administrative Console to all WebSphere nodes and restart WebSphere Application Server on all nodes.
Validating the Extended Insight setup
Ensure that your configured WebSphere applications are running and executing database transactions, and that the Repository Server is started. Then validate the complete Extended Insight setup by looking at the collected monitoring data displayed on the Extended Insight dashboard on the Optim Performance Manager web console.
On the Extended Insight dashboard, you can display transaction response-time data by application, by user or by server using workload cluster groups. Predefined workload cluster groups are available that specify different groupings. To activate the WebSphere-specific predefined workload cluster groups, click Activate and select the WebSphere groups as shown below.
Figure 10. Workload cluster groups activation panel
After you activate the WebSphere workload cluster groups, you see your WebSphere applications within the WebSphere applications workload cluster group, as shown in Figure 11. The response-time columns show you the time-spent metrics per application so you can identify the application with the highest response time easily and determine where the most time is spent.
For example, you can see that the
daytrader2-ee5 application spent the most time
per transaction within the network. Further analysis of the response-time
breakdown includes a drill-down to more detailed metrics, to the executed
SQL statements and their time distribution and execution details, and to
WebSphere-specific configuration specifics, such as the connection pool
Figure 11. Extended Insight dashboard
NOTE: If, after a few minutes of monitoring in Recent mode, you do not see any data in the Average Network Time and Average Client Time columns, it is an indication that no Extended Insight data from your WebSphere applications is arriving at the Optim Performance Manager repository server. Verify that the following items to find the cause for this problem:
- Ensure that you restarted WebSphere Application Server after configuration.
- Ensure that the JDBC providers have a supported IBM Data Server Driver in the classpath.
- Ensure that the Extended Insight controller server is started within
the Repository server. Check the Repository server log
db2pesrv.logfor the following message:
The Extended Insight controller server is started on port xxx.
- Ensure that the Extended Insight client is using the same port number
for the controller server. Check the
pdq.propertiesfile in <OPM EI install dir>\pureQuery for the property
If you need to perform additional analysis to find the cause, you can enable Extended Insight tracing on the WebSphere Application Server machine.
Tracing the Extended Insight client
If no Extended Insight data from your WebSphere applications is arriving at the Optim Performance Manager repository server, you can enable Extended Insight client tracing to help you determine the root cause.
Enable the Extended Insight client trace
pdq.properties file available in
the classpath of the JDBC provider for your WebSphere applications, add
the following entries:
For example, the specified trace filename could be C:/temp/pdq.trc.
If you did not make manual changes to the classpath after you ran the
Extended Insight configuration tool, the
pdq.properties file is located in the <EI
client install dir>\pureQuery folder.
Restart WebSphere Application server to ensure that the new properties are used and ensure that the WebSphere applications execute database transactions. The trace file grows large very quickly, so copy it to a different location after two or three minutes and open the copied version.
Getting driver versions from the Extended Insight client trace
To verify that a supported IBM Data Server Driver is used, search for the
pureQuery Software Prerequisites in the
trace file. You will see information similar to that shown below.
Driver information in trace file
pureQuery Software Prerequisites satisfied. Found in classpath: IBM Data Server Driver for JDBC and SQLJ at release 3.61.47 Prerequisite level is 3.57 or higher. Java Runtime Environment version 1.6.0 Prerequisite level is 1.5 or higher.
Is the Extended Insight controller server reachable?
To verify that the Extended Insight controller server within Optim
Performance Manager server is reachable, search for the
controller server string in the trace file. For
a successful connection to the controller server, you will see information
like that shown below.
Successful controller connection in trace file
[pdq][Time:2011-09-01-23:13:51.390] Successfully connected to a controller server at bruneck/192.168.178.24:60000 [pdq][Time:2011-09-01-23:13:51.531] Monitor agent for datasource id:1 successfully connected to the monitor server at 18.104.22.168:60001
If you have an unsuccessful connection (for example, due to a firewall issue), you would see information similar to that shown below.
Unsuccessful controller connection in trace file
[pdq][Time:2011-09-01-23:19:54.375] WARNING: Failed to establish a socket connection to controller server at bruneck/192.168.178.24:60000
Is data sent to the Repository Server?
To verify that transaction and statement response-time data is sent to the
Repository Server, search for the
Monitor payload string in the trace file as
shown below. Note that the data is sent every minute in an
aggregated format, so the trace must run longer than one minute in
order for you to see these payload messages.
Extended Insight client payload messages
[pdq][Time:2011-09-02-09:38:09.546][Thread:queuereader:...[ConnectionImpl@34353435] [flushMessage] processor: Monitor payload (UTF-8): ["peri",10005,[1,1314949089406,60,1,[[null,null,null,null], ..more.. ,,[1314949029000,null,"22.214.171.124",5911,"PMOD911","DB2","DSN09015", ..more.. ,["mts",30,1,392,2,324254,2,313011,2,24748,26746,27088,42,744,42,0,0,42,1,782,1,47, "9=35,11=6,12=1",null,null,null,1,0,-1,-1]],]]
By following the steps outlined here, you have learned how to install the Extended Insight client software and configure it to monitor the transaction and SQL statement response times of your WebSphere applications, including the time-spent breakdown across the IT stack. The Extended Insight feature can help DBAs collaborate more efficiently with other IT staff members by providing them with a web-based user interface that provides overview, alert, and diagnostic dashboards to simplify root cause analysis in WebSphere applications that access DB2 data.
- Learn more in the Quick Start Guide for the end-to-end SQL monitoring function.
- Learn about your choices for getting the Extended Insight Analysis Dashboard in the Technote Deployment Alternatives — OMEGAMON PE for DB2 on z/OS Extended Insight Analysis Dashboard.
- Get more details about configuring Extended Insight in the IBM Redbooks "Optim Performance Manager."
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.