Generate reports remotely and offline with InfoSphere Optim Performance Manager command-line utility

Amp up your reporting with your own scheduler

You can use predefined report features in the InfoSphere® Optim™ Performance Manager for DB2® for Linux®, UNIX® and Windows® web console to generate reports in HTML, PDF, PPT, and XLS formats. In this tutorial, learn how to extend these features with the command-line utility (CLU). With the CLU, you can generate reports in offline and batch mode without logging on to the web console, schedule reports to run at periodic intervals, and send reports as attachments to team members.

Amitava Kundu (amitavakundu@in.ibm.com), Senior Software Engineer, IBM China

Author photo of AmitavaAmitava Kundu is a Senior Software Engineer working in the IBM Information Management division at India Software Lab. He’s the architect and lead for InfoSphere Optim Query Capture and Replay reporting area. Amitava is also responsible for IBM InfoSphere Optim Performance Manager reporting solution. Prior to that he was part of IBM Rational team leading its Project Management tooling and solutions. Overall, Amitava has 19 years of IT related experience in different industry segments and functional areas. Amitava is a PMP from PMI since 2005.



Anushka Chandrababu (anuschan@in.ibm.com), Staff Software Engineer, IBM

Anushka Chandrababu is a staff software engineer who has been with IBM for six years. She is a key developer for InfoSphere Optim Performance Management tools for DB2. Anushka is an IBM Certified Database Associate for DB2 and Sun-certified Java Professional. She also has experience working on the Optim Data Studio suite of products.



Neela Madhav Suram (sn.madhav@in.ibm.com), Senior Staff Software Engineer, IBM

Neela Madhav Suram is an IT specialist. Since joining IBM in 2001, he has held various roles, from design and development of applications to working with legacy systems running on the System z platform. He currently works with the Optim Performance Manager development team at the IBM India Software Lab. Previously, Neela was part of the worldwide WebSphere Business Partner Enablement team.



Ellen Livengood (elivengd@us.ibm.com), Information Design Lead, IBM

Ellen Livengood is a lead information developer at the IBM Silicon Valley Lab in San Jose, Calif. She has worked for more than 10 years on various DB2 database tooling products, including InfoSphere Optim Performance Manager, Data Studio, and InfoSphere Data Architect.



14 August 2014

Overview

Trend analysis using interactive performance reports is a key feature of the InfoSphere Optim Performance Manager for DB2 for LUW web console. The reports help organizations to identify bottlenecks in database performance, and they can be analyzed to help prevent performance issues in the future.

Learn to use the CLU to generate performance reports in offline batch mode without using the Optim Performance Manager web console. By bypassing the web console, you can generate reports remotely and schedule report generation at periodic intervals using a scheduler (for example, Windows Task Scheduler or UNIX Cron). Download the sample scripts.

In Optim Performance Manager 5.3, you can use the CLU to generate the types of reports shown below.

Table 1. Reports generated by the command-line utility
Snapshot ModeIn-Memory Mode
Database Configuration (DBCFG)Database Configuration (DBCFG)
Database Manager Configuration (DBMCFG)Database Manager Configuration (DBMCFG)
Performance OverviewPerformance Overview
SQL Baseline Comparison
Storage Group (DB2 10 and above)
Table UsageTable Usage
Diskspace ConsumptionDiskspace Consumption
Top SQL Package
Top Dynamic SQLTop SQL
Workload Manager OverviewWorkload Manager Overview
Workload Manager Service SubclassWorkload Manager Service Subclass
Workload Manager Service SuperclassWorkload Manager Service Superclass
Workload Manager WorkAction SetWorkload Manager WorkAction Set
Workload Manager WorkloadWorkload Manager Workload

You cannot use the CLU for any of the detailed reports that can be generated using the Optim Performance Manager web console or the Cross-Database Overview report. To automate the generation of a Cross-Database Overview report, schedule the report to run at specified intervals in the web console.

Prerequisites

To follow along with the examples, you should have installed and configured InfoSphere Optim Performance Manager for DB2 for LUW and collected some performance data. (The IBM Information Center has details about installing and configuring InfoSphere Optim Performance Manager.) You will also need:

  • JRE 1.6 or higher
  • 250 MB of hard-disk space for expanding the CLU
  • Additional hard-disk space to store report files generated by the CLU
  • Connectivity between the system where Optim Performance Manager is installed and the system from which the CLU is being executed
  • Connectivity between the system where the monitored database is installed and the system from which the CLU is being executed

To create reports, you must select the following monitoring profiles in the database monitoring configuration: Basic, Locking, SQL Statements and Connections, I/O and Disk Space, and Workload Manager.


Downloading the CLU from the web console

After a database has been configured for monitoring, performance data is collected at specified intervals and stored in the repository database. Reports can then be created for any time period during which monitoring data is available in the repository database.

Before you can use the CLU to generate reports offline, you must use the web console to specify initial report settings and download the necessary files.

Complete the following steps to download the CLU and other required files:

  1. Click the Open menu and select Predefined Reports.
    Figure 1. Open Predefined Reports
    Image shows downloading CLU - Step 1
  2. Select the report type and fill in the required fields. Click Save Report Definition to start downloading the files required to run the CLU.
    Figure 2. Download CLU
    Image shows downloading CLU - Step 2
  3. Select the Report format and the Target operating system (the platform on which you will execute the CLU).

    If you've never used the CLU, you must select Download script file and library files. This option downloads the library files required to execute the script file that generates the report. In the future, if you want to use the CLU again, you can choose to download the script file only.

    Figure 3. Download required files
    Image shows downloading CLU - Step 3
  4. After the download completes, expand the .zip file into a new directory — c:\clu_directory\, for example. The resulting directory structure should look like the image below.
    Figure 4. CLU directory structure
    Image shows CLU directory structure

Important directories

A couple of the folders in the CLU directory structure are especially important, as follows.

Generated Reports
The Generated Reports folder is the default output location for reports generated when you run the CLU script. You can change the output directory for generated reports to any directory by changing the OPMREPORTOUTPUTPATH parameter in the Command Line Utility script file.
Logs
The Logs folder contains the log file, which contains entries for any errors that occur during the creation of the report file and whether the job was successful. You can change the output directory for log files to any directory by changing the LOGFILEPATH parameter in the Command Line Utility script file.

Generating a report with the CLU

Before you use the script, or batch file, to generate the report, review the file for the parameters that affect report generation, as outlined in Table 2.

Table 2. Optim Performance Manager repository connection parameters
Parameter nameDescription
SCRIPTNAMEThe name of the file. This must be identical to the script file name. If the file name for the script is changed, this parameter must also be changed to the same value.
OPMREPOSITORYHOSTThe host name or IP address of the repository database. This value can sometimes be set to 127.0.0.1, in which case you must change it before you can generate a report. This can happen because of the way values were provided during InfoSphere Optim Performance Manager setup and configuration. If necessary, change the value to the actual IP address on the Configuration Repository window, as shown in Figure 5.
OPMREPOSITORYPASSWDThe decrypted password to log on to the repository database. To use this password, overwrite the clear-text password and uncomment this parameter.
REPORTFORMATThe format in which the report should be generated. Supported formats are PDF, PPT, and XLS.
REPORTENDTIMEThe end time of the report. Together with the REPORTDURATION parameter, it defines the time period in the monitored database time zone for which the report is generated.
Figure 5. Configuration Repository
Image shows screen to alter Configuration Repository host details

After you modify the parameters as needed, generate a report by executing the script (batch file) as follows:

  1. Ensure that the .bat/.sh script file is in the same directory where the CLU library files were expanded, as shown in Figure 4.
  2. On UNIX (including AIX and Linux) systems, run chmod -R 755 clu_directory to set the required file permissions on all of the files and folders in the expanded directory structure (shown in Figure 4).
  3. Execute the .bat/.sh file to generate the report. The generated reports are created by default in the .\Generated Reports folder, as explained in Important directories.

Scheduling CLU report creation

To use the Windows Task Scheduler or UNIX Cron to schedule report generation or to automatically send a generated report as an email attachment, you must make a few changes to the script file. The following examples show the necessary changes in a sample script that generates a Performance Overview Report on Windows:

  • Add a line to the top of the script file that changes the working directory to the directory where you expanded the library files, as shown in Figure 4. In this example, the directory is named clu_directory, as in Listing 1.
    Listing 1. Change working directory to the CLU directory
    cd clu_directory
  • Change the REPORTENDTIME so that for each execution, the required time frame would get selected. For example (on Windows), the REPORTENDTIME parameter could be changed so that the current execution time is taken as the REPORTENDTIME, as shown in Listing 2.
    Listing 2. Change for REPORTENDTIME parameter
    SET repEndDtStamp9=%date:~-4%-%date:~4,2%-%date:~7,2% 0%time:~1,1%:%time:~3,2% 
    SET repEndDtStamp24=%date:~-4%-%date:~4,2%-%date:~7,2% %time:~0,2%:%time:~3,2%
    if "%HOUR:~0,1%" == " " (SET REPORTENDTIME=%repEndDtStamp9%) 
        else (SET REPORTENDTIME=%repEndDtStamp24%)
  • The CLU typically waits for user input after creating the report. When you schedule a report to run, however, you do not want to require any user input or intervention. Thus, to schedule a report, you must add the code in Listing 3 before the last java -cp statement in the script.
    Listing 3. Letters in bold to be added
    For AIX/Linux
    	yes ‘’| java –cp ".":"config.properties":"$BIRTHOME/lib/*"……………….
    For Windows
        echo .| java –cp ".":"config.properties":"$BIRTHOME/lib/*"……………….
  • You can email the generated report to a set of recipients by using either mailx or Send-MailMessage, as shown in Listing 4.
    Listing 4. Send mail
    For AIX/Linux
    	mailx or uuencode
    For Windows (7 and greater)
        powershell Send-MailMessage -to %TOMailList% -from %FromMailID% 
    	    -subject %Subject% -Attachments %REPORT% -SmtpServer %SMTPSERVER%

Conclusion

This article provided step-by-step instructions for downloading, deploying, and using the Optim Performance Manager CLU in offline mode. The CLU significantly extends reporting functions so you can generate and email reports in your own environment without any dependency on the Optim Performance Manager Console server being available.


Downloads

DescriptionNameSize
Sample Performance Overview Script for hourly run1Performance_Overview_Hourly.zip4KB
Sample TopSQL Report Script for hourly run2Top_SQL_Hourly.zip4KB

Notes

  1. This is a Windows .bat file.
  2. This is a UNIX/AIX/Linux .sh file.

Resources

Learn

Get products and technologies

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

Discuss

  • Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=980315
ArticleTitle=Generate reports remotely and offline with InfoSphere Optim Performance Manager command-line utility
publish-date=08142014