Analyzing an ad hoc statement

Use the following procedure to browse and copy a statement stored in a data set, or to write a statement into the statement box of the Performance perspective.

Important

You can set values that are persistent for all ad hoc statements that you create in the current session in the current workspace. For details, see Setting-SQL-tuning-options.

Related topics

Selecting-a-statement-for-SQL-Analysis

Analyzing-a-statement-from-the-Db2-statement-cache

Analyzing-a-statement-from-a-Db2-package

Analyzing-an-SQL-statement-embedded-in-a-source-code-file (BMC.DB2.SPE2307)

Identifying-performance-issues-in-SQL-statements

To analyze an ad hoc statement

  1. In the Performance perspective, click the Add view NW_plus_circle_o.png menu arrow and then click Add SQL Statement View.
  2. Complete the SQL tab:
    1. Enter a name for the statement.

    2. From the DB2 Connection list, select a Db2 subsystem.

      Tip

      Click NW_plug_connect_square_green.png and specify which Db2 connections to include in your list of favorite connections.

    3. (Optional) Enter the table qualifier.BMC AMI Command Center for Db2 uses this qualifier if the table in the SQL is unqualified.
    4. For the Explain Type, select Dynamic.
    5. At Degree, select the degree of parallel processing to allow:
      • Select Any to allow parallel processing.
      • Select 1 to prohibit parallel processing.
    6. Type or paste the SQL statement into the SQL statement box.

    7. (BMC.DB2.SPE2210) Edit the SQL statement as needed:

      Purpose

      Action

      Find a search string

      1. Click Find/Replace NW_lines_search.png.
      2. Enter the search string in the Search box.
      3. Click Find.

      Find and replace a search string

      1. Click Find/Replace NW_lines_search.png.
      2. Enter the search string in the Search box.
      3. Enter the replacement string in the Replace box.
      4. Click Find.
      5. Click Replace.

      Find and replace all instances of a search string

      1. Click Find/Replace NW_lines_search.png.
      2. Enter the search string in the Search box.
      3. Enter the replacement string in the Replace box.
      4. Click Replace All.

      Make a search case sensitive

      Select the Match case checkbox.

      Enable syntax highlighting

      Select the Use syntax highlighting for SQL statements Command Center option.

      For more information, see Setting-Command-Center-options.

      View SQL statement options

      Click Show more.

      Hide SQL statement options

      Click Show less.

      Undo an action

      Click NW_undo.png to undo the last action.

      Redo an action

      Click NW_share.png to cancel undoing the last action.

  3. Click Generate explain plan NW_generate_explain.png.The Explain results are displayed along with the following tabs:



 

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