IBM InfoSphere Optim Performance Manager is a web-based database monitoring solution that helps you resolve your database performance issues with a guided approach.
InfoSphere Optim Performance Manager is designed for individuals responsible for the overall performance, health, and availability of their DB2 for Linux®, UNIX®, and Windows® data servers — typically a database administrator (DBA) or application owner. InfoSphere Optim Performance Manager monitors DB2 data servers by gathering performance data and determining if any key performance indicators are exceeding acceptable thresholds. By constantly monitoring the system, InfoSphere Optim Performance Manager can help detect potential performance problems before users are affected and service-level agreements (SLAs) are breached. InfoSphere Optim Performance Manager uses a repository of historical performance data for problem prevention, trend analysis, customizable reporting, and growth planning.
The Extended Insight feature provides end-to-end database performance monitoring for Java™ technology, CLI, and .NET® 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 SAP 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 recognizes transactions coming from an SAP Server automatically and groups them by SAP application server host name, SAP user, SAP source module, or SAP transaction. For all characteristics of each attribute, you can see the breakdown of the average response time into the times spent in the SAP application server, in the database client, and in the database server. For the database server time spent, you will get a detailed breakdown into such values as average lock wait time, sort processing time, and I/O processing time. This helps to identify problematic SAP Application Server machines, specific problematic applications, or users.
This article describes the prerequisites and steps that you have to perform to set up Extended Insight for SAP Server applications. It focuses on SAP CLI applications (SAP Application Server ABAP) set up with the New Client Connectivity. Additionally, it shows you how you can validate the setup and trace it if necessary. This article is based on InfoSphere Optim Performance Manager Extended Edition V5.1.
InfoSphere Optim Performance Manager Editions
InfoSphere Optim Performance Manager is available in several editions. InfoSphere Optim Performance Manager Enterprise Edition provides data server monitoring with integrated problem identification and notification, problem diagnosis, performance reporting, and DB2 Workload Manager configuration.
InfoSphere Optim Performance Manager Extended Edition adds the Extended Insight feature, which provides end-to-end database performance monitoring for Java technology, CLI, and .NET database applications. It gives database professionals the visibility across the application stack to where database workloads are spending their time. It provides problem isolation to the appropriate layer of the stack, extensive introspection into database bottlenecks, and the ability to monitor response-time objectives for the highest-priority workloads.
Figure 1. InfoSphere Optim Performance Manager Editions
Installation architecture of InfoSphere Optim Performance Manager
You can customize the architecture of your InfoSphere Optim Performance Manager system by installing it in one of several ways. IBM recommends that you install InfoSphere Optim Performance Manager on computers that are separate from your production database server and database client computers.
Install InfoSphere Optim Performance Manager on a different computer than the monitored database if you administer several systems, such as test systems, management systems, and production systems, or if InfoSphere Optim Performance Manager and the monitored DB2 database are on different operating systems (for example, InfoSphere Optim Performance Manager is on Windows, and the monitored DB2 database is on Linux).
If you install the product on a computer that is different from the computers with the monitored databases, you do not need to share system resources, such as CPU, memory, and disk space, between the monitored DB2 database and the DB2 instance on which InfoSphere Optim Performance Manager runs.
Figure 2. Installation architecture of InfoSphere Optim Performance Manager
InfoSphere Optim Performance Manager Extended Insight feature includes the InfoSphere Optim Data Tools Runtime Client software package, which allows you to install the Extended Insight client component. You install the Extended Insight client component on each computer where a database application is running that you want to monitor. With the Extended Insight feature, DBAs can extend monitoring to the entire database application system (client, server, and network).
The following figure illustrates the basic architecture of the product and provides a basis for planning your installation.
Figure 3. Installation architecture of IBM InfoSphere Optim Performance Manager Extended Insight
Basic monitoring without Extended Insight feature
For basic monitoring with InfoSphere Optim Performance Manager without the Extended Insight feature, the level of the monitored instance and DB2 CLI driver must be DB2 V9.1 or later.
Before you can install InfoSphere Optim Performance Manager, you must have DB2 V9.1 or later installed to use as its repository database. During the InfoSphere Optim Performance Manager installation, you can specify an existing DB2 instance you want InfoSphere Optim Performance Manager to use or you can create a new DB2 instance.
Monitoring with Extended Insight feature
InfoSphere Optim Performance Manager
If you want to use the Extended Insight feature of InfoSphere Optim Performance Manager for monitoring SAP applications, the level of the monitored instance and the DB2 CLI driver has to be DB2 V9.7 FixPack 2 or later.
SAP Application Server
The following directories and shorthand symbols are referred to in this document:
- DIR_GLOBAL — The SAP system global directory, which must be accessible
on all servers.
- Linux and UNIX: /usr/sap/<SAPSID>/SYS/global
- Windows: \\%DSCDB6HOME%\sapmnt\<SAPSID>\global where
DSCDB6HOME is a variable of user
<sapsid>adm, which contains the name of the host where the global directory is located
- DIR_EXECUTABLE local directory for SAP executables, for example: /usr/sap/<SAPSID>/DVEBMGS00/exe
- In addition, the following variables are used:
<SAPSID>SAP system ID in uppercase
<sapsid>SAP system ID in lowercase
Prepare the SAP machine:
- Ensure that you are using the SAP Kernel 7.00 or later.
- Ensure that the DB2 Runtime Client or DB2 CLI driver is at least of level DB2 V9.7 FixPack 2. If you want to embed InfoSphere Optim Performance Manager Extended Insight into the DB2 CLI driver, the minimum level is DB2 V9.7 FixPack 3.
- Ensure that the db2dsdriver.cfg file is centrally available in
directory DIR_GLOBAL/db6. Your SAP Kernel needs to contain the SAP
note 1414616, which is already guaranteed if you use an SAP Kernel 7.00
or later. To verify that this SAP note is contained, go to transaction
SM51, select one of the application servers by single-clicking, and
click Release Info. Then you can search
for the patch text "client affinity support." During Extended Insight
configuration, additional parameters have to be added to this
configuration file. If the file is not available, you can create it as
<sapsid>adm, using the following sample as a guideline.
Listing 1. Sample for db2dsdriver.cfg
<configuration> <DSN_Collection> <dsn alias="D3D" name="D3D" host="DB2PS-member0" port="5912" /> </DSN_Collection> <databases> <database name="D3D" host="DB2PS-member0" port="5912"> <acr> <parameter name="enableAcr" value="true"/> <parameter name="enableSeamlessAcr" value="true"/> <parameter name="affinityFailbackInterval" value="60"/> <alternate_server_list> <server name="db_server0" hostname="db6s0.wdf.sap.corp" port="5912" /> <server name="db_server1" hostname="db6s1.wdf.sap.corp" port="5912" /> <server name="db_server2" hostname="db6s2.wdf.sap.corp" port="5912" /> <server name="db_server3" hostname="db6s3.wdf.sap.corp" port="5912" /> </alternate_server_list> <clientaffinityroundrobin> <client name="sap_as1" hostname="sapserv1.wdf.sap.corp" /> <client name="sap_as2" hostname="sapserv2.wdf.sap.corp" /> <client name="sap_as3" hostname="sapserv3.wdf.sap.corp" /> <client name="sap_as4" hostname="sapserv4.wdf.sap.corp" /> <client name="sap_as5" hostname="sapserv5.wdf.sap.corp" /> <client name="sap_as6" hostname="sapserv6.wdf.sap.corp" /> <client name="sap_as7" hostname="sapserv7.wdf.sap.corp" /> <client name="sap_as8" hostname="sapserv8.wdf.sap.corp" /> </clientaffinityroundrobin> </acr> </database> </databases> <parameters> <parameter name="CommProtocol" value="TCPIP"/> </parameters> </configuration>
Client connectivity scenarios
The Application Server ABAP (AS ABAP) uses the DB2 Call Level Interface (CLI) API, whereas the Application Server Java (AS Java) uses the JDBC API to access the database.
SAP systems based on releases below SAP NetWeaver 7.0 SR3 used a DB2 client setup, which is referred to as the Old Client Connectivity (OCC). In this scenario, every AS ABAP had its own DB2 Runtime Client installed. All of these clients had to be maintained separately.
Starting with SAP NetWeaver 7.0 SR3, a new DB2 client setup called New Client Connectivity (NCC) was introduced. The DB2 CLI driver (also known as the thin client) is used for AS ABAP instead of the DB2 Runtime Client. The CLI Driver is installed once in a directory shared by all application servers of the SAP system. During the start of an application server, the CLI driver is copied to a local directory. For the DB2 IBM Data Server driver for JDBC and SQLJ of the AS Java, the same mechanism is used.
This article will concentrate on the New Client Connectivity and AS ABAP. To exactly determine the client setup of AS ABAP and to find out the path to the DB2 clients which are used, complete the following steps:
- Log on to the application server on which you want to check the DB2
client set up as user
- Execute the following command:
R3trans -x. (R3trans loads the DB2 library and connects to the database. A log file trans.log is created in the current directory.)
- Open the trans.log file in an editor and search for the term "DB2 library."
If the message "Running with CLI driver" is part of the trans.log file, the NCC setup is configured for the application server. Here is an example output:
Listing 2. NCC setup example output
4 ETW000 [dev trc ,00000] DB2 library successfully loaded DB2 library '/usr/sap/<SAPSID>/SYS/global/db6/<platform>/db6_clidriver/lib/libdb2.so' successfully loaded> ... 4 ETW000 [dev trc ,00000] Running with CLI driver 40239 0.468024 ...
If the DB2 Runtime Client is used, the output looks as follows:
... 4 ETW000 [dev trc ,00000] DB2 library successfully loaded DB2 library '/db2/db2<dbsid>/sqllib/lib/libdb2.so' successfully loaded
This means that the system uses the OCC setup.
To check the DB2 Client version for AS ABAP, you can use the
db6level command, which has been available to the
<sapsid>adm user since SAP
release 7.0. Listing 3 shows an output example.
db6level sample output
bash-3.00$ db6level -v Trace CB: reasonCode=4 reasonText=DB2NOEXITLIST not set in environment Trace CB: reasonCode=4 reasonText=putenv(DB2NOEXITLIST=ON) Trace CB: reasonCode=4 reasonText=DB2COUNTRY not set in environment Trace CB: reasonCode=4 reasonText=putenv(DB2COUNTRY=1) Trace CB: reasonCode=4 reasonText=DB2DB6_FORCE_RUNTIME_CLIENT not set in environment Trace CB: reasonCode=4 reasonText=DB2DB6_FORCE_CLI_DRIVER not set in environment Trace CB: reasonCode=4 reasonText=CLI driver first Trace CB: reasonCode=4 reasonText=DB2_CLI_DRIVER_INSTALL_PATH not set in environment Trace CB: reasonCode=4 reasonText=default DB2_CLI_DRIVER_INSTALL_PATH= /usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver Trace CB: reasonCode=4 reasonText=path /usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver/lib64 does not exist or cannot be accessed Trace CB: reasonCode=4 reasonText=path /usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver/lib exists and it is a directory Trace CB: reasonCode=4 reasonText=use CLI driver from /usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver/lib Trace CB: reasonCode=4 reasonText=First check and set DB2CLIINIPATH Trace CB: reasonCode=4 reasonText=DB2CLIINIPATH not set in environment Trace CB: reasonCode=4 reasonText=putenv(DB2CLIINIPATH=/usr/sap/OP1/SYS/global/db6) Trace CB: reasonCode=4 reasonText=Second check and set DB2DSDRIVER_CFG_PATH Trace CB: reasonCode=4 reasonText=DB2DSDRIVER_CFG_PATH not set in environment Trace CB: reasonCode=4 reasonText=putenv(DB2DSDRIVER_CFG_PATH=/usr/sap/OP1/SYS/global/db6) Trace CB: reasonCode=1 reasonText=successfully loaded DB2 library '/usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver/lib/libdb2.a(shr_64.o)' Trace CB: reasonCode=2 reasonText=successfully loaded symbol 'SQLGetInfoW' from library 'libdb2.a(shr_64.o)' 09.07.0003
In this example, the ABAP kernel uses the DB2 CLI Driver from Version 9.7 with FixPack 3 (09.07.0003). The system uses the NCC setup. Because db6level also checks the library loading sequence, you should use this tool to determine the DB2 Client version whenever possible.
Installing and configuring InfoSphere Optim Performance Manager
You can install InfoSphere Optim Performance Manager by running the installation wizard, by doing a console mode installation, or by doing a silent installation.
From the directory of the installation image for InfoSphere Optim Performance Manager, run the installation program by executing the script IOPM.server.v184.108.40.206.install-on-aix.sh You can append -i console to the command to run the installation program in console mode. NOTE: On Linux and UNIX, you must run the commands in the bash shell.
On AIX, you start the installation by issuing the following command:
./IOPM.server.v220.127.116.11.install-on-aix.sh –I console. (For further guidance on the installation procedure, please refer to the
InfoSphere Optim Performance Manager Installation documentation.)
Activating InfoSphere Optim Performance Manager Extended Insight license
To activate Extended Insight, run the license activation kit for an edition of IBM InfoSphere Optim Performance Manager that contains Extended Insight — Extended Edition or the Extended Insight feature. If you purchased IBM InfoSphere Optim Performance Manager Extended Edition, you can activate the license during the product installation.
The activation tool prompts you to provide unused, open port numbers for the Extended Insight ports to use for the communication between the InfoSphere Optim Performance Manager server and Extended Insight clients. The Extended Insight ports are two consecutive ports. The default values are 60000 and 60001. The ports are stored in the repository database. You can review and change the ports later in the Performance Manager Server window in the web console.
Tip: Remember the port numbers you specify because you will need to provide them when you configure Extended Insight on the client computer.
The following figure shows the installer panel used to specify the communication ports for Extended Insight.
Figure 4. Extended Insight communication port specification
You can review the specified communication ports or change them later using the Performance Manager Server panel in the InfoSphere Optim Performance Manager web console.
After you have installed and activated InfoSphere Optim Performance Manager Extended Insight, make sure the repository server and the web console are started.
Configuring InfoSphere Optim Performance Manager
After you install InfoSphere Optim Performance Manager, you must configure monitoring by adding or importing database connections, managing monitoring profiles, defining monitoring authorizations, and configuring partition sets. You can configure multiple databases for monitoring.
Before you can add or import data source connections, you need the following information:
- The DB2 database name
- The host name
- The port number
- The user ID and password of a user with at least CONNECT authority on the database
To add a data source connection, open the Databases panel in the InfoSphere Optim Performance Manager web console, click Add, then complete the Add Database Connection wizard. To collect monitoring data from a database, you enable monitoring profiles or specify a predefined system template. When you configure a monitoring profile, you specify the details of the data you want to collect. To configure a data source for monitoring, select the database for which you want to configure monitoring on the Databases panel, then click Configure Monitoring. The Configure Monitoring wizard opens.
Use one of the predefined system templates for SAP systems as a starting point for your monitoring configuration, as described in Configuring monitoring for SAP.
Figure 5. Select SAP predefined templates during configuration of monitoring
If you are monitoring an SAP system, you should choose a custom table space for the event monitor data in the DB2 event monitor configuration in Step 2 of the configuration wizard. Select the SAP table space SAPTOOLS or create a separate table space for InfoSphere Optim Performance Manager and select this one. If you are monitoring a partitioned database, make sure that a table space spanning all partitions for which you would like to collect event monitor data is selected.
Figure 6. Configure DB2 event monitor settings
To collect all flavors of InfoSphere Optim Performance Manager Extended Insight data, including detailed information about the time spent in the data server and for SQL statements, ensure in Step 2 of the configuration wizard that the Extended Insight monitoring profile is turned on with all options. If you selected an SAP predefined template "with all details," all options for the Extended Insight monitoring profile are selected.
Figure 7. Configure Extended Insight Monitoring profile
In Step 3 of the configuration wizard, all changes that will be made on the monitored system are shown. Depending which predefined system templates you have chosen, InfoSphere Optim Performance Manager will turn on DB2 monitoring switches, create event monitors on the monitored system, and will access certain monitoring functions and views.
Figure 8. View resulting DB2 settings for SAP low overhead templates
Figure 9. View resulting DB2 settings for SAP all details templates
Installing and configuring Extended Insight Client software
Use the InfoSphere Optim Data Tools Runtime Client media to install and configure the InfoSphere Optim Performance Manager Extended Insight client software, which has to be available on each SAP application server of your SAP system.
For an SAP environment configured with New Client Connectivity, it is recommended to embed the Extended Insight client software so the libraries needed are copied to the local EXE directory (DIR_EXECUTABLE) together with the DB2 CLI driver. The Extended Insight client software can be installed in one place and will be available to all SAP instances of the SAP system.
This Data Tools Runtime Client installation runs in console mode. The Extended Insight client software will be installed into the DIR_GLOBAL/db6/<platform>. For the configuration, you will need to make manual changes to the db2dsdriver.cfg file and to the clidriver.lst file. If you upgrade the DB2 CLI driver, the whole db6_clidriver directory will be deleted first. This does not impact the Extended Insight client installation directory, because it is located one level higher.
Your DB2 client package must be at level DB2 9.7 FixPack 3 or higher for this type of installation. If the SAP application servers are running on several platforms and there are several platform directories below DIR_GLOBAL/db6/, you have to install the Extended Insight client software for each platform.
Complete the following steps to install the InfoSphere Optim Performance Manager Extended Insight client software with the Data Tools Runtime Client media into a directory parallel to an existing DB2 client package on an AIX system:
- Launch the console mode installation as an SAP user by executing the
following command from the directory of the Extended Insight
installation image. The SAP user has the name
./IBM_Data_Tools_Runtime_Client_aix.bin -i console
- Proceed through the Language Selection, the Introduction panel, and the Software License Agreement panel.
- Specify DIR_GLOBAL/db6/<platform>/IBM_Data_Tools as the
Figure 10. Choose install directory section
- Review the information shown on the Pre-Installation Summary panel
and confirm to proceed with the installation. It will take a few
minutes for the installation to complete. You will see an indicator
that shows the progress. Press Enter to exit the
Figure 11. Confirm installation summary
- After the InfoSphere Optim Performance Manager Extended Insight client
software is installed, you must configure it for each application
you want to monitor with Extended Insight. To launch the configuration
./cfgtool.sh -i consolein a bash shell from the configuration subdirectory of the Data Tools Runtime Client install directory (DIR_GLOBAL/db6/<platform>/IBM_Data_Tools/configuration).
- Select to configure the client software for IBM InfoSphere Optim Performance Manager Extended Insight.
Figure 12. Select product to configure
- Select applications to configure DB2 Call Level Interface applications.
Figure 13. Select applications to configure
- Enter the host name or IP address of the
InfoSphere Optim Performance Manager server and the port number of the
Extended Insight controller specified during activation of
Extended Insight or during the installation of InfoSphere Optim
Performance Manager with an Extended Edition license. If you do not
know the port number, you can obtain it from the Performance Manager
Server window in the web console.
Figure 14. Select host name or IP address and port number
- Identify the db2dsdriver.cfg file to configure.
Because the db2dsdriver.cfg file should be in the global
directory, enter the following path:
Figure 15. Location of db2dsdriver.cfg file
- Optional: Validate the connection to the database configured for monitoring.
Figure 16. Validate database connection
- Review the information in the configuration summary and press
Enter to proceed with the configuration.
Figure 17. Review the information of the installation summary
- Open the d2dsdriver.cfg file in the directory
DIR_GLOBAL/db6/. You will see that during configuration, the path to
the pqcmx library was set to an absolute path. The copy mechanism of
the DB2 CLI driver in an SAP environment requires a relative path.
Therefore, you must set the path to the pqcmx library to the following
Figure 18. Modify db2dsdriver.cfg file
- Add a new line to the clidriver.lst file with the name of the Data
Tools Runtime Client installation directory into which you installed
the InfoSphere Optim Performance Manager Extended Insight client
software. This clidriver.lst file is in the
DIR_GLOBAL/db6/<platform> directory. You must add the
installation directory as a second line to this file:
With this change, the IBM_Data_Tools installation directory will now be copied to the local executable directory during application server startup.
This installation procedure has to be done for every SAP system for which you would like to configure the Extended Insight feature.
you can install the InfoSphere Optim Performance Manager Extended Insight
client software only once on a central share, which needs to be mounted
on all SAP instances of the SAP systems for which you would like to configure the
Extended Insight feature. The configuration tool will be launched with
./cfgtool.sh -i console in a bash
shell from the configuration subdirectory of the Data Tools Runtime
Client install directory. There is no need to change the absolute path in
the db2dsdriver.cfg file or to adapt the clidriver.lst file. This single
InfoSphere Optim Performance Manager Extended Insight client software
installation can be used to configure several SAP systems.
Further configuration steps
After you finish the installation and configuration of the Extended Insight Client, you must restart your SAP application servers so the changes in the db2dsdriver.cfg file are picked up.
Validation of the Extended Insight Client software installation and configuration
If your DB2 client package is at DB2 9.7 FixPack 2 or higher, you can validate the Extended Insight client configuration for your SAP environment to ensure that Extended Insight data can be collected by InfoSphere Optim Performance Manager.
Call the validate routine using the following command:
db2cli validate –database mydb:mydbserver:portnumber,
where mydb, mydbserver, and portnumber are the database name, host name,
and port number of your monitored database. For example:
./db2cli validate -database OP1:db6lpar7:50099.
Run this command as the SAP user from the bin directory of your DB2 client
package. The SAP user has the name
Before running the
db2cli command on Linux or UNIX, you might have to
adapt environment variables. Try the command first and if it fails, set
DB2_CLI_DRIVER_INSTALL_PATH variable to the path of the DB2 client
package. If you are using the New Client Connectivity for which the
db2cli.ini file and the db2dsdriver.cfg file are located in the directory
DIR_GLOBAL/db6, you have to set the
DB2CLIINIPATH variables to the DIR_GLOBAL/db6 directory as well.
These settings are only necessary to execute the
db2cli validate command
and should be made only temporarily. You do not need to modify login
environments permanently. Depending on the UNIX shell used, you can set
the variables with the export or the
setenv command as shown in the
Listing 4. Set environment variables
export DB2_CLI_DRIVER_INSTALL_PATH=/usr/sap/OP1/SYS/global/db6/AIX_64/db6_clidriver export DB2DSDRIVER_CFG_PATH=/usr/sap/OP1/SYS/global/db6 export DB2CLIINIPATH=/usr/sap/OP1/SYS/global/db6
If you installed the Extended Insight client software into the DIR_GLOBAL/db6/<platform> directory and used a relative path to specify the path to the pqcmx library, you need to add an absolute path temporarily as shown to run the validation command.
Listing 5. Absolute path in db2dsdriver.cfg to specify location of pqcmx library
<parameters> <parameter name="GlobalParam" value="Value"/> <parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000"/> <!--<parameter name="connectionSupervisorLibrary" value="../../IBM_Data_Tools/pureQuery/lib64/pqcmx"/> --> <parameter name="connectionSupervisorLibrary" value="/usr/sap/OP1/SYS/global/db6/AIX_64/IBM_Data_Tools/pureQuery/lib64/pqcmx"/> </parameters>
Listing 6. Output of validation
bash-3.00$ ./db2cli validate -database OP1:db6lpar7:50099 IBM DATABASE 2 Interactive CLI Sample Program (C) COPYRIGHT International Business Machines Corp. 1993,1996 All Rights Reserved Licensed Materials - Property of IBM US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Header : --------------------------------------------------------------------------- [ CLI Driver Version : 09.07.0000 ] [ Informational Tokens : "DB2 v18.104.22.168", "special_25542",IP23092_25542","Fixpack 3" ] [ CLI Driver Type : IBM Data Server Driver For ODBC and CLI ] --------------------------------------------------------------------------- db2dsdriver.cfg Schema Validation: --------------------------------------------------------------------------- The following schema errors found in: /usr/sap/OP1/SYS/global/db6/db2dsdriver.cfg Error at : line 2, column 20 Message : Unknown element 'DSN_Collection' Error at : line 34, column 17 Message : Element 'DSN_Collection' is not valid for content model: '((parameter|client|dsncollection|databases)|parameters)' --------------------------------------------------------------------------- Warning: The schema validation operation completed successfully. The following data source name was not found in the db2cli.ini file: "OP1". CSC Information Section: --------------------------------------------------------------------------- Monitored Database Name: OP1 Monitored Database Server: db6lpar7 Monitored Database Port: 50099 Platform Specific CSC Library Name: /usr/sap/OP1/SYS/global/db6/AIX_64/IBM_Data_Tools/pureQuery/lib64/libpqcmx.a CSC library load: success CSC initialization: success, 3.1 CSC Name: PQCMX CSC Version: '22.214.171.124' 's110330' 'IP23236' '4' PQCMX is attempting to connect to a controller server using the controllerURL property fixed address: 10.17.202.179:64000 with protocol 4. PQCMX successfully connected to a controller server using the controllerURL property fixed address: 10.17.202.179:64000 with a negotiated protocol: 4. PQCMX datasource db6lpar7:50099:OP1 attempting to obtain properties using 3-part client specified address. PQCMX datasource db6lpar7:50099:OP1 obtained properties based on 3-part client specified address. PQCMX datasource 1:db6lpar7:50099:OP1 will use properties resolved after connecting to the controller server. Resolved properties version: 1. Resolved properties: monitorEnabled: 1, monitorServer: 10.17.202.179;192.168.2.179, monitorPort: 64001, monitorLevel: 1, monitorCollectionInterval: 60, clientPollingInterval: 20, supportedKeyTypes: 1,3, clientUserMask: , clientHostnameMask: , applicationNameMask: , clientAccountingInformationMask: , dispatchToken: 30005 PQCMX monitoring for client datasource 1:db6lpar7:50099:OP1 is enabled. PQCMX datasource 1:db6lpar7:50099:OP1 is attempting to connect to monitor server 10.17.202.179;192.168.2.179:64001 with protocol 4. PQCMX datasource 1:db6lpar7:50099:OP1 is successfully connected to monitor server 10.17.202.179:64001 with negotiated protocol 4. Monitoring status: on End CSC Information Section --------------------------------------------------------------------------- The validation completed.
Coexistence and SAP specific setup support for InfoSphere Optim Performance Manager
Monitoring an SAP environment with InfoSphere Optim Performance Manager requires different configuration settings. IBM works with SAP to test the recommendations, and to obtain feedback from customers and consultants that continuously improves and shares the knowledge base.
InfoSphere Optim Performance Manager is designed to coexist with SAP Monitoring tools such as DBA Cockpit or Solution Manager. This means that DBA Cockpit and InfoSphere Optim Performance Manager can collect performance information independently from each other for one SAP database at the same time.
This coexistence is achieved because InfoSphere Optim Performance Manager does not turn off any DB2 monitoring switches for SAP databases regardless of the monitoring configuration you use with InfoSphere Optim Performance Manager. Furthermore, you can easily identify objects in the monitored SAP database because InfoSphere Optim Performance Manager uses its own schema and specific naming conventions.
Configuring monitoring for SAP
For each database that uses InfoSphere Optim Performance Manager to collect monitoring data, you must define a monitoring configuration. Use the following predefined system templates for SAP systems as a starting point for your monitoring configuration:
- SAP Business Information Warehouse production with low overhead
- SAP Business Information Warehouse production with all details
- SAP Enterprise Resource Planning production with low overhead
- SAP Enterprise Resource Planning production with all details
The templates named "with low overhead" set the same configurations for SAP Business Information Warehouse and SAP Enterprise Resource Planning. However, the collected data is evaluated differently. This is achieved with special threshold alert settings for SAP Business Information Warehouse and SAP Enterprise Resource Planning.
Similarly, the templates named "with all details" set the same configurations for SAP Business Information Warehouse and SAP Enterprise Resource Planning and collect more monitoring data. The collected data is evaluated differently due to the special the threshold alert settings for SAP Business Information Warehouse and SAP Enterprise Resource Planning.
Lock event monitor levels
For a database running an SAP workload, it is recommended by SAP to set
the detail level of lock event monitor data collected to
WITHOUT_HIST. The database should not be configured to collect more details on lock events.
Therefore, during configuration, InfoSphere Optim Performance Manager
detects whether the database is running with an SAP workload and disallows
the configuration of the lock event monitor details level in the locking
profile. When a lock event is configured, the corresponding database
configuration parameter is set to the
WITHOUT_HIST value. This is true for
lock events, such as deadlock events, lock timeout events, or lock wait
WITHOUT_HIST setting minimizes the overhead created
when lock events are generated.
DB2 monitor switch settings
SAP requires that the snapshot switches be turned on for a DB2 database regardless of whether the database is monitored by InfoSphere Optim Performance Manager. None of the snapshot switches are turned off by InfoSphere Optim Performance Manager regardless of the monitoring configuration you use.
Controlling event monitors by using watchdog procedures
Watchdog procedures are procedures InfoSphere Optim Performance Manager configures to run automatically on your database. When data is not pruned, the table space can fill unnecessarily, which might affect the performance of your monitored database. These procedures stop the collection of event monitor data when InfoSphere Optim Performance Manager is not reading and pruning collected data due to a network disruption or similar system problem.
Watchdog procedures are registered in the administrative task scheduler of DB2. To activate the administrative task scheduler, DB2 9.5 FixPack 2 or later is required.
Identifying the objects created by InfoSphere Optim Performance Manager in monitored databases
SAP demands that any objects created by third-party products in a database running an SAP workload must be clearly identifiable. Therefore, InfoSphere Optim Performance Manager creates objects in its own schema and has defined naming conventions for the event monitors.
Objects in the InfoSphere Optim Performance Manager schema
You can easily identify the objects in your monitored database created by InfoSphere Optim Performance Manager, such as the table of event monitors. The objects are created in the same schema, and the schema name is OPM.
Naming conventions for event monitors
InfoSphere Optim Performance Manager creates event monitors in a monitored database depending on the configuration of the monitoring profile. The event monitors are not associated with specific schemas. Therefore, InfoSphere Optim Performance Manager uses specific names to identify the event monitors. The names of the event monitors created by InfoSphere Optim Performance Manager in the monitored databases all start with OPM.
Impact of InfoSphere Optim Performance Manager Extended Insight on memory consumption of SAP application servers
If you use the Extended Insight feature, SAP processes will load the additional pqcmx library. Within this library, memory will be allocated to collect statistics for the SQL statements executed. Normally, this does not have a significant effect on the memory consumption of your SAP application servers. However, if your SAP workload executes thousands of statements in one transaction without committing them, the memory consumption of each SAP process might increase by an undesirable degree.
To prevent this effect, a parameter named maxMonitoredStatements was introduced with DB2 client package DB2 9.7 FixPack 4. This parameter can be set in the db2dsdriver.cfg file and specifies the maximum number of statements in a transaction for which monitored statistics are collected. The default value for this parameter is 500. The value of -1 indicates that there is no limit on the number of monitored statements in a transaction.
The following example shows the usage of the parameter in the db2dsdriver.cfg file.
Listing 7. Parameter to limit the maximum number of statements to collect statistics for
<parameter name="connectionSupervisorLibrary" value=""../../IBM_Data_Tools/pureQuery/lib64/pqcmx "/> <parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000,dataSourceLookupInterval=20, maxMonitoredStatements=1000"/> </parameters>
Viewing the Extended Insight monitoring data for SAP applications
Activating the SAP workload groups
Ensure that your configured SAP applications are running and executing database transactions, and that InfoSphere Optim Performance Manager is started. If you successfully installed and configured InfoSphere Optim Performance Manager Extended Insight, you can see the collected monitoring data displayed on the Extended Insight dashboard in the InfoSphere Optim Performance Manager web console.
On the Extended Insight dashboard, you can use workload groups to view transaction response-time data by application, user, or server. Predefined workload groups are available that specify different groupings. To activate the SAP-specific predefined workload groups, click Activate and select the SAP groups as shown below.
Figure 19. Workload groups activation panel
By clicking New, you can add an additional workload group that clusters by SAP source modules. In Step 1, you specify the name of the workload group. In Step 2, you should select SAP on DB2 LUW as type of workload group.
Figure 20. New Workload group panel
Next, select the SAP source module as the clustering connection attribute.
Figure 21. New Workload group: Select cluster attributes
Next, set the response-time threshold for the workload group as shown in the next figure. You can set response-time thresholds for the whole workload group or for individual workload subgroups. This step is optional.
Figure 22. New Workload group: Set response-time thresholds
Viewing data in the Extended Insight dashboard
After you activate the SAP workload groups, you see your SAP applications represented in different workload groups as shown in the following figure. The response-time columns show you the time-spent metrics per SAP user, for example, so you can identify the user with the highest response time easily and determine where the most time is spent. For example, you can see that the transactions run by user andrea had the highest average end-to-end response time and on average spent the most time within the data server.
Figure 23. Extended Insight dashboard overview
Further analysis of the response-time metrics include drill-down to more detailed time-spent metrics, and to the executed SQL statements and their time distribution and execution details.
Figure 24. Extended Insight dashboard drill-down
Troubleshooting the Extended Insight setup
If after a few minutes of monitoring Historical Data with Automatic Refresh turned you do not see any data in the Average Network Time and Average Client Time columns, this is an indication that no Extended Insight data from your SAP applications is arriving at the InfoSphere Optim Performance Manager Repository server.
Verify the following items to find the cause for this problem:
- Ensure that you restarted the SAP Application Server after configuration.
- Ensure that the Extended Insight controller server is started within
the InfoSphere Optim Performance Manager server:
- Check the InfoSphere Optim Performance Manager repository
server log db2pesrv.log for the following message:
The Extended Insight controller server is started on port xxx.
- Check the InfoSphere Optim Performance Manager repository server log db2pesrv.log for the following message:
- Ensure that the Extended Insight client is using the same port number
for the controller server:
- Check the db2dsdriver.cfg file in DIR_GLOBAL/db6 for the
<parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000,dataSourceLookupInterval=20"/>.
- Check the db2dsdriver.cfg file in DIR_GLOBAL/db6 for the parameter
- Ensure that the pqcmx library can be loaded by checking the db2diag.log file as described in the next section.
To verify in the db2diag.log that the correct db2dsdriver.cfg file was configured and that the pqcmx library could be loaded, complete the following steps.
Set the DB2 diagnostic trace to level 4 by adding the following line to the common section of the db2cli.ini file located in the GLOBAL_DIR/db6 directory.
DiagLevel set to 4, you should only start your SAP system or run
R3trans, but do not start your usual SAP workload. If you start your usual
SAP workload, this will result in a very big and fast-growing db2diag.log
If you run
R3trans -x, you will see the following
messages in the db2diag.log file.
Listing 8. Messages in db2diag.log file for process R3trans
2011-12-12-126.96.36.1995797+060 I1451A705 LEVEL: Info PID : 503866 TID : 1 PROC : R3trans INSTANCE: db2op1 NODE : 000 EDUID : 1 FUNCTION: DB2 UDB, Remote Client Config API, rccConfig::getInstance, probe:75 DATA #1 : String, 418 bytes <configuration> <dsncollection> <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"/> </dsncollection> <parameters> <parameter name="GlobalParam" value="Value"/> <parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000"/> <parameter name="connectionSupervisorLibrary" value="../../IBM_Data_Tools/pureQuery/lib64/pqcmx"/> </parameters> </configuration> 2011-12-12-188.8.131.522777+060 I2157A429 LEVEL: Info PID : 503866 TID : 1 PROC : R3trans INSTANCE: db2op1 NODE : 000 EDUID : 1 FUNCTION: DB2 UDB, call level interface, CLI_loadCscLibrary, probe:0 DATA #1 : <preformatted> CSC Initialization succeeded (Load library /x97_data/sapmnt_OP1/OP1/global/db6/AIX_64/ db6_clidriver/lib/../../IBM_Data_Tools/pureQuery/lib64/libpqcmx.a succeeded)
Stand-alone tools like R3trans use the DB2 CLI driver from DIR_GLOBAL/db6.
The AS ABAP uses the CLI driver, which is copied to the local executable
directory. To verify that these processes are started, search the
db2diag.log file for the same messages but for the process
Listing 9. Messages in db2diag.log file for process R3trans
2011-12-12-184.108.40.2065735+060 I52606A707 LEVEL: Info PID : 573466 TID : 1 PROC : disp+work INSTANCE: db2op1 NODE : 000 EDUID : 1 FUNCTION: DB2 UDB, Remote Client Config API, rccConfig::getInstance, probe:75 DATA #1 : String, 418 bytes <configuration> <dsncollection> <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"/> </dsncollection> <parameters> <parameter name="GlobalParam" value="Value"/> <parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000"/> <parameter name="connectionSupervisorLibrary" value="../../IBM_Data_Tools/pureQuery/lib64/pqcmx"/> </parameters> </configuration> 2011-12-12-13.43.16.034992+060 I53314A423 LEVEL: Info PID : 532486 TID : 1 PROC : disp+work INSTANCE: db2op1 NODE : 000 EDUID : 1 FUNCTION: DB2 UDB, call level interface, CLI_loadCscLibrary, probe:0 DATA #1 : <preformatted> CSC Initialization succeeded (Load library /x97_data/usrsap/OP1/DVEBMGS04/exe/ db6_clidriver/lib/../../IBM_Data_Tools/pureQuery/lib64/libpqcmx.a succeeded)
These messages are only shown when the
disp+work processes are started.
These processes are started at SAP application server startup and will
stay in the connection pool afterward. Therefore, you have to restart
your SAP application servers to see the messages for the
By following the steps outlined here, you can install InfoSphere Optim Performance Manager Extended Insight client and configure it to monitor the transaction and SQL statement response times of your SAP 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 a web-based user interface that provides overview, alert, and diagnostic dashboards to simplify root-cause analysis in SAP applications that access DB2 data.
- Learn more in the IBM Redbooks® publication titled "IBM Optim Performance Manager for DB2 for Linux, UNIX, and Windows."
- Watch a demo "IBM InfoSphere Optim Performance Manager 5.1, Part 1: Install" to learn about InfoSphere Optim Performance Manager 5.1 architecture and packaging, and how to install the product.
- The article "Configure and monitor InfoSphere Warehouse with Optim Performance Manager Extended Insight" will help you get started with Optim in an InfoSphere Warehouse environment.
- In the article "End-to-end database monitoring with Optim Performance Manager Extended Insight," learn how to identify, analyze, and resolve problems from application layer to database.
- In the InfoSphere Optim data lifecycle management area on developerWorks, get the resources you need to advance your skills on the InfoSphere Optim family of tools and solutions.
- 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.
Dig deeper into Information management on developerWorks
Experiment with new directions in software development.
Read and subscribe for the best and latest technical info to help you deal with your development challenges.
Software development in the cloud. Register today and get free private projects through 2014.
Evaluate IBM software and solutions, and transform challenges into opportunities.