Identifying performance issues in SQL statements

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

Use the following procedure to view a report 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. (SPE2104)

    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.

   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 (SPE2010)

   • Client Application (SPE2010)

   If you add a filter, BMC AMI Command Center displays  (Filters Applied)  next to the title of the results pane and changes the color of the filter icon ().
   For more information, see Filtering-the-result-set-for-an-SQL-app.

5. (SPE2104)

    (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. (BMC.DB2.SPE2210)

    (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 analyze the statement, click Explain and then select one of the following options:

   • Dynamic
   • Static
   • Explain Package

   For more information, see Performance-perspective.

10. Click  to return to the subsystem summary view.

11. (SPE2104)

     (Optional) Click Options  to open the Options dialog box and select new search options.

12. (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.