Howdy All,

I'm new to the DB2 arena and I've come to learn that the DB2 Optimizer makes different execution decisions not only on data and object structure but also on the database configuration itself such as the size of the buffer pools.

I tuned my queries on a 1 CPU 500 meg box then deployed them to a 2 CPU 1 Gig Box and the execution plan is different and the performance is worse than the smaller box.

My question is how do you avoid, or can you avoid, having to tune your queries each time you deploy it to a new customer or machine?

Thanks for you time,
mX.

You would need to supply details of your situation in order to determine what the issue is, and how it can be addressed. The following is needed:

1. SQL DML statement in question.
2. DDL of objects in the DML statements (tablespaces, tables, and indexes).
3. Description of disk subsystem on both systems (JBOD, RAID-5, etc.).
4. Cardinality of tables and indexes (fullkeycard or that part of the index used in DML predicate).
5. Information from the Explain as to access path selection for both systems.
6. Complete description of your DB2 release and Fixpak levels, and OS platform.

BTW, make sure you run detailed statistics on tables, indexes, with column distribution before you go any further.

Thanks for the reply Marcus.

My question is more general than the tuning of the offending query. The table structures, indexes, data, and the releases of the OS and Database are the same. I've run statisitics and the only thing occuring against these database is my single query for now.

Based on the above being the same across both machines the Optimizer seems to be using different optimization plans because of the machine or the database configuration parameters.

As a consequence of this does this mean that when we deploy this to our customers its possible they will also have different execution plans?

Am I making sense?

I don't know about anyone else, but I cannot answer the question unless I see the details I asked for. There is a reason this is happening and I need that info to figure out why. Once we figure out why it is happening, there is probably a way to prevent it from being a problem when you deploy the application on different systems.

BTW, if the this involves static SQL, the default bind parameters, and the explicit bind parameters need to be looked at.

IMHO 'when you deploy this to your customers its possible they will also have different execution plans' . Optimizer uses statistical weights for CPU,IO etc. Your OS,DB configurations affect the weights. Parameters like OVERHEAD, TRANSFER rate in tablespace definitions affect it too.

Hi mX,

the best way to avoid having to tune your queries is to have a proper database design and use SQL in a straightforward manner - and to know when to stop tuning. You know what kiss means, I presume?

Actually there is so much that may be different at your customer's site you certainly have to pay close attention to steer clear of performance issues.

DB2's optimizer is a cool piece of software and without enough knowledge about exactly what you are comparing to each other, I would rather expect the optimizer to be right about it's choice, unless the SQL in question is complex. If it is, it might be an indication there is something wrong - with database design, requirements, implementation, customer expectation or whatever.

By the way, what about charging your customer some implementation fee by the hour, if your application is nice and straightforward and still won't run fast enough at his site?

Johann