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 :

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.

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...

Disclaimer

The posts here are solely my personal views and opinion. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis or consultation with technical support. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. I do not represent any vendor or product.