Troubleshooting SQL statements

You can troubleshoot SQL statements for Db2 errors and performance problems. Use the SQL Errors app to identify SQL statements that are generating SQL errors. Use the Db2 statement cache view and SQL Analysis app to identify SQL statements with performance problems. For more information about the Db2 statement cache view, see Analyzing-a-statement-from-the-Db2-statement-cache.

(BMC.DB2.SPE2310) Use the SQL Alerts app to view BMC AMI SQL Performance interval alerts generated for SQL statements whose cost has passed a user-defined threshold.

Related topics

Tuning-SQL

Using

Overview of the SQL Errors app

The SQL Errors application displays SQL errors captured by BMC AMI Apptune for Db2. You can view a list of errors generated on one or more subsystems. You can also drill down to identify the programs and statements that are causing the errors.

The following table lists the actions that you can perform in the SQL Errors app:

Purpose

Action

Change the time interval

Select the time interval from the Timeframe list.

Change the subsystems displayed

  1. Click Options NW_gear.png.
  2. Select the Subsystems that you need to investigate.
  3. Click OK.

Refresh the data displayed

Click Refresh refresh_app-24.png.

View errors for a subsystem

Perform one of the following actions:


    • Click the bar for a subsystem on the bar graph.
    • Double-click a row on the list of subsystems.

Adjust the time interval for the errors displayed

Select the time interval from the Timeframe list.

Filter the rows displayed by:

  • Plan
  • Program
  • User Name
  • Client Application

Click Filter Filter_app_Contents-24.png.

Clear the filters

(BMC.DB2.SPE2404)

Click Clear all filters NW_ClearAllFilters.png.

Clear all filters performs the following actions:

  • Removes all filters entered in the Filter dialog box
  • Resets the pie-chart legend to its original state

Return to the previous view

Click Back back_icon_app-24.png.

View a list of occurrences of an error code

Perform one of the following actions:

  • Select the error from the Occurrences for Error Code list.
  • Click the section of the pie chart that represents the error code.

View a list of occurrences of all error codes

(BMC.DB2.SPE2410)

Select All from the Occurrences for Error Code list.

View the details for an occurrence of an error code

Double-click a row on the list of occurrences.

Suppress the display of the Options dialog box

  1. Click Options NW_gear.png.
  2. Clear Always shows this panel on startup.
  3. Click OK.

Open the SQL Analysis app

Click SQL_analysis_app-24.png.

Open the SQL Alerts app

(BMC.DB2.SPE2310)

Click NW_sql_alerts.png.

Overview of the SQL Analysis app

The SQL Analysis application displays a list of the top 100 SQL statements with the highest CPU time for a given subsystem and time period. You can also view the details for a statement.

The following table lists the actions that you can perform in the SQL Analysis app:

Purpose

Action

Choose subsystem

Select the subsystem from the Subsystems list.

Refresh the data displayed

Click Refresh refresh_app-24.png.

View details for an SQL statement

Double-click a row on the list of SQL statements.

Adjust the time interval for the SQL statements displayed

Select an interval from the Timeframe list.

Filter the rows displayed by:

  • Plan
  • Program
  • User Name
  • Client Application

Click Filter Filter_app_Contents-24.png.

Clear the filters

(BMC.DB2.SPE2404)

Click Clear all filters NW_ClearAllFilters.png.

Removes all filters entered in the Filter dialog box.

Export the list of SQL statements currently displayed

(BMC.DB2.SPE2210)

  1. Click Generate Report

  2. Select one of the following options:

    • CSV - With SQL text preview 
    • CSV - With full SQL text
    • PDF

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

  3. Enter a name for the report
  4. Click Save

Investigate performance trends for a SQL statement

(BMC.DB2.SPE2310)

Perform one of the following actions on the list of SQL statements:

  • Select a row and click Trend.
  • Open the context-menu for a row and select Trend.

Return to the previous view

Click Back back_icon_app-24.png.

Customize the subsystem summary grid columns

(BMC.DB2.SPE2410)

  1. Click Visible Columns.
  2. Perform any of the following actions: 
    • To hide a column, clear the check box next to the column.
    • To show a column, select the check box next to the column.
    • To change the order of the columns, drag a column to the new position.
    • To restore the default column display, click Restore Defaults.
  3. Click OK.

Suppress the display of the Options dialog box


  1. Click Options NW_gear.png.
  2. Clear Always show this panel on startup.
  3. Click OK.

Open the SQL Errors app

Click SQL_errors_app-24.png.

Open the SQL Alerts app

(BMC.DB2.SPE2310)

Click NW_sql_alerts.png.

Overview of the SQL Alerts app

(BMC.DB2.SPE2310)

The SQL Alerts application displays a list of the BMC AMI SQL Performance interval alerts generated for SQL statements whose cost has passed a user-defined threshold. 

The following table lists the actions that you can perform in the SQL Alerts app:

Purpose

Action

Adjust the timeframe for the alerts displayed

Select an interval from the Timeframe list.

Refresh the data displayed

Click Refresh refresh_app-24.png.

Display the alerts for a subsystem

Perform one of the following actions:

  • Click the bar for a subsystem on the bar graph.
  • Select a subsystem from the Alerts on list.

View the details for an alert

Perform one of the following actions on the alerts table:

  • Select a row and click Details.
  • Double-click a row.
  • Open the context-menu for a row and select Details.

Investigate performance trends of the SQL statement that caused an alert

Perform one of the following actions on the alerts table:

  • Select a row and click Trend .
  • Open the context-menu for a row and select Trend.

Select the display format of the historical basis for an alert

Select one of the following options:

  • Chart
  • Tabular

Chart is not available for disruption alerts. 

Return to the previous view

Click Back back_icon_app-24.png.

Open the SQL Analysis app

Click SQL_analysis_app-24.png.

Open the SQL Errors app

Click SQL_errors_app-24.png.

This section provides more information about the following topics:

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*