Bookshelf v7.5: DB2 Optimization Level


Parent Object Type	Business Component
Description	Currently, the DB2 connector uses an optimization level of 0 for optimizing client SQL statements. In some cases, certain SQL statements may perform suboptimally using optimization level 0. This user property allows an implementor to change the optimization level of all of the SQL statements produced by the given business component. Because this setting affects the whole business component, changing it may adversely affect the performance of other SQL statements produced by the same business component. Before using this option, it is extremely important to analyze slow performing SQL statements, and then consider all of the options available for tuning the statement. Some of these tuning methods are as follows: Make sure there is an index which addresses the needs of both the where conditions on the driving tables of the query, and order by clause. If the business component has been customized, simplify the customization. Remove one or more columns from the order by clause. Change the optimization level. The DB2 Optimization Level user property can only be used in expert mode.
	The first step to analyzing performance is to start the Siebel client with the `/s <filename>` option to log all of the SQL statements. This log file shows the time spent executing each SQL statement. Identify the statements that are slow. NOTE: The DB2-specific SQL generator adds an "optimize for 1 row" clause to the end of the SQL statement. This clause will not show up in the SQL log. Check the business component that produced the SQL statement to determine if there is already a DB2 Optimization Level User Property. If there is, then use that optimization level to explain the SQL of the slow query. Otherwise, use optimization level 0. Paste the suspected slow query into one of the explain utilities, add the "optimize for 1 row" clause, and set the optimization level to the appropriate value. Generate a query plan for the statement and analyze it. If you conclude that changing the optimization level is the best approach to increasing the performance of the query, then re-explain it using a different optimization level. Trying level 3 first is recommended, because some complex queries that perform poorly with optimization level 0 often perform better with optimization level 3. If the new optimization level solves the slow query performance, then add the user property name to the business component.