Setting options for the tuning advisors
rdheath 120000A31Q Visits (4645)
Each of the tuning advisors in IBM Data Studio, IBM InfoSphere Optim Query Tuner, and IBM InfoSphere Optim Query Workload Tuner has at least one option that you can set to influence the recommendations that they generate. Some of the advisors have exactly one option, but the advisors that help with statistics and indexes have many more than that. In this blog, I'd like to explain some of these options from time to time because a number of them look complicated, are not documented well, or both.
The options appear in two places. In one place, you can set values globally, in which case the values apply to all statements and workloads that you tune while working in the current workspace. In the other place, you can set values for individual statements and workloads, overriding the global values.
Before you start tuning a statement or a query workload, you can set values for these options in the global preferences by selecting Window > Preferences. When the Preferences window opens, in the tree view on the left expand the Data Management entry, and the expand the Query Tuner entry. Expand the Tune Single Query entry and the Tune Workload entry to see the entries for the advisors. Select one of the advisors and the right side of the Preferences window shows the options for it. After you set values for the options for an advisor, click the Apply button if you want to set preferences for another advisor before closing the Preferences window. After you've finished setting values and have closed the window, you can start tuning.
You can also set values for the advisor options after you have captured a statement or a workload and are either on the Run Single-Query Advisors and Tools page or the Run Workload Advisors page of the Query Tuner Workflow Assistant. On the left side of the page, select Set Advisor Options. The options that appear are the same ones that appear in the Preference window. The difference is that the values that you set here apply only to the current statement or workload that you are running.
Take some time to look through the options and let me know which ones you would like me to explain. In my next post, I'll start with the first option for the Workload Statistics Advisor for DB2 for z/OS, unless someone asks me to cover a different one first.
Robert Heath, email@example.com (Start the subject line with "MDL blog: ")
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner