QUERY OPTIMIZATION

[sree]

Hi

Is it a good practice to use the statement
"SET CURRENT QUERY OPTIMIZATION = 9"
within a stored procedure?

Does it improve the performance of the Stored procedure?
What is the default value , if we are not specifying a value for this?

Thanks
Sree

[Blair Kenneth Adamache]

The default value is 5. I would avoid altering this unless there are
specific queries that you can prove run better with a different
optimization level. 9 means perform more optimization, which means more
time is spent in the DB2 optimizer trying to find a better plan. The
time spent in the optimizer can sometimes be greater than executing a
simple plan (optimization=0) for a small table.

See:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001007.htm

Post by sree
Hi
Is it a good practice to use the statement
"SET CURRENT QUERY OPTIMIZATION = 9"
within a stored procedure?
Does it improve the performance of the Stored procedure?
What is the default value , if we are not specifying a value for this?
Thanks
Sree