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.
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
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
(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.
- Add it in the Manage Database Connections view by clicking the Add button, filling in the form, and saving your changes.
- After you have added the connection, you need to configure it. Click the Configure Monitoring button to open the Configure Monitoring wizard.
- 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.
- 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
- 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
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.
- 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
- 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
- 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
- Ensure that the Open the configuration tool check box is selected.
- 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
- 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
- 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
- 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
db2pesrv.logfor the following message to identify the right port:
The Extended Insight controller server is started on port xxx.
- 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
Figure 9. Monitored database identifiers for OPM Server communication test
- 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 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.
- 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
- 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
- Copy file
- Copy file
- Restart the WebSphere Application Server.
- Copy file
- 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
- Now you can generate some workload by scheduling warehousing
application on InfoSphere Warehouse Administration Console as shown in
Figure 11. Scheduling sample control flow as workload to be monitored
(View a larger version of Figure 11.)
- After the workload is successfully executed look at the monitoring data on the Extended Insight dashboard of the Optim Performance Manager web console.
- Click the Activate button and select the SQW groups
as shown in Figure 12 to enable predefined workload cluster groups for
Figure 12. Enabling predefined InfoSphere Warehouse workload cluster groups
- 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
(View a larger version of Figure 13.)
- 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
(View a larger version of Figure 14.)
- 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
(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.
- Go to the Extended Insight dashboard.
- Click the New button. Alternatively you can choose New cluster group from the context menu.
- 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
(View a larger version of Figure 16.)
- Click Next.
- Choose InfoSphere Warehouse from the Type of workload cluster group drop down list.
- Choose SQW Details in the Cluster by Connection Attributes column. Choose SQW application server in the Filter the Workload column.
- Type the IP address of your SQW Application Server into the filter
Figure 17. Definition of new workload cluster group
(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.
- 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
(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:
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
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
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/22.214.171.124:60000 ... [pdq][Time:2011-01-20-15:15:12.000] Successfully connected to a controller server at OPMEIDEMO.boeblingen.de.ibm.com/126.96.36.199: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/188.8.131.52: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
Figure 19. Payload information in trace file (output formatted)
(View a larger version of Figure 19.)
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.
Thank you to Ute Baumbach for her contributions and review of this article.
- Link to the SQL Warehousing tutorial.
- See how to validate your CLI client installation and the PQCMX setup in the Configure and monitor CLI applications with Optim Performance Manager Extended Insight tutorial.
- Learn how to Troubleshoot client installation or configuration in the Configure and monitor CLI applications with Optim Performance Manager Extended Insight tutorial.
- Get more details about configuring Extended Insight in the IBM Redbooks publication Optim Performance Manager.
- In the Optim family 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, or use a product in a cloud environment.
- 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.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.