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 Mode||In-Memory Mode|
|Database Configuration (DBCFG)||Database Configuration (DBCFG)|
|Database Manager Configuration (DBMCFG)||Database Manager Configuration (DBMCFG)|
|Performance Overview||Performance Overview|
|—||SQL Baseline Comparison|
|—||Storage Group (DB2 10 and above)|
|Table Usage||Table Usage|
|Diskspace Consumption||Diskspace Consumption|
|—||Top SQL Package|
|Top Dynamic SQL||Top SQL|
|Workload Manager Overview||Workload Manager Overview|
|Workload Manager Service Subclass||Workload Manager Service Subclass|
|Workload Manager Service Superclass||Workload Manager Service Superclass|
|Workload Manager WorkAction Set||Workload Manager WorkAction Set|
|Workload Manager Workload||Workload 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.
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:
- Click the Open menu and select Predefined
Figure 1. Open Predefined Reports
- 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
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
- 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
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
OPMREPORTOUTPUTPATHparameter in the Command Line Utility script file.
- 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
LOGFILEPATHparameter 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
|The 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.|
|The 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.|
|The decrypted password to log on to the repository database. To use this password, overwrite the clear-text password and uncomment this parameter.|
|The format in which the report should be generated. Supported formats are PDF, PPT, and XLS.|
|The end time of the report. Together with the
Figure 5. Configuration Repository
After you modify the parameters as needed, generate a report by executing the script (batch file) as follows:
- Ensure that the .bat/.sh script file is in the same directory where the CLU library files were expanded, as shown in Figure 4.
- On UNIX (including AIX and Linux) systems, run
chmod -R 755 clu_directoryto set the required file permissions on all of the files and folders in the expanded directory structure (shown in Figure 4).
- 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
- Change the
REPORTENDTIMEso that for each execution, the required time frame would get selected. For example (on Windows), the
REPORTENDTIMEparameter could be changed so that the current execution time is taken as the
REPORTENDTIME, as shown in Listing 2.
Listing 2. Change for
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 -cpstatement 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
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%
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.
|Sample Performance Overview Script for hourly run1||Performance_Overview_Hourly.zip||4KB|
|Sample TopSQL Report Script for hourly run2||Top_SQL_Hourly.zip||4KB|
- This is a Windows .bat file.
- This is a UNIX/AIX/Linux .sh file.
- Read about Reporting with InfoSphere Optim Performance Manager in the IBM Information Center.
- The IBM Redbooks® publication titled "Performance Management: Using IBM InfoSphere Optim Performance Manager and Query Workload Tuner" describes the architecture and components of IBM InfoSphere Optim Performance Manager Extended Edition.
- Learn more about InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows features.
- The IBM InfoSphere Optim Performance Manager 5.2 and 5.3 Information Center provides all the official documentation.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
Get products and technologies
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, or use a product in a cloud environment.
- Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.