Hi,
IBM descibe various optimization classes which can be set to optimize db performance. For example:

SET CURRENT QUERY OPTIMIZATION = 1

will set the database to class optimization 1. However, my question is if I execute this in a java application, is this optimization class set only for the current use of the current JDBC connection, for any connection in the connection pool that the application uses or is it set for the entire database?

Any help appreciated.

Hi,
this settings is wailed only for current connection, doesn't effect others connection in connection pool and also does not effect all applications on entire database.

If you would like to set optimization for entire database then you must change db cfg parameter DFT_QUERYOPT like:
db2 update db cfg for <database> using DFT_QUERYOPT 1

Hope this helps,
Grofaty

Thanks for that. I was just wondering though when you say current connection do you mean JDBC connection or database connection?

i.e. just say I am using a connection pool which pools my JDBC connections.

Do I have to issue SET CURRENT QUERY OPTIMIZATION = 1 once and only once for each connection in the pool

or

because I regularly close JDBC connections to return them to the pool, must actually issue this command before I use any connection from the pool?

Connection pooling is irrelevant here. When you get a new connection (either pooled or not), all special registers are set to their default values. So the answer is: yes, you have to set the optimization level each time.

p.s: Are you familiar enough with the optimization levels and the effect of changing them to reduce the optimization from 5 (default) to 1. I'm worrying that you may start "tuning" something without really understanding the impact. (I think it's fair to ask this, given the basic nature of your question.)

Fair question. Requirement is from DBA. I'm just implementing it.

I don't think this is always the case: depending on the connection pool implementation, it may not reset all (or some) connection settings when the connection is returned to the pool.

Still, it is a good practice to always explicitly set your database session environment when you obtain a connection, because you don't have control over what settings might be in effect at that time.

One time I had to ask the java programmer to change the optimization level for an SQL statement. Jsut to be safe I asked them to change it back to the database default level after submitting the SQL. The difference in performance was significant enough to more than justify the two exta SET statements to set and reset the optimization level.

I agree. The question is where the pooling is done. If you use DB2's mechanisms, DB2 initializes connections properly. If you use on some other, possibly home-grown mechanism, you cannot rely on that.