Space announcement

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Identifying performance issues in SQL statements

The SQL Analysis app displays the top 100 SQL statements for a subsystem that use the most CPU time. The app defaults to displaying the SQL statements for the last subsystem selected in the Options dialog box for the app. The SQL Analysis app displays the following views:

  • Subsystem summary
  • Statement detail

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

 New console
  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:

      OptionDescription
      Default IntervalIf 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 HourThe results include SQL statements in all APPTUNE intervals within the last hour, based on the server’s time zone.
      Last 24 HoursThe results include SQL statements in all APPTUNE intervals within the last 24 hours, based on the server's time zone.
      TodayThe 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)
    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. To view the details for a row:
    • Select the row and click Details.
    • Double-click the row.
  8. (Optional) To analyze the statement, click the Explain menu and then select Dynamic.
    For more information, see Performance perspective.
  9. Click  to return to the subsystem summary view.
  10. (SPE2104) (Optional) Click Options to open the Options dialog box and select new search options.
  11. (Optional) Click  to update the information displayed in the subsystem summary view.
 Classic console
  1. In the upper right corner of the Classic console, click Apps.
  2. Click SQL Analysis .
  3. Select the Subsystem you want to investigate.
  4. (optional) Click Filter  to limit the results displayed based on one or more of the following fields:
    • Plan
    • Program
    For more information, see Filtering the result set for an SQL app.
  5. (Optional) Click Filter Time Interval  to adjust the time period in which you want to view results. For more information, see Selecting the time interval for SQL apps.
  6. To view the details for a row:
    • Select the row and click Details.
    • Double-click the row. 
  7. (Optional) To analyze the statement, click the Explain menu and then select Dynamic.
  8. Click  to return to the subsystem summary view.
  9. (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.


Was this page helpful? Yes No Submitting... Thank you

Comments