Scheduling SQL optimization tasks

Edit online

You can create schedules for automatically exploring SQL queries to generate recommendations, for deploying recommendations, and for resolving access path regressions without manual intervention. By scheduling these tasks, you can reduce the amount of time that you need to interact manually with IBM® Z Database Assistant.

About this task

Things to cover:
  • This task applies to existing queries only. To evaluate and tune new queries, see Optimizing new SQL queries.
  • This task covers all three types of schedules.
  • Creating schedules is optional; you can manually do all of these tasks on the fly if you want to take action outside of the established schedule.

Procedure

Use the information in the following tasks to schedule SQL optimization tasks.

Creating an exploration schedule

Edit online

The process of generating recommendations for improving SQL performance is called SQL exploration. IBM Z Database Assistant can generate recommendations for improving access path performance, index performance, and for rewriting queries to run more efficiently.

About this task

There are several advantages to creating an exploration schedule:

  • By creating an exploration schedule, you reduce the time associated with interacting with IBM Z Database Assistant through automating the tasks of selecting and exploring queries.
  • A schedule enables explorations to be performed at a specified time, without the need for you to be logged in at that time.
  • By spreading out explorations over time, you also spread out the overhead associated with them over time and potentially avoiding resource spikes that could come from exploring a large number of queries at once.

Creating an exploration schedule is optional, but recommended. If you choose not to create an exploration schedule, you must manually initiate explorations from the SQL tuning page. There is no default exploration schedule.

Note: IBM Z Database Assistant will explore as many statements as it can within the time constraints specified by the kick-off and end times. If not enough time is available to explore all qualified statements, you have two options. You can create a repeating schedule to provide additional time windows for exploring the remaining statements. Alternatively, you can increase the amount of time allowed for the scheduled exploration.

Procedure

To create an exploration schedule:

  1. Open Manage connections / Connections details.
  2. Select the connection for which you want to create an exploration schedule from the drop-down menu.
    ]SQL optimization schedules open by default.
  3. In the upper-right section of the Exploration schedule section, click Create exploration schedule.
  4. In the Create exploration schedule dialog, specify the following values:
    Schedule name
    An optional user-defined descriptor for a particular exploration.
    Statement type
    Indicates the type of SQL statement explored, either Static or Dynamic.
    Frequency
    The rate at which the exploration will occur, either Daily, Weekly, or Once.
    Options available when the chosen frequency is "Daily"
    Effective from
    The date that the exploration schedule starts.
    Effective until
    The date on which the exploration schedule ends.
    Kick-off time
    The time when the exploration will begin.
    Max end time
    The time at which an exploration will end if it has not finished already.
    Filter
    Collection ID
    A logical grouping of packages within Db2.
    Package name
    The name that is used to identify the package.
    Label
    The statement statuses that are included in the exploration.
    Package version
    The version of the package for which IBM Z Database Assistant has collected data.
  5. Select Validate access path to specify that the proposed new access path will be internally validated by IBM Z Database Assistant. The default setting is to validate the proposed new access path.
    Important: If you choose not to validate the access path, the resulting recommendations might cause a performance regression.
  6. Click Confirm to create the exploration schedule.

Creating a deployment schedule

Edit online

You can choose to create a deployment schedule to specify when IBM Z Database Assistant will automatically implement the results of an exploration.

About this task

There are several advantages to creating deployment schedules:

  • By creating a deployment schedule, you reduce the time associated with interacting with IBM Z Database Assistant to deploy the results of explorations.
  • You can deploy recommendations at a specified time, without the need for you to be logged in at that time.

Creating a deployment schedule is optional, but recommended. If you choose not to create an deployment schedule, you must manually deploy recommendations from the SQL optimization page. There is no default deployment schedule.

Procedure

To create a deployment schedule:

  1. Open Manage connections / Connections details.
  2. Select the connection for which you want to create a deployment schedule from the drop-down menu.
    ]SQL optimization schedules open by default.
  3. Click Create deployment schedule.
  4. In the upper-right section of the Deployment schedule section, click Create exploration schedule.
  5. In the Create deployment schedule dialog, specify the following values:
    Deployment label
    An optional user-defined descriptor for a particular deployment.
    Statement type
    Indicates the type of SQL statement deployed, either Static or Dynamic.
    Frequency
    The rate at which the deployment will occur, either Daily, Weekly, or Once.
    Effective from
    The date that the deployment schedule starts.
    Effective until
    The date on which the deployment schedule ends.
    Kick-off time
    The time when the deployment will begin.
    Max end time
    The time at which an deployment will end if it has not finished already.
    Filter
    Collection ID
    A logical grouping of packages within Db2.
    Package name
    The name that is used to identify the package.
    Package version
    The version of the package for which IBM Z Database Assistant has collected data.
  6. Select Deploy validated recommendations only to only deploy explorations that have been validated. The default setting is to deploy validated recommendations only.
    Important: If you choose not to validate the access path, the resulting recommendations might cause a performance regression.
  7. Select Rebind packages to automatically rebind packages when a rebind is needed to implement the results of an exploration. The default setting is to rebind packages.
    Note: If you choose not to automatically rebind, you must perform the rebind outside of Db2.
  8. Click Confirm to create the deployment schedule.

Creating a regression resolution schedule

Edit online

You can choose to resolve access path regressions either by creating a regression resolution schedule to specify when IBM Z Database Assistant will automatically resolve the access path regression or by manually resolving the access path regression through the IBM Z Database Assistant user interface.

About this task

IBM Z Database Assistant has a default regression resolution schedule, which resolves access path regressions every 5 minutes. If this schedule is acceptable, you do not need to make any changes. If changes to the default schedule are needed, use the procedure described below to modify the default schedule.

By creating a regression resolution schedule, you reduce the time associated with interacting with IBM Z Database Assistant through automating the tasks of selecting and resolving access path regressions, which can reduce the impact of the performance regression.

Procedure

To create or modify a regression resolution schedule:

  1. Open Manage connections / Connections details.
  2. Select the connection for which you want to create an regression resolution schedule from the drop-down menu.
    ]SQL optimization schedules open by default.
  3. In the upper-right section of the Regression resolution schedule section, click Create regression resolution schedule.
  4. In the Create regression resolution schedule dialog, specify the following values:
    Regression resolution label
    An optional user-defined descriptor for a particular regression resolution schedule.
    Statement type
    Indicates the type of SQL statement, either Static or Dynamic.
    Frequency
    The rate at which the regression resolution will occur, either Daily, Weekly, Once, or Every nth minutes.
    Options available when the chosen frequency is "Daily"
    Effective from
    The date that the regression resolution schedule starts.
    Effective to
    The date on which the regression resolution schedule ends.
    Kick-off time
    The time when the regression resolution will begin.
    Max end time
    The time at which a regression resolution will end if it has not finished already.
    Filter
    Collection ID
    A logical grouping of packages within Db2.
    Package name
    The name that is used to identify the package.
    Package version
    The version of the package for which IBM Z Database Assistant has collected data.
    Note: Specifying a frequency of "Once" does not provide real automation because after the schedule runs, there is no schedule in effect anymore. Any regression that occurs after the schedule runs will not be automatically resolved.
  5. For static statements, select Rebind packages to automatically rebind packages when a rebind is needed to resolve a regression. The default setting is to rebind packages.
  6. Click Confirm to create the regression resolution schedule.