Hi all,

we are trying to do some performance optimizations in our application and are therefore analyzing some slow SQL statements.

We are using the tool DB2 Monitor (IBM DB2 UDB Performance monitor) for grabbing the long running SQLs.

Yesterday I found a statement which execution time was 12 seconds (measured by our application itself and by DB2 Monitor). I copied the statement and put it into the DB2 Control Center application in order to let it build the execution plan so that I can find the most expensive parts of the statement. But when I execute this same statement in the Control Center it only takes 0.5 seconds. How is that?

In order to exclude any caching issues I re-executed the statement several times in our application and in the Control Center and each time it took 12 seconds in the app and 0.5 seconds in the Control Center.

Can someone explain to me what I am doing wrong? That way I am unable to find my performance leaks.

Thanks in advance,
Olel

DB2 version: "DB2 v9.1.300.257", "s070719", "WR21393" and FixPak "3".
DB2 type: DB2 Enterprise Server Edition, db2ese, 9.1

I bet my shoes that you're substituting literal values for parameter markers in the WHERE clause. Don't do it; use EXPLAIN PLAN FOR SELECT ... to generate explain information for the original statement.

Not bad... you're right! Looks like this is a common mistake by newbies...
As I am such a newbie can you explain how that "EXLAIN PLAN FOR SELECT" works? I would like to stick to the graphical representation of the plan which is part of the Control Center as I am not really good in reading textual explain plans.

Oh, and why is the query is so much faster with literal values?

Thanks in advance!

You can still use the Control Center/Command Editor to get the visual explain using parameter markers. In the Command Editor, enter the SQL with parameter markers. Then press the "Access Plan" button.

Andy

See if this thread answers your question. Prepared statement not using indexes

As far as I understand that thread there are some optimization possibilities for DB2 if you use literal values.

I think that makes clear why I cannot see any differences when executing queries directly in the Control Center with literal values with indexes created or not.

Is there any way to execute queries in the Control Center with literal values without giving DB2 the opportunity for these optimizations? It would be very helpful for me to test some queries with literal values without having our application running.