You can control the setting of the current optimization profile for dynamic statements in an application by using the SET CURRENT OPTIMIZATION PROFILE statement.
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.
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 ... ;
EXEC SQL SET CURRENT OPTIMIZATION PROFILE = NULL;
EXEC SQL SET CURRENT OPTIMIZATION PROFILE = '';
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