Problem solve Get help with specific problems with your technologies, process and projects.

Tuning an access path using QMF

I need some help with a tuning issue. Program A has 10 SQL statements in it. The program is bound, and when I view the explain, query 5 has a poor access path. The programmer has tried all the "optimizer tuning tricks," to no avail. When I pull this query out of the program, put it into QMF, and run an explain, it chooses a very different, and better, access path. Why? Why don't I get the same access path that is found in the explain for the program? If I can't get the same access path the program gets (and I am in the same environment), how can I help the programmer tune this query?

Although it is not possible for me to give an absolute answer to this question, I can give you a good guess at what I think is causing the difference. The SQL statement in the program is bound as static SQL; the SQL statement in QMF is dynamic SQL. DB2 may be formulating a better access path for the dynamic SQL because it has access to the host variables. Have you considered using dynamic SQL in the application program (only for the query that is causing you problems)?

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.