Configure and monitor CLI applications with Optim Performance Manager Extended Insight

Optim™ Performance Manager Extended Edition provides end-to-end database performance monitoring capability for DB2® call level interface (CLI) applications with its extended insight capability. This capability gives you insight into the response time metrics of a database application throughout the software stack, from when the SQL is issued in the application, through the database server, the operating system, and the network. This tutorial provides detailed steps for installing, configuring, and validating the Optim Performance Manager Extended Insight feature for a CLI application.

Sonali Kenge (skenge@us.ibm.com), Software Engineer, IBM

Sonali Kenge photoSonali Kenge is the quality assurance lead for IBM's pureQuery/OPM EI. She is part of the Data Server Runtime Clients Quality Assurance organization at the IBM Silicon Valley Lab in San Jose, California. She has over 12 years of experience working on Development and Quality Assurance teams involved with IBM DB2 and Informix products.



28 October 2010

Also available in Chinese

Before you start

Introduction

IBM Optim Performance Manager is a web-based database monitoring solution that helps you resolve your database performance issues with a guided approach. IBM Optim Performance Manager's Extended Insight feature provides end-to-end database performance monitoring for Java® and CLI applications, giving you the ability to quickly understand where your database applications are spending their time. With Extended Insight, DBAs can quickly see and understand where database applications are spending time across the IT stack from the application through the application server, the database client, the database server, and the network. When a critical business application isn't performing the way you expect, you need Extended Insight to give you end-to-end visibility from the line of code issuing 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 isn't in the database itself, Extended Insight also makes it easier for you to work with the people managing other parts of the infrastructure or the developers to solve those problems.

End-to-end monitoring for CLI uses a connection supervisor client (CSC). The CSC implementation of IBM is PQCMX. PQCMX is part of the IBM Optim Performance Manager Extended Insight client. PQCMX is a native implementation of the Java CMX Client Service. Together, CLI and PQCMX form a CMX client that interacts with existing end-to-end monitoring components, such as CMX Controllers and CMX Monitors.

This tutorial takes you through a setup where you configure Optim Performance Manager's Extended Insight feature to monitor CLI applications.

About this tutorial

This tutorial walks you through installing, configuring, and validating the Optim Performance Manager Extended Insight feature for CLI applications. Following are the main steps that are covered:

  • Verify that you have the right prerequisites
  • Install the software on the necessary servers
  • Activate the Extended Insight feature
  • Configure the CLI client to recognize the Extended Insight feature
  • Validate your CLI client installation and the PQCMX setup
  • Run a workload to validate that data is being collected
  • Troubleshoot client installation or configuration

The Download section also contains a sample CLI workload program.

Assumptions

The tutorial makes the following assumptions about your environment:

  • You have installed Optim Performance Manager and activated the Optim Performance Manager Extended Insight feature. The Resources section contains a link to detailed instructions for installing and configuring Optim Performance Manager.
  • You have installed one of the IBM Data Server Client Packages for Version 9.7 Fix Pack 2 on your client computer. The instructions in the tutorial assume you are using the Data Server Driver Package. The Resources section contains a link to detailed instructions for installing IBM Data Server Client Packages.
  • You have a database that is configured for monitoring.
  • The instructions in the tutorial assume that Optim Performance Manager and the CLI client are both running on Windows®.

Activate the CLI client to recognize the Extended Insight feature

Install the Extended Insight feature

The Extended Insight feature of Optim Performance Manager provides an installation wizard and a configuration wizard to install and configure the product on your client computer.

Follow these steps to use the installation wizard to install and configure Extended Insight on your client computer:

  1. From the directory of the Extended Insight installation image, run the IBM_OPMEI_V4_1_Windows.exe command by either double-click on the IBM_OPMEI_V4_1_Windows.exe file from Windows Explorer or by running it from a Windows command prompt.
  2. The IBM_OPMEI_V4_1_Windows.exe command launches the installation wizard and displays the title screen (Figure 1). From the title screen, select your preferred language and click OK.
    Figure 1. Initial installation screen
    Product title and licensed materials statement on initial Optim Performance Manager installation screen.
  3. From the Introduction screen (Figure 2), click Next.
    Figure 2. Introduction screen
    Welcome and introductory message for the installation wizard for IBM Optim Performance Manager Extended Insight
  4. From the Software License Agreement screen (Figure 3), read the license agreement, make the appropriate selection to indicate you accept the terms, and click Next.
    Figure 3. Software License Agreement screen
    displays the license agreement
  5. From the Choose Install Directory screen (Figure 4), specify the installation path where you want to install the product and click Next. (Use the default directory unless you need to install it somewhere else.)
    Figure 4. Choose Install Directory screen
    You can enter another directory to replace the default or use the Choose... button
  6. Review the information on the Pre-Installation Summary screen, and click Install.
    Figure 5. Pre-Installation Summary screen
    Displays product name, install folder, and disk space required and available at at the inatallation target
  7. It will take a few minutes for the installation to complete. The Installing screen (Figure 6) has an indicator at the bottom that shows the progress.
    Figure 6. Install screen
    Installing IBM Optim Performance Manager Extended Insight screen has a progress indicator at bottom of screen
  8. When installation is complete you go to a screen that indicates installation was successful (Figure 7). Select the Open the configuration tool check box and click Done. The next section of the tutorial picks up from here.
    Figure 7. Installation complete
    successful install message, with path listed for installation and for the install log

You are now done activating the Extended Insight feature.

Note: You only need to activate Extended Insight once. However, if you change the location or settings of your Optim Performance Manager installation, you can update the configuration by running the configuration tool again.


Configure the CLI client to recognize the Extended Insight feature

In the last step of the previous section, you launched the configuration tool by selecting the Open the configuration tool check box and clicking Done. You can also launch the tool by running the cfgtool.bat command from the following location:
C:\Program Files\IBM\IBM_pureQuery\configuration

Follow these steps to use the configuration tool to configure your CLI applications:

  1. From the initial Configuration screen (Figure 8), select only the CLI applications check box and click Next. By default, the check boxes for all three applications are selected, so you need to clear the other two.
    Figure 8. Configuration screen
    Only CLI applications is selected. JDBC applications and WebSphere applications check boxes are not selected
  2. The Configuration File screen (Figure 9) is very important. On this screen you must identify the CLI driver that is being used by the application that you want to monitor. By identifying the correct CLI driver, you can identify which db2dsdriver.cfg file to configure. The configuration will not succeed if you do not configure the correct db2dsdriver.cfg file. There are two types of environment variables that you might have to set to use the IBM Data Server Driver for ODBC and CLI:
    • Environment variables that have replaced some DB2 registry variables
    • An environment variable that tells your applications where to find the driver libraries

    The Resources section contains a link to detailed information on configuring environment variables for the IBM Data Server Driver for ODBC and CLI.

    Figure 9. Configuration File screen
    Shows db2dsdriver.cfg file found on computer

    The configuration tool attempts to find the db2dsdriver.cfg files for you. If the configuration tool cannot find the one that you need to configure, you can enter the location for it. The Resources section contains a link to detailed information about the db2dsdriver.cfg file and its location.

    The screen shot in Figure 9 shows that the configuration tool has found the configuration file at:
    C:\Program Files\IBM\IBM DATA SERVER DRIVER\cfg\db2dsdriver.cfg

    The sample db2dsdriver.cfg file without any configuration changes is shown in Listing 1.

    Listing 1. Sample db2dsdriver.cfg file
    <configuration>
      <DSN_Collection>
        <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"/>
        <!-- Long aliases are supported -->
        <dsn alias="longaliasname2" name="name2" host="server2.net1.com" port="55551">
          <parameter name="Authentication" value="Client"/>
        </dsn>
      </DSN_Collection>
      <databases>
        <database name="name1" host="server1.net1.com" port="50001">
          <parameter name="CurrentSchema" value="OWNER1"/>
          <WLB>
            <parameter name="enableWLB" value="true"/>
            <parameter name="maxTransports" value="50"/>
          </WLB>
          <ACR>
            <parameter name="enableACR" value="true"/>
          </ACR>
        </database>
        <!-- Local IPC connection -->
        <database name="name3" host="localhost" port="0">
          <parameter name="IPCInstance" value="DB2"/>
          <parameter name="CommProtocol" value="IPC"/>
        </database>
      </databases>
      <parameters>
        <parameter name="GlobalParam" value="Value"/>
      </parameters>
    </configuration>

    When you are sure that you have specified the correct db2dsdriver.cfg file, click Next.

  3. From the next screen of the Configuration Tool wizard, enter the host name or IP address of the Optim Performance Manager and the Port number for communication between Optim Performance Manager and Extended Insight. This port is generally called the cmx controller port and is set by the pdq.cmx.controllerURL parameter in this file:
    C:\Program Files\IBM\OPM\RepositoryServer\instances\instancename\pdq.properties

    Figure 10 shows this screen with sample values entered. When you have entered the values for your installation, click Next.

    Figure 10. Configuration Tool wizard — Host name or IP address and Port number
    Configuration Tool wizard with sample values for the host name and communication port number
  4. From the next screen of the Configuration Tool wizard, enter information for the database that you want to monitor. As stated earlier in the Assumptions section, this tutorial assumes that the database is added for monitoring on the computer where Optim Performance Manager is installed. Even though this information is requested at this point, it is not added into the db2dsdriver.cfg file. You have to manually add this information while setting up to run the workload. The Configuration Tool only sets the global configuration values.

    Figure 11 shows this screen with sample values entered. When you have entered the values for your database, click Next.

    Figure 11. Configuration Tool wizard — database information
    Configuration Tool wizard with sample values for Database host name, Database port, and Database name
  5. Review the information on the summary screen (Figure 12) and click configure.
    Figure 12. Configuration Tool wizard — summary
    Pre-Configure Summary
  6. The Finish screen (Figure 13) indicates that the CLI application configuration completed successfully. Click Done to close the Configuration Tool wizard.
    Figure 13. Configuration Tool Finish screen
    The Finish screen indicates that the Configuration Tool wizard completed successfully.

You have now successfully configured CLI applications. To verify that the configuration is correct, open the db2dsdriver.cfg file. The lines shown in bold in Listing 2 are the ones that should have been added by the Configuration Tool.

Listing 2. db2dsdriver.cfg file after configuration
<configuration>
  <DSN_Collection>
    <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"/>
    <!-- Long aliases are supported -->
    <dsn alias="longaliasname2" name="name2" host="server2.net1.com" port="55551">
      <parameter name="Authentication" value="Client"/>
    </dsn>
  </DSN_Collection>
  <databases>
    <database name="name1" host="server1.net1.com" port="50001">
      <parameter name="CurrentSchema" value="OWNER1"/>
      <WLB>
        <parameter name="enableWLB" value="true"/>
        <parameter name="maxTransports" value="50"/>
      </WLB>
      <ACR>
        <parameter name="enableACR" value="true"/>
      </ACR>
    </database>
    <!-- Local IPC connection -->
    <database name="name3" host="localhost" port="0">
      <parameter name="IPCInstance" value="DB2"/>
      <parameter name="CommProtocol" value="IPC"/>
    </database>
  </databases>
  <parameters>
    <parameter name="GlobalParam" value="Value"/>
    <!-- PQCMX E2E monitoring -->
    <!-- <parameter name="connectionSupervisorLibrary" 
      value="/home/pureQuery/pqcmx"/> -->
    <!-- <parameter name="connectionSupervisorProperties" 
      value="controllerURL=server1.net1.com:50001"/> -->
    <parameter name="connectionSupervisorProperties" 
      value="controllerURL=fogcity.usca.ibm.com:6000,dataSourceLookupInterval=20"/>
    <parameter name="connectionSupervisorLibrary" 
      value="C:\Program Files\IBM\Optim_pureQueryRuntime\pureQuery\bin\pqcmx"/>
  </parameters>
</configuration>

Tip 1

If at a later point you move your Optim Performance Manager to a different machine, you can modify the db2dsdriver.cfg file manually to reflect the change. You would need to change the controllerURL value to identify the new server and the new communication port. For example, if your new server is server1.com and your cmx controller port is 5555, you would specify:
controllerURL=server1.com:5555

Tip 2

When you manually configure the db2dsdriver.cfg file, update the connection supervisor client (CSC) library property with the appropriate value as follows:
parameter name="connectionSupervisorLibrary" value=”path_to CSC_library”

where path_to_CSC_library is the path to the CSC library file for your platform with the file specified only as pqcmx. Variations for different operating systems are shown below.

Windows:
EI_home\pureQuery\bin\pqcmx

Linux® and UNIX® 32-bit:
EI_home/lib32/pqcmx

Linux and UNIX 64-bit:
EI_home/lib64/pqcmx

For example, if you were using a Linux or UNIX 32-bit system, you would update the property as follows:
parameter name="connectionSupervisorLibrary" value="EI_home/lib32/pqcmx"

Congratulations, you've completed the setup process. Now you are ready to move on to the next step.


Validate your CLI client installation and the PQCMX setup

CLI end-to-end monitoring requires that you install and configure various software components such as Optim Performance Manager and Optim Performance Manager Extended Insight, including PQCMX and the CLI driver. If the Extended Insight client is able to get all the monitoring data from the Optim Performance Manager on the Optim Performance Manager Extended Insight Dashboard, you have the correct setup. Otherwise, an error in any installation or configuration step can prevent monitoring of CLI applications with Optim Performance Manager Extended Insight.

Possible sources of errors include:

  • Syntax errors or required settings missing in the db2dsdriver.cfg file.
  • Incorrect or missing PQCMX property values. For example, an incorrect TCPIP address or port value specified for the controllerURL property.
  • Failure to enable monitoring on the Optim Performance Manager server for a specific target database.
  • Not using TCPIP for the CLI database connection.

DB2 Version 9.7 Fix Pack 2 includes the DB2 interactive CLI command tool (db2cli), which you can use to perform a validation check on the db2dsdriver.cfg file. The tool validates the db2dsdriver.cfg schema and optionally displays the keywords CLI uses to establish a database connection without actually establishing a connection.

By default, the db2cli command is located in the bin directory of the Data Server Driver Package installation:
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin

The Resources section contains a link to detailed information about using db2cli.

The following screenshots show a set of examples that use the db2cli command to validate a CLI installation and also to validate whether the CSC/PQCMX driver can talk to the Optim Performance Manager server properly. Before starting this validation process, make sure that you have an Optim Performance Manager server up and running, and that the database is added to the Optim Performance Manager server for monitoring.

Note: CSC is used as a general term corresponding to connection supervisor client PQCMX is an implementation of the CSC stack.

Figure 14 shows db2cli being used to display version information for the CSC library.

Figure 14. db2cli csc -info
Command entered from prompt. Returns version information for the CSC library.

Figure 15 shows db2cli being used to verify the validity of the db2dsdriver.cfg configuration file and its schema.

Figure 15. db2cli validate
Command entered from prompt. Returns results of db2dsdriver.cfg validation.

Figure 16 shows db2cli being used to list the details of the status of the provided monitoring database. Details about what this form of the command returns follow the figure.

Figure 16. db2cli validate –database database_name:database_hostname:database_port
Command entered from prompt. Returns details of whether the status of the provided monitoring database is ON or OFF.

Click to see larger image

Figure 16. db2cli validate –database database_name:database_hostname:database_port

Command entered from prompt. Returns details of whether the status of the provided monitoring database is ON or OFF.

The following explains some of the data that is returned in the example shown in Figure 16:

  • In the example, the CLI trace has not been set. This is why the warning about the data source name not being found appears.
  • Optionally, you can set the DB2DSDRIVER_CFG_PATH variable to ensure that the correct db2dsdriver.cfg file is picked up. Figure 17 shows an example of the command to set the variable.
    Figure 17. set DB2DSDRIVER_CFG_PATH
    Command entered from prompt with value of C:\Program FIles\IBM\IBM DATA SERVER DRIVER\cfg
  • The CSC Information Section includes the following data:
    • The name of the monitored database, the name of the monitored database server, and the name of the monitored database port. If CLI is unable to obtain this information for any reason, blanks will be reported to indicate that this information is not available.
    • The platform specific CSC library name.
    • The result of the CSC library load indicates either success or failure.
    • The result of the CSC initialization indicates either success or failure. If success, the negotiated CSC major and minor levels are also reported.
    • The CSC Name and CSC Version string as returned by the CSC library. This information is blank if the CSC is not successfully loaded or initialized.
    • The current monitoring status for the target database. The status indicates either on or off. In the case of PQCMX, the status is on only if monitoring is enabled and a connection can be made to the Optim Performance Manager monitor server.
    • All logging messages externalized by the CSC. In the case of PQCMX, this information can include additional diagnostic information describing the interaction with the Optim Performance Manager server.
    • CLI may also add additional diagnostic messages in the output. For example, CLI might indicate if monitoring is disabled because the CLI connection does not use TCPIP. Certain CSC logging messages and CLI diagnostic messages may not appear in all scenarios. For example, no CSC logging messages are included if the CSC cannot be loaded.

Run a workload to validate that data is being collected

Following are some tips to consider when writing your own workload program:

  • Run your workload for at least three minutes to ensure monitoring data has enough time to appear on the Optim Performance Manager Extended Insight Dashboard. Monitoring data is sent from PQCMX to the CMX server once every minute. At least two flows (transactions) are needed for the data to appear in the Optim Performance Manager Extended Insight Dashboard.
  • Make sure that you have an Optim Performance Manager server up and running, that the database is already added to the Optim Performance Manager server for monitoring, and that you have successfully validated the CLI configuration.

The Download section contains a sample CLI workload program. Follow these steps to run the sample program:

  1. Modify the following line of the sample workload:
    sqldriverconnect 1 0 "DATABASE=****;PROTOCOL=TCPIP;PORT=****;HOSTNAME=****;UID=****;PWD=****" -3 255 SQL_DRIVER_NOPROMPT

    Change the **** values to reflect your database information and the user ID and password you use to connect to the database.

    For example:
    sqldriverconnect 1 0 "DATABASE=clie2edb;PROTOCOL=TCPIP;PORT=50000;HOSTNAME=fogcity.usca.ibm.com;UID=user1;PWD=password" -3 255 SQL_DRIVER_NOPROMPT

  2. Update the db2dsdriver.cfg file to include your database information.

    For example:
    <database name="clie2edb" host="fogcity.usca.ibm.com" port="50000"> </database>

    Generally, you add database information between <databases> and </databases>.

  3. Use the db2cli command to run the workload. Figure 18 shows an example of how you would run the workload if you had downloaded the sample to the c:\cli directory.
    Figure 18. db2cli < c:\cli\EICLIsample.t
    Command entered from prompt with current directory of C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin
  4. When you see the next command prompt, the run is complete. This may take approximately four to five minutes. Once the workload run is complete you should be able to see the data on the Optim Performance Manager Extended Insight Dashboard.
  5. From the Optim Performance Manager Extended Insight Dashboard (Figure 19), click Task Manager and select Extended Insight Dashboard.
    Figure 19. Optim Performance Manager Extended Insight Dashboard
    Screenshot of Optim Performance Manager Extended Insight Dashboard highlighting Task Manager button and Extended Insight Dashboard selection.
  6. From the Extended Insight Dashboard (Figure 20), click Select a Database and select your monitoring database.
    Figure 20. Select the database on the Optim Performance Manager Extended Insight Dashboard
    Screenshot highlighting the selection that lets you choose the monitoring database.
  7. The dashboard should now show data similar to what is shown in Figure 21. Select the CLI application and click Open Details.
    Figure 21. Monitoring data collected on Optim Performance Manager Extended Insight Dashboard
    Screen cap : Monitoring data collected on Optim Performance Manager Extended Insight Dashboard
  8. The dashboard should now show detailed data similar to what is shown in Figure 22.
    Figure 22. Details from the CLI Application run
    Screen shot of details view.

Troubleshoot client installation or configuration

This section explains how to troubleshoot a situation where you run a CLI workload and look at the Optim Performance Manager Extended Insight Dashboard, but see no data. Follow these steps to determine what might be causing the problem:

  1. Verify that your client software is supported and conforms to these requirements:
    • CLI applications must use the Data Server Client Packages for DB2 Version 9.7 Fix Pack 2.
    • Monitoring is not provided for the command line processor, embedded SQL applications, and any type of application that is not based on DB2 CLI.
    • Monitoring data is not collected and displayed in the Extended Insight Dashboard for statements that are executed under the following CLI scenarios:
      • CLI stored procedures, local CLI connections, or remote CLI connections that do not use TCP/IP
      • Array input chaining
      • Compound SQL
      • Catalog functions for querying system catalog information
      • SQLExecute() or SQLExecDirect() calls that return SQL_NEED_DATA
      • Asynchronous statement processing
      • Scenarios that involve SQLBulkOperations() calls
      • CLI LOAD API
  2. Use the DB2 interactive CLI tool.

    Use the db2cli command as described in Validate your CLI client installation and the PQCMX setup to ensure your monitoring is on. Sometimes, between your setup and the actual run time tasks that you do, something can unexpectedly go wrong. For example ,the DB2 may down, the monitoring database may not be responding, or the Optim Performance Manager instance may shut down. If you verify that everything is working fine, then continue to the next step.

  3. Enable the PQCMX trace.

    The pqcmxtrc command controls the trace facility for PQCMX. PQCMX traces can be useful for IBM support to analyze recurring and reproducible problems. Trace points are captured only within the PQCMX client, and are separate from DB2 and CLI tracing. The pqcmxtrc command syntax and options are similar to the db2trc command. Refer to the documentation for the db2trc command in the DB2 Info Center for details. The executable file for the pqcmxtrc command is located in a subdirectory under the OPMEI_HOME/pureQuery directory.

    Follow these steps to enable the PQCMX trace:

    1. Start the PQCMX trace:
      cd C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin
      C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin>pqcmxtrc on
    2. .
      cd C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin
      C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin>db2cli < C:\EICLIsample.t
    3. Dump the PQCMX trace:
      cd C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin
      C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin>pqcmxtrc dmp pqcmxtrc.dmp
    4. Stop the PQCMX trace:
      cd C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin
      C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin>pqcmxtrc off
    5. Format the PQCMX trace:
      cd C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin
      C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin>pqcmxtrc 
          fmt pqcmxtrc.dmp pqcmxtrc.fmt

      This creates a trace file named pqcmxtrc.fmt in the current directory, which is C:\Program Files\IBM\IBM_pureQuery\pureQuery\bin. Look in this file for messages that indicate a successful connection. Successful connection messages look similar to the following:

      [PQCMX '9.7.0.2' 's100514' 'IP23089' '2'][File cmxcs.C, Line 340] PQCMX is 
      attempting to connect to a controller server using the controllerURL property 
      fixed address: fogcity.usca.ibm.com:60000.
      [PQCMX '9.7.0.2' 's100514' 'IP23089' '2']
          [File cmxcs.C, Line 359] PQCMX successfully 
      connected to a controller server using the controllerURL property fixed 
      address: fogcity.usca.ibm.com:60000 with a negotiated level: 2.

      If you do not see these type of messages, this means that you have a problem with your setup. Instead, you might be seeing messages similar to this:

      [PQCMX '9.7.0.2' 's100514' 'IP23089' '2'][File cmxcs.C, Line 365] PQCMX did not 
      successfully connect to a compatible controller server using the controllerURL 
      property fixed address: fogcity.usca.ibm.com:66000.
  4. Verify that the database is added for monitoring.

    Sometimes the database might be added for monitoring correctly, but for some reason it might be disabled or there could be errors. You can look for errors in the Optim Performance Manager server log files (typically the db2peserv.log file) located on the machine where your Optim Performance Manager is installed or the Optim Performance Manager console.

    Use the db2cli validate command for this purpose. The output from the command should help you understand the problem and you may not require any further analysis.

  5. Set the diagnostic level.

    CSC implementations can externalize messages for logging. These messages include a message level to identify the type of message. The message levels are similar to the diagnostic log file error capture levels (DiagLevel). CLI writes the message text to a db2diag log file based on the message level and the DiagLevel setting. Messages with message levels less than or equal to the DiagLevel are written to the log.

    You can externalize the messages for logging by setting DiagLevel to get more info in the db2diag.log file. To do this, add the following section to your db2cli.ini file:

    [COMMON]
    DiagPath=C:\DIAGLOG
    DiagLevel=4

    The Resources section contains a link to detailed information on the db2cli.ini initialization file.

    Run the workload and check the db2diaglog.log contents.

    In Windows 2003 and XP environments, user data files are written to:
    C:\Documents and Settings\All Users\Application Data\IBM\DB2\Copy Name
    where Copy Name is the name of your DB2 copy.

    In Linux and UNIX environments, information is written to:
    INSTHOME/sqllib/db2dump
    where INSTHOME is the home directory of the instance.

    The db2diaglog.logoutput file should show messages from CSC or PQCMX that indicate what the problem is. For example, if there is no message saying the connection has been established, the Optim Performance Manager server is probably not up and running, or the controller port is wrong.

    You may also see messages (CSC/PQCMX) that indicate a communication problem such as the following:

    2010-09-28-17.03.11.625848-240 I22649E474          LEVEL: Info
    PID     : 4674                 TID  : 1090525504   PROC : db2cli
    INSTANCE: sona                 NODE : 000
    FUNCTION: DB2 UDB, call level interface, CSCLogMessage, probe:10
    DATA #1 : String, 210 bytes
    [PQCMX '9.7.0.2' 's100514' 'IP23089' '2'][File cmxcs.C, Line 365] PQCMX did not 
    successfully connect to a compatible controller server using the controllerURL 
    property fixed address: fogcity.usca.ibm.com:66000.

    These messages show the controllerURL you used. Verify that it is correct.

    Or, you might see something like this:

    2010-09-28-17.16.36.367509-240 I28432E364          LEVEL: Error
    PID     : 15440                TID  : 47234027570256PROC : db2cli
    INSTANCE: sona                 NODE : 000
    FUNCTION: DB2 UDB, call level interface, CLI_loadCscLibrary, probe:0
    DATA #1 : <preformatted>
    CSC Initialization failed 
    (Load library /home/hotel78/sona/sqllib/libpqcmx.so failed with rc = 0)

    A message similar to the one above indicates that the CSC initialization failed. Check and make sure that your library is exists at the path you specified in the db2dsdriver.cfg file.

  6. Enable CLI tracing.

    The CLI trace captures information about applications that access the DB2 CLI driver. The CLI trace offers no information about why you do not see the data on the Optim Performance Manager Extended Insight Dashboard, but it can help you determine whether the application is connected to the database server.

    To turn on a CLI tracing, you must enable a set of CLI configuration keywords. To do this, add the following lines to the db2cli.ini file:

    [COMMON]
    TraceTimeStamp=3
    TraceComm=1
    trace=1
    TraceFileName=trace_cli.txt
    TraceFlush=1

    The Resources section contains a link to detailed information about obtaining CLI traces.

    Check the trace_cli.txt trace file to see if the sqldriverconnect call was successful and if the client was able to make a connection with the monitoring database. (This file is created in your current working directory because you have not specified an absolute path.) For example, the following trace file indicates that the client was not able to make a connection to the monitoring database because the username or password was invalid:

    SQLDriverConnect( hDbc=0:1, hwnd=0:0, szConnStrIn="DATABASE=clie2edb;
    PROTOCOL=TCPIP;PORT=50000;
    HOSTNAME=fogcity.usca.ibm.com;UID=******;PWD=********", cbConnStrIn=-3, 
    szConnStrOut=*****, cbConnStrOutMax=255, pcbConnStrOut=******, 
    fDriverCompletion=SQL_DRIVER_NOPROMPT )
    ---> Time elapsed - +6.000000E-005 seconds
        sqlccconnr( timeout - +0.000000E+000, protocol - 0x03 )
        sqlccconnr( ) - time elapsed - +0.000000E+000
        sqlccsend( Handle - 0047589315850368 )
        sqlccsend( ulBytes - 282 )
        sqlccsend( ) rc - 0, time elasped - +2.300000E-005
        sqlccrecv( timeout - +0.000000E+000 )
        sqlccrecv( ulBytes - 127 ) - rc - 0, time elapsed - +8.993500E-002
        sqlccsend( Handle - 0047589315850368 )
        sqlccsend( ulBytes - 289 )
        sqlccsend( ) rc - 0, time elasped - +1.000000E-005
        sqlccrecv( timeout - +0.000000E+000 )
        sqlccrecv( ulBytes - 37 ) - rc - 0, time elapsed - +9.589600E-002
    SQLDriverConnect( )
         <--- SQL_ERROR   Time elapsed - +3.643090E-001 seconds
    
    SQLError( hEnv=0:0, hDbc=0:1, hStmt=0:0, pszSqlState=*****, 
    pfNativeError=****, pszErrorMsg=*****, cbErrorMsgMax=1024, pcbErrorMsg=**** )
        ---> Time elapsed - +1.140000E-004 seconds
    ( iRowNumber=-1, iColumnNumber=-2 )
    
    SQLError( pszSqlState="08001", pfNativeError=-30082, 
    pszErrorMsg="[IBM][CLI Driver] SQL30082N  Security processing failed with 
    reason "24" ("USERNAME AND/OR PASSWORD INVALID").  
    SQLSTATE=08001", pcbErrorMsg=127 )
       <--- SQL_SUCCESS   Time elapsed - +1.843800E-002 seconds

Conclusion

As demonstrated by this tutorial, it is fairly easy to install Optim Performance Manager Extended Insight and configure it to monitor your CLI applications. This is an exciting product release that opens up a world of possibilities in terms of performance management from a life cycle perspective. This feature can help DBAs collaborate more efficiently with other IT staff by providing them with a web-based user interface that provides overview, alert, and diagnostic dashboards to simplify root cause analysis in CLI applications that access DB2 data.


Download

DescriptionNameSize
Sample CLI Workload for this tutorialEICLIsample.zip2KB

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

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=556648
ArticleTitle=Configure and monitor CLI applications with Optim Performance Manager Extended Insight
publish-date=10282010