Hi,
I have a strange performance problem. On my production system DB2 ESE v8.2 fp11 on Linux executing SQL from Command Window SQL runs for 2 seconds. But the same SQL through CICS application using static SQL (use of SQL package) it runs for 15 minutes and occupies 100% of DB2 computer CPU during execution.

What should I check on my production environment? What are conditions that influence so dramatically to static SQL and that does not impact dynamic SQL?

To be even more strange the same program runs for 2 seconds on test environment as well as Command Window execution. So it go to be something different on production environment. What should I check first?

Thanks,
Grofaty

I'd think of statistics that influence the access path.
Do you have host variables in your static SQL?
What's the (static) access path (Explain info)?

Peter,
1. On both systems statistics are up to date.
2. Yes I have host variables in static SQL.
3. Can you please provide more info how to create explain to get access path for static SQL?
Thanks,
Grofaty

Run the explain using parameter markers (?) in the SQL predicates instead of literals.

Select * from emp where empno = ?

In that case, you will have to "convince" the optimizer to use a different access path, viz. the one used by the dynamic query.

So, run explain both with parameter markers ("?") and with explicit constants, and compare the access paths, especially the index(es) used in the two cases.

Most of the time, you can change the optimizer's opinion about an index that's being used and shouldn't by a simple query rewrite. Namely by making the condition on the (first) column in that index look "less efficient", e.g. by adding "+ 0" to its right-hand side (if numeric).

How would you code that? where integer_column = +0?

If you originally hadyou change that to (The question mark can be either a host variable, or a constant, or even an expression.)

Hi,
thank you very much for your tips. I solved the problem.

I have found out that SQL was very poorly written. I have rewritten SQL and now it works for 1 second on test and also on production environment.

I have also found out that explain on test and production system can be very different. Test computer is 20% faster that production that explains why there can appear such a problem.
Thanks a lot. No more help is needed for this problem,
Grofaty