Hi .

I want to know how to set optimization level for any query in a single statement. I mean say I have query " select * from A", I want that for this query optimization setting should be 3. One way to do so is execute "set optimization level = 3 for current query " and execute that as a statement before it. But I want to do so in a single statement .(so that it can be automated)...what is the syntax for that?

To my knowledge there is no single command to do what you want.

Andy

kunal, if you have a need for automating try thinking into changing db cfg parameter for all statements. The level 3 is better for OLTP applications. The default is 5. So lowering this parameter on db cfg level can eliminate the need of any optimization level by statement.

Hi, The reason why I dont want to change my default setting to 3 is coz ours is basically a data warehouse application with 80% data warehouse OLAP queries which works fine but there are few OLTP queries fired for which Db2 gives good performance only if we lower OPT level..Thats why I am looking for a single statement?

You want to look at the following SQL command:

SET CURRENT QUERY OPTIMIZATION

I am not sure if this applies globably or only to the session that issues it.

BTW, in my tests of suing DB2 with the TCP-H benchmarks, some queries run faster with level 7.

Marcus,
It only applies to the session. the SET DB CFG FOR bdalias using DBF_QUERYOPT sets it globally. The original post stated that he was looking for a way around using SET CURRENT QUERY OPTIMIZATION.

Andy