Tuning SQL statements

Use the Db2 for z/OS > Tune Selected SQL pop-up menu action to open the Tuning Actions window from an editor.

From the Tuning Actions window, you can choose these actions:
  • Visual Explain: Generates a graphical representation of a query path.
  • Statistics Advisor: Generates RUNSTATS for a query.
  • Capture Query Environment: Captures the runtime environment for a query.

Before you start

IBM® Developer for z/OS® uses the Db2® for z/OS SQL Tuning Services server to run the tuning actions. Before you can use these actions, you must create a connection to the SQL Tuning Services server and configure tuning options.
  1. Gather the following information from a Db2 for z/OS administrator:
    • The port to use to connect to the server.
    • The name of a database connection profile that has EXPLAIN tables configured. During the following procedure, you can also create a database connection profile and generate EXPLAIN tables for it.
  2. To create a connection to the server:
    1. In the Remote Systems view, expand a z/OS system connection, and then expand the Db2 for z/OS subsystem.
    2. Right-click SQL Tuning Services Servers and select New > SQL Tuning Services Server.
    3. Log in to the remote system using your Db2 user ID and password.
    4. Type the HTTP port range, and then click Test Connection. Developer for z/OS connects to the tuning server.
  3. To configure tuning services:
    1. In the Db2 for z/OS subsystem, expand Db2 for z/OS Connections, right-click a database connection, and select Properties.
    2. From the list of properties pages, click Tuning.
    3. From the Database connection profile list, choose a profile. You can also click New to create a profile.
    4. Click Apply and Close.
To learn more about the SQL Tuning Services server, EXPLAIN tables, and database connection profiles, see these topics:

Tuning SQL

After you connect to a Db2 SQL Tuning Services server, you can select and tune SQL statements from the COBOL, PL/I, SQL, or z Systems® LPEX Editor.

  1. Open the editor on a file that contains SQL statements.
  2. Select an SQL statement, and then right-click and select Db2 for z/OS > Tune Selected SQL (Ctrl+Alt+T or ⌥+⌘+T).
    Tip: To set options for the Tune SQL commands select the Tune SQL Options menu item. The context-sensitive helps contain descriptions of the options on the Tune SQL Options page.
  3. On the Tuning Actions window, select one or more tuning actions, and then click OK.

    The tuning data is displayed in the Remote System Details view.

  4. To see the output of each tuning action, select it and click Open Results.
For more information about the tuning actions, see these topics: