Creating and deploying plan hints for SQL statements that run on DB2 for z/OS

You can create, validate, and deploy a plan hint for an SQL statement that runs on a DB2® for z/OS® subsystem. With a plan hint, you can set criteria to assist the DB2 optimizer to select the optimal execution plan for the SQL statement.

Before you begin

About this task

Giving plan hints to DB2 is useful in the following situations:

Procedure

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. Select one of the captured statements and click the Invoke Advisors and Tools button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
  3. On the left side of the workflow assistant, under Single Query expand Advanced and select Create Plan Hint. The workflow assistant opens the Customize Access Plan with Plan Hint page of the Review section. This page contains three sections:
    Join Diagram
    A join diagram displays this information:
    • The tables that the query will touch, which the diagram shows as nodes
    • The relationships between the tables, which the diagram shows as lines between the nodes and that are generated based on the analysis of the query's predicates
    • Both local and join predicates

    Double-click a table to customize how the table is referenced in the access plan for the SQL statement. The customization appears in the table in the in the Overview section at the bottom of the page. The customization is not yet active. You can continue to add other customizations to the plan hint.

    Editable Join Sequence Diagram
    A join sequence diagram displays the tables' join sequence for the specified access plan. You can perform the following actions:
    • Delete selected nodes
    • Add a table reference node
    • Double-click a table to customize how the table is referenced in the access plan for the SQL statement.
    • Add a join operator node
    • Double-click a join operator node to change the join to a different type of join.
    • Drag a table on top of another table to switch the positions of those tables in the join sequence
    All changes that you make to the existing join sequence are listed in the Overview table at the bottom of the page. The workflow assistant immediately checks them. Errors and warnings are displayed in the Problems section at the bottom of the page.

    If you want to remove all changes that you have made to a join sequence, click Default Join Sequence.

    When you have a candidate plan hint, click the Validate Plan Hint button in the top-left corner of the page. In the Hint Validation Options window, you can specify parameters of the application environment in which the SQL statement runs. After you click OK, the Hint Validation Result window opens.

    Hint definition
    When you make a change in the join diagram or join sequence diagram, a new row is added to this section. If the workflow assistant detects any errors in the entry, has a warning regarding the entry, or suggests a change to the entry, and indicator appears in the Problems tab. Click that tab to view the information.
  4. Create a plan hint by making changes in the join diagram and join sequence diagram, creating entries in the Hint Definition section.
  5. Validate the plan hint. When you have the entries that you want in the Hint Definition section, have resolved all errors, and responded to all of the warnings, click the Validate hint icon at the top of the page. In the Validate Hint window, customize parameters of the application environment or leave the default values. For information about these options, click the Help icon in the lower-left corner of the window or type F1. After you click Validate, the Result of Validating the Hint window opens. For help with this window, click the Help icon in the lower-left corner or type F1.
  6. Deploy the plan hint after resolving the problems that you found in the validation process.
    1. Click the Deploy the Hint icon.
    2. In the Deploy Hint window, specify options, such as the name of the hint and the number to use for identifying the SQL statement. For help with this window, click the Help icon in the lower-left corner or type F1.
    3. Click Deploy.
    If the workflow assistant is able to deploy the hint, the Result of Deploying the Hint window opens and displays this information:
    • The SQL statements that were used to create the plan hint in the PLAN_TABLE table.
    • The new records that are in the PLAN_TABLE table.

Feedback