Creating and deploying optimization profiles for SQL statements that run on DB2 for Linux, UNIX, and Windows

You can use an optimization profile to influence directly how an access plan for an SQL statement is created. In the profile, you can specify the indexes to use, how to access tables that the SQL statement references, the join method to use, and other settings.

Before you begin

About this task

An optimization profile can contain global guidelines, which apply to all data manipulation language (DML) statements that are executed while the profile is in effect, and it can contain specific guidelines that apply to individual DML statements in a package.

Procedure

To open the visual editor for creating, validating, and deploying optimization profiles:

  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-hand side of the workflow assistant, under Single Query expand Advanced and select Create Optimization Profile.

What to do next

Use the sections of the Customize Access Plan with an Optimization Profile page to specify guidelines for an optimization profile.

This page consists of three sections.
Join diagram and join sequence diagram
This section appears in the top-left corner of the page.

Join diagrams display as nodes the tables that a statement references. Edges between the nodes show the relationships between the tables. The type of predicate that an edge represents appears alongside the edge. Predicates can be local predicates or join predicates. You can double-click a node to change how the access plan accesses the corresponding table.

Join sequence diagrams display the join sequence in the access plan for an SQL statement.

Each node that represents a table displays the following information:
  • The name of the table in the statement as rewritten by the DB2 optimizer
  • The fully-qualified table name
  • The cardinality of the table
  • The type of access that is specified for the table
Editable join sequence diagram
This section appears in the top-right corner of the page.

Edit the join sequence by changing the type of joins, or by adding or deleting joins. You can also add and remove nodes that represent tables that the SQL statement references.

Guidelines in the Optimization Profile
This section appears in the bottom portion of the page.
You can use the Guidelines in the Optimization Profile section to complete these tasks:
Set guidelines for all statements that use the optimization profile
  • Enable or disable the use of materialized query tables. If enabling the use of them, can specify which MQTs to consider using.
  • Enable or disable consideration of computational partition group optimization. If you enable this consideration, then you can specify the partition group for the DB2 optimizer to consider using.
  • Override the setting of the REOPT bind option with a different value.
  • Override the setting of the DEGREE bind option with a different value.
  • Override the setting of the QUERYOPT bind option with a different value.
  • Enable or disable the collection of real-time statistics. If you enable this collection, you can specify the maximum amount of time to allow the DB2 optimizer to collect these statistics. If the optimizer estimates that collecting them will require more time, the optimizer does not collect them.
  • Enable MQT enforcement.
Set guidelines for the current SQL statement
  • Override the setting of the REOPT bind option with a different value.
  • Override the setting of the DEGREE bind option with a different value.
  • Override the setting of the QUERYOPT bind option with a different value.
  • Enable or disable the collection of real-time statistics. If you enable this collection, you can specify the maximum amount of time to allow the DB2 optimizer to collect these statistics. If the optimizer estimates that collecting them will require more time, the optimizer does not collect them.
  • Enable MQT enforcement.
Set guidelines that affect the transformations that are considered during the query rewrite optimization phase, which transforms the original statement into a semantically equivalent optimized statement.
  • Enable or disable the IN-LIST predicate-to-join rewrite transformation.
  • Enable or disable the NOT-EXISTS predicate-to-anti-join rewrite transformation.
  • Enable or disable the NOT-IN predicate-to-anti-join rewrite transformation.
  • Enable or disable the subquery-to-join rewrite transformation.
Customize how a table is accessed
  • Set a plan optimization guideline.
  • Double-click the table in the join diagram and change the way that the table is accessed.

You can check your optimization profile for problems, so that you can correct them before deploying the profile.

Finally, you can deploy the optimization profile. Deploying means inserting the optimization profile as a row in the SYSTOOLS.OPT_PROFILE table.

Use the OPTPROFILE bind option to specify that an optimization profile is to be used at the package level, or use the CURRENT OPTIMIZATION PROFILE special register to specify that an optimization profile is to be used at the statement level.

This special register contains the qualified name of the optimization profile that is used by statements that are dynamically prepared for optimization. For CLI applications, you can use the CURRENTOPTIMIZATIONPROFILE client configuration option to set this special register for each connection.


Feedback