IBM® Optim Performance Manager is a web-based database monitoring solution that helps you resolve your database performance issues with a guided approach. IBM Optim Performance Manager's Extended Insight feature provides end-to-end database performance monitoring for Java® and CLI database applications, giving you the ability to quickly understand where your database applications are spending their time.
With Extended Insight, DBAs can quickly see and understand where database applications are spending 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 to give you end-to-end visibility from the line of code that issues a database statement right 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 also makes it easier for you to work with the people who are managing other parts of the infrastructure, or with developers to solve the problem.
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 that are coming from WebSphere Application Server automatically, and groups them by WebSphere Application Server hostname, WebSphere application name, or WebSphere Application Server user. This helps to 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. Additionally, it shows you how you can validate the setup, and trace it if necessary. This article is based on Optim Performance Manager Extended Edition V4.1.1.
Before you set up Extended Insight on your WebSphere Application Server machine, you need to install Optim Performance Manager Extended Edition and activate the license. Typically you install Optim Performance Manager on a computer that is separate from the database servers that you want to monitor, as well as your WebSphere Application Server machine. 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 that you specify unused and open ports. Figure 1 shows the installer panel that is 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 Optim Performance Manager web console to review the specified communication ports, or to change them later.
After you have installed and activated Optim Performance Manager Extended Edition, make sure that the repository server and the web console are started. Then use the web console to configure the databases that are accessed by your WebSphere applications for monitoring.
To configure the databases for monitoring, open the Manage Database Connection panel in the Optim Performance Manager 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.
Preparing the Extended Insight setup
Before you install and configure Optim Performance Manager Extended Insight Client software on your WebSphere Application Server machine, prepare the machine by completing the following steps:
- Ensure that you are using a recommended version of WebSphere
- IBM WebSphere Application Server Version 18.104.22.168 or later with APAR PK98171
- IBM WebSphere Application Server Version 22.214.171.124 or later
- IBM WebSphere Application Server Version 126.96.36.199 or later with APAR PK98171
- IBM WebSphere Application Server Version 188.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 Version
9.7 Fix Pack 2 or later for Linux®, UNIX®, and Windows®
- Corresponds to IBM Data Server Driver for JDBC and SQLJ Version 3.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 Version 9.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
Extended Insight 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:
- CI14KML – AIX
- CI14QML – HP-UX
- CI14NML – Linux for System z
- CI14MML – Linux for System x
- CI14PML – Linux for System p
- CI14JML – Windows
- CI14LML - Solaris
In most cases, perform the following steps and 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 then configure WebSphere manually as described in the section called 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 2.
Figure 2: Application types selection panel
- From the Optim Performance Manager Server URL panel
shown in Figure 3, enter the fully-qualified host name or IP address
of the server on which Optim Performance Manager 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 Optim Performance Manager repository server) is running. Click Next.
Figure 3: Optim Performance Manager Server URL specification panel
If, when you click the Next button, 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 Optim Performance Manager server is wrong.
- The Optim Performance Manager 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 on to the Optim Performance Manager web console and get the port number from the Performance Manager Server panel.
- Check the Optim Performance Manager 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 shown in Figure 4,
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 4: 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 in Figure 5 lists the available JDBC providers
that are retrieved from the profile that you specified in Figure 4.
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.jarand the directory of the property file
pdq.propertiesto the classpath of the JDBC provider.
Figure 5: JDBC Providers specification panel
- Use the next panel shown in Figure 6 to verify that the database that
is accessed by your WebSphere applications is configured for
monitoring by Optim Performance Manager. Enter the database host name,
the port number, and the database name, and then click Test
Figure 6: 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 name of the database that you are monitoring.
- Ensure that your Optim Performance Manager 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 Optim Performance Manager 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 Optim Performance Manager 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 in
Figure 7 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 version 184.108.40.206 or
higher then you must add a customer property
enableEndToEndMonitoringFeatureand set it to True in the WebSphere Application Server version 6 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 7: Finish panel
You have now configured the Optim Performance Manager Extended Insight Client software, and Optim Performance Manager 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
If you want 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
hostname> is the host name or IP
address of the Optim Performance Manager machine 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 correspondinge JDBC providers:
<EI_installation_dir>/pureQuery/pdq.jar <EI_installation_dir>/pureQuery/pdqmgmt.jar <EI_installation_dir>/pureQuery/
If you use WebSphere Application Server version 220.127.116.11 or higher, you
must add the
customer property and set it to True in the WebSphere
Application Server Version 6 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 Admininstrative 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 Optim Performance Manager is started. Then validate the complete Extended Insight setup by looking at the collected monitoring data that is 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 in Figure 8.
Figure 8: 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 9. The response time columns show you the time spent metrics per application so that you can identify the application with the highest response time easily, and determine where the most time is spent.
For example, in Figure 9, you can see that the
business reports application spent the most
time per transaction within the data server, whereas the
order entry application spent the most time per
transaction on the client side. Further analysis of the response time
metrics include drill down to more detailed time spent metrics, to the
executed SQL statements and their time distribution and execution details,
and to WebSphere-specific configurations such as the connection pool
Figure 9: 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 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
Optim Performance Manager server. Check the Optim Performance Manager
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 of this problem, you can enable Extended Insight tracing on the WebSphere Application Server machine.
Tracing 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.
How to enable the Extended Insight client trace
pdq.properties file that is 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 <OPM
EI 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 in Example 1:
Example 1: Driver information in trace file
pureQuery Software Prerequisites satisfied. Found in classpath: IBM Data Server Driver for JDBC and SQLJ at release 3.59.81 Prerequisite level is 3.57 or higher. Java Runtime Environment version 1.5.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 in Example 2:
Example 2: Successful controller connection in trace file
[pdq][Time:2011-01-20-15:15:11.000] Attempting to connect to a controller server using the pdq.cmx.controllerURL property fixed address: OPMEIDEMO.boeblingen.de.ibm.com/18.104.22.168:60000 ... [pdq][Time:2011-01-20-15:15:12.000] Successfully connected to a controller server at OPMEIDEMO.boeblingen.de.ibm.com/22.214.171.124:60000
If you have an unsuccessful connection (for example, due to a firewall issue) you would see information similar to that shown in Example 3:
Example 3: Unsuccessful controller connection in trace file
[pdq][Time:2011-01-25-10:57:59.765] WARNING: Failed to establish a socket connection to controller server at OPMEIDEMO.boeblingen.de.ibm.com/126.96.36.199:60000
Is data sent to Optim Performance manager server?
To verify that transaction and statement response time data is sent to
Optim Performance Manager server, search for the
Monitor payload string in the trace file as
shown in Figure 10. Note that the data is sent every minute in an
aggregated format, therefore the trace must run longer than one minute in
order for you to see these payload messages.
Figure 10: Payload information in trace file (output formatted)
(View a larger version of Figure 10.)
By following the steps outlined in this article, you have learned how to install Optim Performance Manager Extended Insight client 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 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.
- Get more details about configuring Extended Insight in the IBM Redbooks publication Optim Performance Manager.
- In the Optim family area on developerWorks, get the resources you need to advance your Optim product skills.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
Get products and technologies
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
- 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.
- Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.