In IBM DB2, which prerequisite must be satisfied for the optimizer to consider using a hash join strategy when joining tables?

Introduction / Context:
Hash join is one of the physical join strategies that the DB2 optimizer can choose when it builds an access plan for a SQL query. Understanding when the optimizer is even allowed to consider a hash join is important for performance tuning, database design, and certification style questions about DB2 internals. This question focuses on the specific logical condition that must be present in the query predicates before a hash join becomes a valid candidate in the optimizer search space.

Given Data / Assumptions:
- We are working with the DB2 relational database and its cost based optimizer.
- The topic is the hash join strategy between two or more tables.
- We assume standard DB2 configuration with typical registry settings and memory parameters.
- We focus on the logical form of the join predicate rather than low level tuning options.

Concept / Approach:
A hash join works by building a hash table on the join key values from one input and then probing that hash table with the rows from the other input. Because of this, the join condition must be based on equality comparisons between join keys. These equality comparisons are called equijoin predicates. Without equality based conditions, a hash table cannot be used in a straightforward way to match rows. Therefore, the DB2 optimizer will only consider a hash join when the join predicates are equijoins. Other parameters, such as optimization level or memory, may influence the cost or feasibility, but they do not replace the logical requirement of equijoin predicates.

Step-by-Step Solution:
Step 1: Recall what a hash join does. It builds an in memory hash table of join key values from one table and probes it with values from the other table. Step 2: Note that hashing is naturally defined for equality comparisons, because rows are matched by identical hash key values. Step 3: Recognize that in DB2, the optimizer only considers hash join when join predicates are equijoins such as T1.col = T2.col, possibly with multiple columns. Step 4: Compare this to non equijoins, for example T1.col > T2.col or T1.col <> T2.col, which do not lend themselves to simple hash based matching. Step 5: Evaluate the options and select the one that explicitly requires equijoin predicates on the join columns as the prerequisite condition.

Verification / Alternative check:
A quick way to verify the reasoning is to think about how other join methods work. Nested loop join can handle many types of predicates because it simply evaluates the predicate row by row. Merge join requires sorted inputs and also works best with equality or range predicates. Hash join is tightly tied to equality based hashing of keys. DB2 documentation and performance guides repeatedly state that hash join is only available for equijoins. This confirms that equijoin predicates are the critical prerequisite, while parameters like SORTHEAP or registry flags are tuning details rather than logical conditions for the join method to be considered.

Why Other Options Are Wrong:
Optimization level 1 or above may influence the richness of optimization but is not the specific prerequisite for hash join. The registry variable related to anti join affects a different optimization feature, not the fundamental hash join requirement. SORTHEAP size affects whether a chosen hash join performs efficiently in memory, but the optimizer can still consider hash join even if SORTHEAP is small, although it may then spill to disk or choose another method. None of these alternative options express the core requirement of equijoin predicates.

Common Pitfalls:
A common mistake is to confuse memory configuration or registry tuning with the logical conditions required by an algorithm. Another pitfall is to assume that because a system parameter is important for performance, it must be the prerequisite for the method to be considered at all. Students may also mix up conditions for hash join with conditions for merge join or nested loop join, or incorrectly think that any join predicate can use hash join. Remember that hash join is strongly tied to equality based joins on the join keys.

Final Answer:
The join must be expressed using equijoin predicates on the join columns.