Configure and monitor SAP applications with InfoSphere Optim Performance Manager Extended Insight

End-to-end database transaction management for your SAP applications

IBM® InfoSphere® Optim™ Performance Manager is a performance analysis and tuning tool for managing DB2® systems by using a web interface and helps organizations resolve emergent database problems before they impact the business. InfoSphere Optim Performance Manager Extended Edition provides end-to-end database transaction response-time monitoring for SAP applications with its Extended Insight capability. This provides insight into the transaction and SQL statement response-time metrics of a database application throughout all layers of the software stack, from the time that the SQL is issued in the application through the network and database server. This article provides detailed information for installing, configuring, and validating the InfoSphere Optim Performance Manager Extended Insight feature for SAP CLI applications set up with the New Client Connectivity.

Share:

Andrea Kapahnke (andreaka@de.ibm.com), Software Test Engineer, IBM

Andrea KapahnkeAndrea Kapahnke is a software test engineer working on the InfoSphere Optim Performance Manager team at the IBM Research and Development lab in Boblingen. She started working for IBM as a software tester for DB2 Utilities on z/OS in 2005. Besides testing, she also works on the integration of InfoSphere Optim Performance Manager with SAP.



16 February 2012

Introduction

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
Image shows Optim Performance Manager Extended Edition, Optim Performance Manager, and Data Studio Health Monitor

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
Diagram shows DB2 LUW with OPM on left, and web browser, monitored databases, and SAP system ERP on right

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
Image shows DB2 LUW with repository db on left, web browser, monitored SAP databases, and SAP System ERP on right

Prerequisites

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:

  1. Log on to the application server on which you want to check the DB2 client set up as user <sapsid>adm.
  2. 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.)
  3. 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.

Listing 3. 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.v5.1.0.0.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.v5.1.0.0.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
Image shows to specify first and second ports

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
Image shows to configure general monitoring settings with predefined template

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
Image shows selecting custom table space SYSTOOLSPACE

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
Image shows to collect extended insight data

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
Image shows monitor switches, event monitors, accessed monitoring functions and views, and configuration settings
Figure 9. View resulting DB2 settings for SAP all details templates
Image shows resulting DB2 settings for monitor switches, event monitors, functions and views, and configuration settings

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 <sapsid>adm. ./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 installation directory.
    Figure 10. Choose install directory section
    Image shows default directory
  • 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 installation.
    Figure 11. Confirm installation summary
    Image shows to review information before you continue
  • 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 tool, run ./cfgtool.sh -i console in 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
    Image shows to select pOPM EE or OCM
  • Select applications to configure DB2 Call Level Interface applications.
    Figure 13. Select applications to configure
    Image shows select DB2 CLI, JDBC, or WebSphere apps
  • 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
    Image shows to 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: DIR_GLOBAL/db6/db2dsdriver.cfg.
    Figure 15. Location of db2dsdriver.cfg file
    Image shows options for location of db2dsdriver.cfg file
  • Optional: Validate the connection to the database configured for monitoring.
    Figure 16. Validate database connection
    Image shows to select to continue without verifying, or verify the database
  • Review the information in the configuration summary and press Enter to proceed with the configuration.
    Figure 17. Review the information of the installation summary
    Image shows to review selected host name, port, and CLI app configuration
  • 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 relative path: ../../IBM_Data_Tools/pureQuery/lib64/pqcmx.
    Figure 18. Modify db2dsdriver.cfg file
    Image shows to modify path in 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:
    db6_clidriver
    IBM_Data_Tools

    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.

Alternatively, 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 the command ./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 <sapsid>adm.

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 the 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 DB2_CLI_DRIVER_INSTALL_PATH and 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 following example.

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 v9.7.0.3", "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: '9.7.0.4' '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 events. The 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
Image shows SAP application servers, SAP users, and SAP transactions selected

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
Image shows selecting SAP on DB2 LUW

Next, select the SAP source module as the clustering connection attribute.

Figure 21. New Workload group: Select cluster attributes
Image shows New Workload group: SAP source module selected

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
Image shows default 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
Image shows average response time, data server time, network time, and client time for each user

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
Dashboard shows graph, SQL statements, and detail area for average end-to-end response time

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.
  • 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 <parameter name="connectionSupervisorProperties" value="controllerURL=10.17.202.179:64000,dataSourceLookupInterval=20"/>.
  • Ensure that the pqcmx library can be loaded by checking the db2diag.log file as described in the next section.

db2diag.log

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.

[COMMON]
DiagLevel=4

With 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 file.

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-13.42.49.595797+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-13.42.49.862777+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 disp+work.

Listing 9. Messages in db2diag.log file for process R3trans
2011-12-12-13.43.15.905735+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 disp+work processes.


Conclusion

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.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

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
ArticleID=791843
ArticleTitle=Configure and monitor SAP applications with InfoSphere Optim Performance Manager Extended Insight
publish-date=02162012