One of the more frequently asked question of DBAs coming from Oracle to DB2 is about how optimizer hints work in DB2. The standard answer we give is that there are no hints of that kind in DB2 and that a cost-based optimizer is used. Keep your statistics up-to-date (and maybe use automatic runstats), try out different optimization levels, and maybe set some of the documented (and undocumented) registry variables.
Well, I just mentioned undocumented registry variables. When you run "db2set -lr", you get all supported registry variables listed - about 190 in my installation of DB2 9.7. A list of many of them and some additional links is in the DB2 Information Center. When you go to the overview of performance variables, you will notice the variable DB2_EXTENDED_OPTIMIZATION. Now here comes the trick of enabling Oracle-style hints:
db2set DB2_EXTENDED_OPTIMIZATION=OHINTS
Make sure, it is really set. Calling just "db2set" should show the new value. Then try it out. For my test, I created two tables with about 100,000 rows. Then I executed my query the first without hints to measure the time and to look at the access plan.
Then I tried it with hints:
select /*+ Make this really fast, please */ a.id, a.desc, ...., b.colfoo, b.doc
from hlhints a, mytest b
where a.id=b.nr and b.colfoo LIKE '%!!argh!!%'
Just based on the measured time, I can say that it really works. Try it yourself.