Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: How to turn on Merge Join ?

    Hi All,


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

  2. #2
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    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.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    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 ?

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm just curious how you verified that there is no difference in statistics between the two environments...
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Does that include cluster ratio? Try reorging the tables on both systems.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by karthi_syb
    PROD: it uses MERGE JOIN method to join the tables.
    DEV: it uses NESTED LOOP JOIN method to join the tables.
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •