In DB2, how can I disable NLJOIN?

Question

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query.

I've been looking for this for a while without any success. I looked for a property that could be set through the db2set command, but the only one referring NLJ is DB2_INLIST_TO_NLJN.

The closest I've found is through the usage of optimization profiles but those are very specific and what I need is a general setting that would apply to any statement being optimized.

Answer 1

Him

You can change the optimization class, in order to have the possibility to chose a merge scan or hash join instead of nested loop. However, the optimizer will decide which method is the best.

The second option is to use a profile for the optimizer: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0024530.html

Answer 2

You might wish to check out this article on join methods and how DB2 chooses which one when.

Based on this documentation (which is for DB2 8 and I'm assuming it is similar for 9+, though not necessarily), nested loop joins are always available to the optimizer to use. DB2 can select merge sort and hash as the optimizer level is upped from 1 and other things are present (read the article to see which).