Introduction

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.

Back to top

Prerequisites

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.

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.

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).

Back to top

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:

1. Ensure that your applications are using a recommended version of WebSphere Application Server:
   • WebSphere Application Server V6.1.0.27 or later with APAR PK98171
   • WebSphere Application Server V6.1.0.31 or later
   • WebSphere Application Server V7.0.0.5 or later with APAR PK98171
   • WebSphere Application Server V7.0.0.11 or later
2. 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 Windows®
     • 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
3. 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.

Back to top

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:"

1. From the Application Types selection panel of the configuration tool, choose WebSphere applications, as shown in Figure 4.
   
   
   Figure 4. Application types selection panel
   
   
   

2. 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.log for the following message to identify the right port:
    The Extended Insight controller server is started on port xxx.

3. 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 Next.
   
   
   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.

4. 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 pdq.jar and pdqmgmt.jar, and the directory of the property file pdq.properties to the classpath of the JDBC provider.
   
   
   Figure 7. JDBC Providers specification panel
   
   
   

5. 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 DDF DB2 command.
   
   
   Figure 8. Database verification panel
   
   
   

6. 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.sh command 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.

7. 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 instructions:
   
   • To complete the Extended Insight configuration for your WebSphere applications, restart WebSphere Application Server.
   • If you use WebSphere Application Server V6.1.0.27 or higher, you must add a customer property enableEndToEndMonitoringFeature and 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:

• UNIX/Linux: <OPM EI install dir>/configuration/cfgtool.sh
• Windows: <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 60000 ).

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 V6.1.0.27 or higher, you must add the enableEndToEndMonitoringFeature 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.

Back to top

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.

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 settings.

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.log for 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.properties file in <OPM EI install dir>\pureQuery for the property pdq.cmx.controllerURL=<hostname>:<port>

If you need to perform additional analysis to find the cause, you can enable Extended Insight tracing on the WebSphere Application Server machine.

Back to top

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

In the pdq.properties file available in the classpath of the JDBC provider for your WebSphere applications, add the following entries:

    pdq.traceLevel=ALL
    pdq.traceFile=<filename>
            

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 string pureQuery Software Prerequisites in the trace file. You will see information similar to that shown below.

                
    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.

                
[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 9.79.33.175:60001

If you have an unsuccessful connection (for example, due to a firewall issue), you would see information similar to that shown below.

                
[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.

                
[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,"9.152.87.221",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]],[]]]

Back to top

Conclusion

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.

Resources

Learn

• 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.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Ute Baumbach has been a software developer at the IBM lab in Germany for 20 years, where she has worked in various software development projects and roles. Most of her projects were based on DB2. For eight years, she has worked as a member of the DB2 Performance Expert development team, now the Optim Performance Manager development team. She is an IBM Certified Database Administrator and a Certified Application Developer for DB2 for Linux, UNIX, and Windows.

Matthias Tschaffler joined IBM at the Silicon Valley Lab in 2000. At SVL he worked on the IBM DB2 Text Information Extender and IBM DB2 Warehouse Manager Connector for the Web product deliverables. After transferring to IBM Germany in 2002, Matthias worked on the EIP Information Mining and Information Integration for BPEL on WebSphere Process Server product deliverables. He currently works as an advisory software engineer in the IBM Tivoli OMEGAMON XE for DB2 Performance Expert, where he is acting as the technical development lead. He has been driving the end-to-end SQL monitoring feature (Extended Insight) development effort for DB2 z/OS back ends from an architectural and deeply technical perspective.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Prerequisites
• Preparing the Extended Insight setup
• Extended Insight setup
• Validating the Extended Insight setup
• Tracing the Extended Insight client
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers