Q
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)?

This was last published in January 2003

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close