Configure and monitor InfoSphere Warehouse with Optim Performance Manager Extended Insight

Optim™ Performance Manager Extended Edition provides end-to-end database transaction response time monitoring capability for IBM® InfoSphere® Warehouse applications with its Extended Insight capability. This capability gives you insight into the transaction and SQL statement response time metrics of a database application throughout all layers of the software stack; from the time that the SQL is issued in the application, and through the network and database server. Special support is available for InfoSphere Warehouse SQL Warehousing Tool (SQW) applications by recognizing transactions and SQL statements from InfoSphere Warehouse Application Server automatically. This article provides detailed information for installing, configuring, and validating the Optim Performance Manager Extended Insight feature for InfoSphere Warehouse applications.

Overview

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.

Extended Insight can identify workload generated by InfoSphere Warehouse. The identified workload's activities can be grouped and filtered by InfoSphere-specific connection attributes like SQW Details or SQW Application Server.

This article describes the prerequisites and steps you have to perform to set up Extended Insight for InfoSphere Warehouse. The article is based on Optim Performance Manager Extended Edition V4.1.1 and InfoSphere Warehouse 9.7.3 on DB2 LUW 9.7 FP 4 database.

Prerequisites

Before you set up Extended Insight on your InfoSphere Warehouse 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 your database servers that you want to monitor, as well as your InfoSphere Warehouse 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
Screen capture shows how to specify the Extended Insight communication ports

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 as shown in Figure 2.

Figure 2. Extended Insight communication port specification from OPM Console
Screen cap: Shows the OPM web console

(View a larger version of Figure 2.)

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 InfoSphere Warehouse 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.


InfoSphere Warehouse and sample applications

For this article, the InfoSphere Warehouse installation that you plan to monitor must be at version 9.7.3 (although Extended Insight is supported for 9.7.1 and higher). The article demonstrates the working of InfoSphere Warehouse using the SQL Warehousing Tutorial (the sample project you can create with InfoSphere Warehouse 9.7.3 Design Studio). The InfoSphere Warehouse tutorial has to be finished before starting the Optim Performance Manager Extended Insight tutorial described in this document.

The SQL Warehousing sample project is a data warehousing project that contains a data model and the complete set of SQL warehousing data flows and control flows that you can create in the SQL Warehousing Tutorial, which you can find in the Resources section. Data flows and control flows are typically used to build and maintain database tables that support data warehousing applications. Data flows define the SQL-based data movement and transformation activities that run in a DB2 database. Control flows enforce the order in which data flows run and include business logic for handling failures and other data processing conditions.


Configuring the InfoSphere Warehouse Sample data source for monitoring in Optim Performance Manager

Do the following to configure the InfoSphere Warehouse sample database, GSDB, for monitoring Optim Performance Manager.

  1. Add it in the Manage Database Connections view by clicking the Add button, filling in the form, and saving your changes.
  2. After you have added the connection, you need to configure it. Click the Configure Monitoring button to open the Configure Monitoring wizard.
  3. On the first page of the Configure Monitoring wizard, you need to enter administrative credentials for the GSDB database and ensure that the database time zone is correct.
  4. You can choose from multiple monitoring templates available in the Use predefined template drop-down list; for InfoSphere Warehouse, it's best to use BI production system with low overhead or BI production system with all details. The difference between the two is that the BI production system with low overhead configuration gathers data about active SQL statements with a 5-minute interval (as opposed to a 2-minute interval in BI production system with all details configuration), and does not collect tablespace statistics, WLM statistics, or detailed information about all executed dynamic SQL statements. As shown in Figure 3, the BI production system with low overhead template is selected and will be further customized with Extended Insight settings.
    Figure 3. Configuration of GSDB database monitoring with BI template
    screen cap: Configuring general monitoring settings
  5. After this selection, proceed to Step 2 of the wizard by clicking Next. This brings you to the detailed monitoring configuration. The Extended Insight monitoring is already enabled for you. Enter detailed settings for Extended Insight monitoring to enable collection of statement and transaction metrics on the data server as shown in Figure 4.
    Figure 4. Configuration of Extended Insight for GSDB database
    screen cap: entering details on collection of monitoring data
    Note: The collection of statement and transaction metrics on the data server is only possible when you monitor a DB2 server version 9.7 FP1 or higher.
  6. Continue to the end of the monitoring configuration wizard, reviewing the monitored database configuration and monitoring authorization pages. Finally, click Finish to save your configuration. When it is saved, you can view monitoring data for your InfoSphere Warehouse Samples database (GSDB) in the Inflight dashboards.

Extended Insight installation and configuration on the InfoSphere Warehouse system

Install the IBM Optim Performance Manager V4.1.1 Client Software package that matches the operating system of your InfoSphere Warehouse Application Server 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
  1. Optim Performance Manager Extended Insight must be installed on the same server as the InfoSphere Warehouse Application Server. During installation you have to provide the installation path which as shown in Figure 5. The following steps use <eiInstallationPath> to represent the installation path.
    Figure 5. Installation path for IBM Optim Performance Manager V4.1.1 Client Software installation
    screen cap: This shows the installation path client software
  2. The installation finishes with the screen shown in Figure 6.
    Figure 6. Summary panel for IBM Optim Performance Manager V4.1.1 Client Software installation
    screen cap: This shows the summary info for IBM Optim Performance Manager
  3. Ensure that the Open the configuration tool check box is selected.
  4. After you click Done, the configuration tool is opened. In the first step choose the type of application you want to monitor with Extended Insight. For InfoSphere Warehouse applications choose the JDBC application type and ensure the WebSphere applications check box is cleared as shown in Figure 7.
    Figure 7. Configuration of Extended Insight for JDBC applications
    screen cap: This shows the configuration of Extended Insight for JDBC applications
  5. From the Optim Performance Manager Server URL panel shown in Figure 8, enter the fully-qualified host name or IP address of the server on which Optim Performance Manager server is installed. Additionally, provide 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 8. Configuration of Extended Insight server host and ports
    screen cap: This shows hostname and port for configuration of Extended Insight server.
  6. If 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.log for the following message to identify the right port: The Extended Insight controller server is started on port xxx.
  7. Use the panel, as shown in Figure 9, to verify that the database that is accessed by your InfoSphere Warehouse 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 connection.
    Figure 9. Monitored database identifiers for OPM Server communication test
    screen cap shows how to enter database hostname, database port, and database name
  8. 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.
  9. On UNIX and Linux, you can use the OPMstatus.sh command from the <OPM install dir>/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.
  10. If you see a message stating that the database is configured for monitoring, click Next and review the listed summary information as shown in Figure 10. Then click Done to finish the configuration.
    Figure 10. Figure 10: Summary of Extended Insight Client configuration for JDBC applications
    screen cap: This shows the completion messages summary for configuration of JDBC applications
  11. The last step to configure InfoSphere Warehouse for Extended Insight is a manual step. You have to copy configured files to the InfoSphere Warehouse Application Server (which is WebSphere Application Server installed as part of InfoSphere Warehouse).
    • Copy file <eiInstallationPath>\pureQuery\pdq.jar into <WebSphereInstallationDir>\AppServer\profiles\AppSrv01\IBMDataTools\dscomponents\ExtendedInsight\PureQuery_2.2.1\lib"
    • Copy file <eiInstallationPath>\pureQuery\pdqmgmt.jar into <WebSphereInstallationDir>\AppServer\profiles\<AppSrv> \IBMDataTools\dscomponents\ExtendedInsight\PureQuery_2.2.1\lib"
    • Copy file <eiInstallationPath>\pureQuery\pdq.properties into <WebSphereInstallationDir>\AppServer\profiles\<AppSrv> \IBMDataTools\dscomponents\ExtendedInsight\PureQuery_2.2.1\etc"
    • Restart the WebSphere Application Server.
  12. In the Previous steps ensure the following:
    • <WebSphereInstallationDir> is the path where WebSphere Application server hosting the InfoSphere Warehouse services and applications is installed.
    • <AppSrv> is the name of the application server hosting the InfoSphere Warehouse services and applications.

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 InfoSphere Warehouse applications if they execute database transactions.


Viewing the Extended Insight monitoring data for InfoSphere Warehouse control flows

  1. Now you can generate some workload by scheduling warehousing application on InfoSphere Warehouse Administration Console as shown in Figure 11.
    Figure 11. Scheduling sample control flow as workload to be monitored
    screen cap: SQL Warehousing control flow schedules

    (View a larger version of Figure 11.)

  2. After the workload is successfully executed look at the monitoring data on the Extended Insight dashboard of the Optim Performance Manager web console.
  3. Click the Activate button and select the SQW groups as shown in Figure 12 to enable predefined workload cluster groups for InfoSphere Warehouse.
    Figure 12. Enabling predefined InfoSphere Warehouse workload cluster groups
    screen cap: Shows the activate Workload Cluster Groups
  4. After you click OK, you will see the activated groups in the dashboard. Expand the SQW applications and flows group to view the control flows being executed as shown in Figure 13.
    Figure 13. Extended Insight dashboard with expanded SQW workload cluster group
    screen cap: Shows the dashboard with performance graphics

    (View a larger version of Figure 13.)

  5. The metrics for two control flows (defined according to the InfoSphere Warehouse tutorial) are enlisted in two separate clusters. You can choose any cluster (for example, populate_time_and_order_fact) and open details for it by double-clicking it, or by clicking the Open Details button. The Extended Insight dashboard presents details for the workload generated by the chosen cluster (control flow) as shown in Figure 14.
    Figure 14. Details of workload generated by control flow populate_time_and_order_fact
    screen cap: Shows the dashboard showing average server time per transaction

    (View a larger version of Figure 14.)

  6. Now you can sort executed statements by Average number of returned rows and pick up the one which returns the biggest volume of rows and see its details as shown in Figure 15.
    Figure 15. Details for top statement
    screen cap: shows the dashboard and statements

    (View a larger version of Figure 15.)

Construct complex workload cluster group

You can construct more complex workload cluster group based on the InfoSphere Warehouse specific connection attributes. For example to analyze workload only from one SQW Application Server you can create a workload cluster group by following these steps.

  1. Go to the Extended Insight dashboard.
  2. Click the New button. Alternatively you can choose New cluster group from the context menu.
  3. Specify the name and description on the first panel with meaningful information as shown in Figure 16.
    Figure 16. New cluster group name and description
    screen cap: Shows the input fields for new cluster group name and description

    (View a larger version of Figure 16.)

  4. Click Next.
  5. Choose InfoSphere Warehouse from the Type of workload cluster group drop down list.
  6. Choose SQW Details in the Cluster by Connection Attributes column. Choose SQW application server in the Filter the Workload column.
  7. Type the IP address of your SQW Application Server into the filter field.
    Figure 17. Definition of new workload cluster group
    screen cap: Shows info about setting attributes and filter criteria

    (View a larger version of Figure 17.)

    You can test your filter on already-collected metrics by pressing the Refresh button. Clusters should appear in the table at the bottom of the screen.

  8. Click the Finish button.

After these steps a workload cluster group containing clusters which only contain data from your chosen SQW Application Server is displayed on the Extended Insight dashboard as shown in Figure 18.

Figure 18. Custom workload cluster group on Extended Insight dashboard
screen cap: Shows the Extended Insight analysis dashboard

(View a larger version of Figure 18.)


Tracing Extended Insight client

Enabling Extended Insight client tracing helps to determine the root cause if no Extended Insight data from your InfoSphere Warehouse applications is arriving at the Optim Performance Manager repository server.


How to enable the Extended Insight client trace

In the pdq.properties file that you have copied into InfoSphere Warehouse, add the following entries:

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

For example, the specified trace filename could be C:/temp/pdq.trc.

The pdq.properties file is located in folder: <WebSphereInstallationDir>\AppServer\profiles\<AppSrv>\IBMDataTools\dscomponents\ExtendedInsight\PureQuery_2.2.1\etc.

Restart WebSphere Application Server to ensure that the new properties are used, and ensure that the InfoSphere Warehouse 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 in Listing 1.

Listing 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 string controller server in the trace file. For a successful connection to the controller server, you will see information like that shown in Listing 2.

Listing 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/9.152.204.37:60000
...
[pdq][Time:2011-01-20-15:15:12.000] Successfully connected to a controller 
server at OPMEIDEMO.boeblingen.de.ibm.com/9.152.204.37:60000

For an unsuccessful connection (for example, due to a firewall issues) you would see information similar to that shown in Listing 3.

Listing 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/9.152.204.37:60000

Is data being sent to Optim Performance manager server?

To verify that transaction and statement response time data is sent to the Optim Performance Manager server, search for the string Monitor payload in the trace file as shown in Figure 19. Note that the data is sent every minute in an aggregated format, therefore the trace must run longer than one minute for you to see these payload messages.

Figure 19. Payload information in trace file (output formatted)
This screen capture shows the payload info for the trace file

(View a larger version of Figure 19.)


Conclusion

As demonstrated by this article you are now able to install Optim Performance Manager Extended Insight client and configure it to monitor transaction and SQL statement response times of your InfoSphere Warehouse 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 InfoSphere Warehouse applications that access DB2 data.


Acknowledgment

Thank you to Ute Baumbach for her contributions and review of this article.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, or use a product in a cloud environment.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Big data and analytics
ArticleID=753490
ArticleTitle=Configure and monitor InfoSphere Warehouse with Optim Performance Manager Extended Insight
publish-date=08252011