Hi All,


How to turn on merge join option in DB2 ? I wanted to know server level and session level option.

I have gone thorugh the below URL.

DB2 Universal Database


But i couldn't find any option to enable merge join.

Note: There is an option to enable hash join.

Merge joins (operator MSJOIN) are enabled by default and you can't activate or deactivate them.

Hash joins were introduced in DB2 V7 or V8 (I don't remember) as a new feature so there was an option to explicitly turn them on/off in cause they are desired or cause problems.

Actually my problem is, i have executed a query in PROD & DEV environment.
There is no difference in the query. but it showed different query plan while executing it in PROD & DEV environment.

PROD:
it uses MERGE JOIN method to join the tables.
DEV:
it uses NESTED LOOP JOIN method to join the tables.

how ?

As you told, if it is enabled by default, how to see that ? my question was is there any variable available to know the status of MERGE JOIN ?

I said that you can't activate or deactive merge joins. MSJOINs are always activated.

Regarding the differences: do you have the same amount of data in both systems? Do you have the same statistics? Do you use the same hardware (CPU, disk, data distribution)? All this can have an influence on the access plan generated by the optimizer. So I would start with synchronizing the statistics (i.e. copying the stats from the production server to the development system) and then go from there...

I have checked the below in both environment.

1) Statistics
2) Volume of data
3) CPU & Hard Disk speed

There is no difference between them.

I'm just curious how you verified that there is no difference in statistics between the two environments...

Does that include cluster ratio? Try reorging the tables on both systems.

Two typical reasons for the optimizer to prefer nested loop joins over merge joins:
- the outer table is relatively small
- the cluster ratio of the inner's table join "key" is high
(and the other way around, of course)

So I would indeed expect, as others already stated, that either the outer table's cardinality statistics on your DEV system are a lot smaller than on PROD, or the cluster ratios on at least the inner table differ between PROD and DEV.