Identifying performance issues in SQL statements

BMC AMI Command Centersupports BMC AMI Apptune for Db2 Advanced and Standard monitoring modes. When Apptune is in Standard monitoring mode, a value of N/A or -1 is displayed for metrics that are not recorded with the following exceptions:

• For Dynamic SQL statements, for collection ID, a value of **DYNAMIC** is displayed.
• For Static SQL statements, for user ID, a value of **STATIC** is displayed.

By default, the Command Center displays the Options dialog box when you open the SQL Analysis app. You can suppress the dialog box's display by clearing Always show this panel on startup in the Options dialog box.

Use the following procedure to view a list of SQL statements with performance issues for a subsystem.

To identify performance issues in SQL statements

1. In the upper left corner of the New console, click Apps.
2. Click SQL Analysis .
3. In the Options dialog box, select the following options:
   1. From the Subsystem list, select the Db2 subsystem that you need to investigate.

   2. From the Timeframe list, select an interval from the following options:

      Option	Description
      Default Interval	If you selected a single subsystem, the default interval start and end times are determined by the current APPTUNE interval. If you selected more than one subsystem, the default interval starts at the earliest interval among all active Db2 subsystems defined to the chosen data collector. It ends at the current date and time.
      Last Hour	The results include SQL statements in all APPTUNE intervals within the last hour, based on the server’s time zone.
      Last 24 Hours	The results include SQL statements in all APPTUNE intervals within the last 24 hours, based on the server's time zone.
      Today	The results include SQL statements in all APPTUNE intervals since midnight, based on the server's time zone.
      Yesterday	The results include SQL statements in all APPTUNE intervals within the 24 hours before midnight, based on the server's time zone.

   3. Click OK.
      The subsystem summary is displayed.

4. (Optional) Click Filter  to limit the results displayed based on one or more of the following fields:

   • Plan
   • Program
   • User
   • Client Application
   • Text Hash 

   If you add a filter, BMC AMI Command Center displays   next to the title of the results pane and changes the color of the Filter .
   For more information, see Filtering the result set for an SQL app.
   To clear the filters, click Clear all filters .

5. (Optional) From the Timeframe list, select an interval to adjust the time period in which you want to view results. If you select Custom Interval, the Time Intervals dialog box is displayed. For more information, see Selecting the time interval for SQL apps.
6. From the Subsystem list, select a Db2 subsystem to change the results viewed.
7. (Optional) To export the displayed list of SQL statements, perform the following steps:
   1. Click Generate Report.

   2. Select one of the following options:

      Option	Description
      CSV - With SQL text preview	Exports all the rows in the current list to a CSV file
      CSV - With full SQL text	Exports all the rows in the current list to a CSV file The full SQL text for each row is included in the CSV file.
      PDF	Exports all the rows in the current list to a PDF file The full SQL text for each row is included in the PDF file.

      Based on your browser settings, after Command Center generates the report, either the Save As dialog box is displayed or the browser saves the file automatically.

   3. Enter a name for the file.
   4. Click Save .
8. To view the details for a row:
   • Select the row and click Details.
   • Double-click the row.
9. (Optional) To investigate performance trends in the statement, click Trend.
   For more information, see Investigating performance trends in SQL statements.

10. (Optional) To analyze the statement, click Explain and then select one of the following options:

    • Dynamic
    • Static
    • Explain Package

    For more information, see Performance perspective.

11. Click  to return to the subsystem summary view.
12. (Optional) Click Options  to open the Options dialog box and select new search options.
13. (Optional) Click  to update the information displayed in the subsystem summary view.

Where to go from here

Use the Performance perspective to analyze an SQL statement with poor performance. For more information, see Tuning SQL.