Siebel Developer's Reference > Business Component User Properties > Doing Other Work >

Specifying the DB2 Optimization Level for SQL Statements

The DB2 Optimization Level user property modifies the optimization level that Siebel CRM uses for the SQL statements that a business component creates. The value for this user property must contain an integer that indicates the level of optimization that Siebel CRM uses. The DB2 connector uses an optimization level of 3 for each client SQL statement.

CAUTION:  The value that you use for the DB2 Optimization Level user property affects the entire business component. Modifying it can adversely affect the performance of other SQL statements that this business component creates. It is extremely important that you analyze SQL statements that Siebel CRM performs slowly, and that you use other tuning options before you modify the DB2 Optimization Level user property.

To specify the DB2 optimization level for SQL statements

1. Use other tuning options:
   • Make sure an index exists that addresses the needs of the WHERE conditions and the ORDER BY clause on the tables that Siebel CRM uses in the query.
   • Remove one or more columns from the ORDER BY clause.
   • If the business component is customized, then simplify this customization.
2. If Step 1 does not fix the problem, then proceed to Step 3.
3. Analyze performance.

   You can use the /s <filename> option when you start the client. This option causes Siebel CRM to log all SQL statements. This log file includes the time that Siebel CRM spends to run each SQL statement. You can use it to identify the statements that run slowly. Note that the DB2 SQL generator adds the following clause to the end of the SQL statement. This clause does not display in the SQL log:

   optimize for 1 row

4. If a DB2 Optimization Level user property already exists on the business component that creates the SQL statement, then examine this optimization level to determine if it causes the slow SQL query.
5. Paste the suspected slow query into one of the explain utilities, add the following clause, and then set the optimization level to the appropriate value:

   optimize for 1 row

6. Create a query plan for the statement, and then analyze it. If you conclude that modifying the optimization level is the best way to improve query performance, then use a different optimization level.
7. If the new optimization level fixes the slow query, then add the user property to the business component.