Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Command Center for Db2 13.2

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.

Note

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

Identifying-performance-issues-in-SQL-statements

To analyze an ad hoc statement

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

      Undo an action

      Click NW_undo.pngto undo the last action.

      Redo an action

      Click NW_share.pngto cancel undoing  the last action.

  3. Click Generate explain plan NW_generate_explain.png.The Explain results are displayed along with the following tabs:
Classic console
  1. Click the Performance perspective.
  2. Click Add SQL Statement View Add-16.png.
  3. Complete the SQL tab:
    1. Enter a name for the statement.
    2. Select a Db2 subsystem by using either of the following methods:
      • Type the full or partial name of a Db2 connection to select the first subsystem that matches this value in the list of favorite connections.

      • Use the menu arrow to display and choose from a list of connected subsystems.

        Tip

        Click ViewFavoritesList-24.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.
  4. Click Generate explain plan explain-24.png.The graphical Explain diagram of the SQL statement is displayed.

 

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