Setting an optimization profile within an application

You can control the setting of the current optimization profile for dynamic statements in an application by using the SET CURRENT OPTIMIZATION PROFILE statement.

About this task

The optimization profile name that you provide in the statement must be a schema-qualified name. If you do not provide a schema name, the value of the CURRENT SCHEMA special register is used as the implicit schema qualifier.

The optimization profile that you specify applies to all subsequent dynamic statements until another SET CURRENT OPTIMIZATION PROFILE statement is encountered. Static statements are not affected, because they are preprocessed and packaged before this setting is evaluated.

Procedure

To set an optimization profile within an application:

Use the SET CURRENT OPTIMIZATION PROFILE statement anywhere within your application. For example, the last statement in the following sequence is optimized according to the JON.SALES optimization profile.

   EXEC SQL  SET CURRENT OPTIMIZATION PROFILE = 'NEWTON.INVENTDB';

   /* The following statements are both optimized with 'NEWTON.INVENTDB' */
   EXEC SQL PREPARE stmt FROM SELECT ... ;
   EXEC SQL EXECUTE stmt;

   EXEC SQL EXECUTE IMMEDIATE SELECT ... ;

   EXEC SQL SET CURRENT OPTIMIZATION PROFILE = 'JON.SALES';

   /* This statement is optimized with 'JON.SALES' */
   EXEC SQL EXECUTE IMMEDIATE SELECT ... ;

If you want the optimizer to use the default optimization profile that was in effect when the application started running, specify the null value. For example:
```
   EXEC SQL SET CURRENT OPTIMIZATION PROFILE = NULL;
```
If you don't want the optimizer to use optimization profiles, specify the empty string. For example:
```
   EXEC SQL SET CURRENT OPTIMIZATION PROFILE = '';
```
If you are using a call level interface (CLI) application, you can add the CURRENTOPTIMIZATIONPROFILE parameter to the db2cli.ini file, using the UPDATE CLI CONFIGURATION command. For example:
```
   update cli cfg for section sanfran using currentoptimizationprofile jon.sales
```
This results in the following entry in the db2cli.ini file:
```
   [SANFRAN]
   CURRENTOPTIMIZATIONPROFILE=JON.SALES
```
Note: Any SET CURRENT OPTIMIZATION PROFILE statements in the application override this setting.