The specified integer indicates the level of optimization to be used. Currently, the DB2 connector uses an optimization level of 3 for client SQL statements. Because this setting affects the whole business component, changing it can 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 that addresses the needs of both the where conditions on the driving tables of the query, and the 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 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 does not appear in the SQL log.
Check the business component that produced the SQL statement to determine if there is already a DB2 Optimization Level. If there is, then use that optimization level to explain the SQL of the slow query. 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. If the new optimization level solves the slow query performance, then add the user property name to the business component. |
