Optimizer: Fake it like you mean it

There is another useful utility that lets you fool the optimizer into thinking it has more resource than there is in the hardware. The utility is db2fopt.

This is useful if you are testing the optimizer in a test environment that is considerably less powerful than the production environment. eg, the sortheap in the production system might have 80000 pages, but your tiny test machine might max out at 2000. What do you do then? You can use db2fopt to fake it:

db2fopt <database> update opt_sortheap 80000

Easy.

It is a bit unfortunate that only these 4 are available to be 'faked' as of this writing ( for DB2 LUW v10)

• opt_buffpage
• opt_sortheap
• opt_locklist
• opt_maxlocks

Replicating a database's 'statistics'

db2look has a useful option '-m' that allows you to 'replicate' a database's statistics, which is not unlike Oracle's DBMS_STATS.EXPORT_XXX_STATS. One advantage it has over the export is that it gives you an insight into the stats components required, in the form of individual UPDATE statements. The other advantage is that you don't have to prepare a table to be 'exported' or 'imported' into.

eg:

db2look -d sample -t t1 -m -o test.sql

This will 'look' into the database 'sample', pull stats only for table 't1', and redirects the output to file 'test.sql'.

One word:  Convenient!