Creating statement-level optimization guidelines

With support for statement-level server options, you can set server options to affect a single SQL statement by using optimization profiles.

About this task

An optimization profile is an XML document in which you can specify optimization parameters for one or more SQL statements. You define the contents of the optimization profile in an optimization profile schema. For detailed information about optimization profiles and guidelines, see Optimization profiles and guidelines.

You can specify any federation server option in an optimization profile. The guidelines defined in the optimization profile influence the Db2® optimizer and can improve the performance and efficiency of federation query processing.

You can set statement-level server options in an optimization profile for a single SQL statmenet.

Restriction: Statement-level server options are limited to the DB2_MAXIMAL_PUSHDOWN and the COLLATING_SEQUENCE server options.

Procedure

Create an entry in the SYSTOOLS.OPT_PROFILE table to define an optimization profile.

The SYSTOOLS.OPT_PROFILE table contains all optimization profiles.

Example

Create an entry in SYSTOOLS.OPT_PROFILE.

drop table SYSTOOLS.OPT_PROFILE;
call sysinstallobjects('opt_profiles', 'c', '', '');

Edit the optimization profile and create a statement profile. Specify the server options after the statement key in the optimization guidelines as shown in bold:

<xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="10.0.1">
<STMTPROFILE ID="QRY1">
<STMTKEY>
<![CDATA[select * from NICK1 as n1, NICK2 as n2 
  where n1.c2 = n2.c1 and n1.c2 > 'a']]>
</STMTKEY>
<OPTGUIDELINES>
<SERVEROPTIONS>
 <SERVER NAME="DATASTORE2">
  <OPTION NAME="DB2_MAXIMAL_PUSHDOWN" VALUE="Y">
  <OPTION NAME="COLLATING_SEQUENCE" VALUE="Y"><
  </SERVER>
  <SERVER NAME="DATASTORE1">
  <OPTION NAME="DB2_MAXIMAL_PUSHDOWN" VALUE="N"/>
 </SERVER>
</SERVEROPTIONS>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

An optimization profile can also contain global guidelines that apply to all SQL statements that are run while the profile is in effect.