How do we use Optimizer Profile in DB2 to overwrite access plan?

Technote (FAQ)

Question

We have a query want to use table scan instead of index scan regardless how many rows in the table. But DB2 optimizer may choose different access plan after executing Runstats to the table. We heard DB2 has a feature called Optimization Profile to manually specify the access plan for queries, is there any step by step instruction how to use it?

Answer

Here is a simple instruction for how to use Optimization Profile. The testing environment is on Windows, but the steps should be platform independent. DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with level identifier "02010107". Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0". Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name "DB2COPY1".

9) Next, we are going to create several files to enable Optimization Profile.

9.1) Creating a file called a1.xml, and copy the following lines into the file. Note the statement in the XML file must be exactly same as the query going to be used by application, and the version parameter in <OPTPROFILE> tag should be matching the version of DB2 server. <?xml version="1.0" encoding="UTF-8"?> <OPTPROFILE VERSION="9.1.0.0"> <STMTPROFILE ID="Use Table Scan instead of Index Scan"> <STMTKEY SCHEMA="DB2INST1"> <![CDATA[SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000]]> </STMTKEY> <OPTGUIDELINES> <TBSCAN TABLE="DB2INST1.MYTABLE"/> </OPTGUIDELINES> </STMTPROFILE> </OPTPROFILE>

9.2) Create a file called insert.del with following line: "DB2INST1", "PROF1", "a1.xml"

9.3) Import the xml file into profile table D:\TEMP>db2 import from insert.del of del modified by lobsinfile insert into systools.opt_profile SQL3109N The utility is beginning to load data from file "insert.del".

SQL3110N The utility has completed processing. "1" rows were read from the input file.

SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W ...COMMIT of any database changes was successful.

SQL3149N "1" rows were processed from the input file. "1" rows were successfully inserted into the table. "0" rows were rejected.

Number of rows read = 1 Number of rows skipped = 0 Number of rows inserted = 1 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 1

10) Once the XML file is imported into SYSTOOLS.OPT_PROFILE table, we are going to enable a registry variables so that DB2 is able to pickup the profile: D:\TEMP>db2set DB2_OPTPROFILE=YES

Based on the above access plan, IXSCAN was replaced by TBSCAN after enabling profile. Note this approach only work for CLP applications. If you want to use Optimization Profile feature for CLI/JDBC applications, please refer DB2 Infocenter.