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

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

Before you begin

Ensure that you have the authorizations and privileges that are required for creating, validating, and deploying plan hints.
Ensure that you have the necessary privileges for tuning single SQL statements.
In the Data Source Explorer, connect to the DB2 subsystem, if the connection is not already open.
If you are connected to DB2 10 for z/OS NFM, ensure that the required APARs are applied.
Ensure that the DB2 subsystem that the SQL statement runs against is configured for tuning single SQL statements.
Ensure that the local cache of the system catalog is current.

About this task

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

You want to ensure consistency of response times across rebinds and across release migrations. When a plan or package is rebound, the access path is reformulated. If the database or application has changed, or if DB2 has new function that causes it to choose a different access plan, you might find it useful to use an old access plan if the new plan does not perform as well.
You want to temporarily bypass the access plan chosen by DB2.

Optimization hints consist of three different types of hint:

PLAN_TABLE hints

This type of hint to tries to enforce a particular access path for an SQL statement that is issued by a specific single authorization ID. It uses rows in a PLAN_TABLE instance that owned by the same authorization ID to apply hints based on the values specified for the OPTHINT bind option or the CURRENT OPTIMIZATION HINT special register.

Statement-level access path hints (DB2 10 for z/OS NFM or later)

This type of hint specifies that DB2 tries to use specified PLAN_TABLE rows to determine the access path for matching SQL statements. You can also create access path hints that apply only to matching statements from a specified scope.

Optimization parameter hints (DB2 10 for z/OS NFM or later)

This type of hint specifies that DB2 uses certain optimization parameters, such as the following subsystem parameters and options, to process all statements that match the hint:

REOPT bind option
STARJOIN subsystem parameter
PARAMDEG subsystem parameter (MAX_PAR_DEGREE column)
CDSSRDEF subsystem parameter (DEF_CURR_DEGREE column)
SJTABLES subsystem parameter

You can also create hints that specify optimization parameters and that apply only to matching statements from a specified scope.

You cannot deploy more than one type of hint at a time. However, you can deploy different types of hints for a single SQL statement sequentially. See "Coexistence of optimization hints".

Tip: If you are not sure which type of hint is right for your situation, you can define all three types in the editor, and then deploy them one at a time for testing.

Procedure

To create, validate, and deploy an optimization hint:

Capture or select the SQL statement for which you want to create an optimization hint.
- If you want to work from a single captured SQL statement, follow these steps:
  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 the statement and click the Tune Statement button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
- If you want to work from an SQL statement that is in a query workload, follow these steps:
  1. Click the Manage tab on the left side of the workflow assistant.
  2. On the Manage Workloads page, select the query workload that contains the SQL statement.
  3. In the More actions field, select Show Statements.
  4. On the Show Statements page, right-click the SQL statement and select Run Single-Query Advisors and Tools on the Selected Statement. The Run Single-Query Advisors and Analysis Tools page of the Invoke section opens.
On the left side of the workflow assistant, under Single Query expand Advanced and select Create Optimization Hint. The workflow assistant opens the Customize Access Plan with Optimization 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 optimization 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.
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.
Create an optimization hint.
- If you want to create a PLAN_ABLE hint or a statement-level access path hint, you can do so by making changes in the join diagram and join sequence diagram. When you make a change in the join diagram or join sequence diagram, a new row is added to the Table Access subsection of the Hint Definition 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.
- If you want to create an optimization parameter hint, you can do so by changing values in the Optimization Parameters subsection of the Hint Definition section.
Validate the optimization 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.
Resolve any problems that the validation process found.
Deploy the optimization hint.
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.

Feedback