Optimization profiles in DB2 UDB

How many times have we spend hours trying to figure why DB2 is not choosing a particular index and access path ? Probably many !    DB2 is one of the best cost based optimizer out there , but yet it some rare cases DB2 refuses to choose the optimal path. As a DBA we go through utilities like db2expln, runstats and  db2advis to tune SQLs and find the best access path, but if none of them work you can resort to optimization profiles to force DB2 to use a particular index. Optimization profiles are similar to the “hints” in other RDBMS engines like Oracle and SQL Server.

Optimization profiles in DB2 UDB is an XML document and is then imported into the SYSTOOLS.OPT_PROFILE table. You activate a profile by executing SET CURRENT OPTIMIZATION PROFILE command  when binding a package or executing a SQL.

SYSTOOLS.OPT_PROFILE  can be created either  by running the sysinstallobjects procedure (recommended) or by creating the table directly :

db2 “call sysinstallobjects(‘opt_profiles’, ‘c’, ‘toolstbsp’, ”)”
or
create table systools.opt_profile (
schema  varchar(128) not null,
name    varchar(128) not null,
profile blob (2m)    not null,
primary key (schema, name)
) in toolstbsp;

Lets take a simple example where DB2 chose a particular access path, but you as a DBA know that a different access path is better , how can you force DB2 optimizer to chose a particular index :

First ensure you have the SYSTOOLS.OPT_PROFILE table created, if not create the table using the method I wrote above.  Lets explain the following sql that can be run against the SAMPLE database:

SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
FROM EMPLOYEE WHERE JOB IN (‘CLERK’, ‘SALESREP’) AND WORKDEPT=’A00’ ORDER BY JOB, SALARY;

Below is the explain :

For instance, if you want to force optimizer to choose another index – XEMP2,  to do so create the following optimization profile in a file , lets say profile1.xml :

?xml version=”1.0″ encoding=”UTF-8″?>

<OPTPROFILE VERSION=”9.5.1″>
<STMTPROFILE ID=”Example 1″>

<STMTKEY>
<![CDATA[SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
FROM EMPLOYEE
WHERE JOB IN (‘CLERK’, ‘SALESREP’) AND WORKDEPT=’A00′
ORDER BY JOB, SALARY]]>
</STMTKEY>

<OPTGUIDELINES>
<INLIST2JOIN OPTION=”DISABLE” TABLE=”EMPLOYEE” COLUMN=”JOB”/>
<IXOR TABLE=”EMPLOYEE” INDEX=”XEMP2″/>
</OPTGUIDELINES>

</STMTPROFILE>
</OPTPROFILE>

Now create a file called profile1.del with the following lines :
“DB2INST2″,”PROF1″,”profile1.xml”

Now IMPORT the profile1.del file to systools.opt_profile table:

IMPORT FROM profile1.del OF DEL    MODIFIED BY LOBSINFILE    INSERT INTO SYSTOOLS.OPT_PROFILE

Its time to see how the optimizer reacts to the profile . Execute the following sqls :

SET CURRENT OPTIMIZATION PROFILE=”PROF1″
SET CURRENT EXPLAIN MODE EXPLAIN
SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARYFROM EMPLOYEEWHERE JOB IN (‘CLERK’, ‘SALESREP’) ANDWORKDEPT=’A00’ORDER BY JOB, SALARY
SET CURRENT EXPLAIN MODE NO
db2exfmt -d SAMPLE -1 -g -o exfmt_query1.out

The file exfmt_query1.out should contain a query access plan similar to the following : 

Note the index that the optimizer chose is XEMP2.  You can add other profiles to the opt_profile table for different set of workload or add more STMTKEY to the same xml document.   You can find more about optimization profile here . Using optimization profile should be your last resort, like I said earlier db2 optimizer is extremely wise and usually choose the optimal access path.

Try out the optimization profile and let me know what your experience.