Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Why on earth would you want to prevent nested loop joins? Especially in a general sense (it sounds like you want to disable it for every query, not just one specific query) ? – Ian Bjorhovde Jan 26 '12 at 23:07

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).

share|improve this answer


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:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.