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. Enter the HTTP port into the Port text field.
    5. Optionally, you can customize the host, connection name, user, or password for the connection.
    6. Select the Test Connection to validate the settings enable connectivity.
    7. Select the Apply and Close button to save your connection settings.
  3. To configure tuning services of your Db2 for z/OS connection:
    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 SQL Tuning Services server list, select the tuning server to use when tuning statements in files associated with this Db2 for z/OS connection.
    4. Optionally, if you want to use a tuning profile, check the Use tuning connection profiles checkbox. Then select a pre-existing Database connection profile from the list or use the New button to create a profile.
    5. To use Multi-Factor Authentication (MFA) when performing tuning operations, make sure the Use tuning connection profiles checkbox is unchecked, and on the General tab of your Db2 for z/OS Connection set the Login Method to MFA Token. When these options are selected, you will be prompted to enter an MFA token each time you send a tuning request.
    6. Click Apply and Close.
Limitation: Performing tuning actions without using a tuning profile requires the EXPLAIN tables first be created. Creation of EXPLAIN tables is currently only possible from a tuning profile.

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:

Tuning without a Tuning Profile

If you decide to not use a tuning profile, the authentication information of the Db2 for z/OS) connection associated with the edited file will be used to establish connectivity from the tuning server to the Db2 for z/OS server. Tuning without a profile supports security mechanism 3 (User ID and Password) and is required for security mechanism 7 and 9 (Encrypted User ID and Password).

If you are tuning SQL without a tuning profile and you are using MFA, you will be prompted to enter an MFA token twice: when you first connect to the Db2 for z/OS) server and again when you initiate the tuning action. The tokens must be unique. Use the IBM Verify app to generate these MFA tokens.