Tuning SQL statements that contain variables when you are developing SQL routines
rdheath 120000A31Q Visits (4059)
Here's a handy tip for a Monday. You might already know that, in InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner, you can use the client to develop SQL stored procedures. When you are working in a routine editor, you can right-click anywhere in an SQL statement and select Start Tuning to open the Query Tuner workflow assistant. The statement appears in the Query Tuner workflow assistant. From this location, you can run advisors and tools to get analyses and recommendations.
Until version 3.1, if the SQL statement contained variables, the variables were present in the statement after it was imported into the workflow assistant, and the statement couldn't be tuned. However, in version 3.1, the variables are replaced with parameter markers.
For example, the SQL statement in this routine uses the variables ORDE
After you right-click in the routine editor and select Start Tuning, the Query Tuner workflow assistant opens to the Run Single-Query Advisors and Analysis Tools page. The variables are replaced with parameter markers, using the appropriate casts to indicate data types.
From this point, you can click the Select What to Run button and choose the tools and advisors that you want to run on the statement, and proceed tuning as you would with any other statement.
Robert Heath, firstname.lastname@example.org