Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: manual db2 access path

    Hi everyone,

    i want to learn that, is there a way to choose access path manually in db2?
    it is essential for performance.

    optimizer' choose is not always correct. especially large tables joins.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, there is no way to dictate exactly what access plan for DB2 to use. You can however influence it quite a bit based on how the query is written.

    Andy

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    You can use optimization profiles to influence the optimizer.

  4. #4
    Join Date
    Jun 2007
    Posts
    5
    thanks a lot firstly,

    hkzz,
    at my work, i'm not dba and not responsible of db system unfortunately.
    but i want to learn how can i influence the optimizer by optimization profiles.


    andy,
    if all joins are equal join (inner), always optimizer chooses the access paths. sometimes i add 1=0 expression into the join sentence for not using of that index.
    but sql sentences are very long, 20+ tables can be joined, row counts vary about 1000 and 2.5 billion (of course, i'm working in a bank).
    in that situtation, join order, access path is very very essential.
    optimizer chooses small tables first, then big tables.

    for instance: (just example, it can be trivial)

    Table A: 2.1 billion rows (partitioned)
    Table B: 1 billion rows (partitioned)
    Table C: 10 million rows
    Table D: 100.000 rows
    Table E: 10.000 rows
    Table F: 1000 rows

    relations
    A -> B (joined)
    A -> E
    C -> E (alias of E)
    B -> C
    C -> D
    B -> F

    optimizer chooses acces path that
    E -> C -> D -> B -> F -> A -> C (just imagination, maybe the example is not consistent)

    but i always prefer that
    first,
    A -> B should be,
    ex: A -> B -> C, .....

    since A and B is partitioned, and i use partition index, i take very quickly response (not for optimizer !! )

    real performance values:
    my preference: 2 minutes (explained cost value is very high)
    optimizer preference: 4.5 hours (explained cost value is very low)
    Last edited by tecessus; 06-07-07 at 09:26.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    My answer was based on DB2 for LUW. My guess is that you are using ZOS, since I never heard of optimization profiles. Please state OS and DB2 version in the future.

    Andy

  6. #6
    Join Date
    Jun 2007
    Posts
    5

    Wink

    Quote Originally Posted by ARWinner
    My answer was based on DB2 for LUW. My guess is that you are using ZOS, since I never heard of optimization profiles. Please state OS and DB2 version in the future.

    Andy

    you're right, we are using ZOS and main frame
    OS version is OS390 V1.7
    DB2 version is V7.1

    i'm upset of optimizer chooses. the same sql sentence may has different access paths because of different where condition.

    assume we have monthly partitioned table and partition indexes are integer ids.
    ex: id is period id of the table
    id = 1 refers 01.01.2007
    id = 31 refers 31.01.2007

    in where condition, if id is between 1 and 31, that means query processed for one partition

    think about 2 queries

    SELECT .....
    FROM ....... (joins)
    WHERE ...
    and id between 1 and 11


    SELECT .......
    FROM .......
    WHERE...
    and id between 1 and 12

    both of the queries goes to the same partition,
    but for this queries, access paths are different and result time is different (2 minutes, 4.5 hours !!)

    if i change the second sql sentence, i add 1=0 into the correct place (generally for small table),
    it resembles the first one.

    but i can't place the correct 1=0 for every query !!!
    Last edited by tecessus; 06-07-07 at 09:44.

  7. #7
    Join Date
    Dec 2005
    Posts
    6
    Pls refer to http://www.ibm.com/developerworks/db...e/dm-0612chen/
    , I am not sure it can support db2 v7.1. If you want, I can send the optimization profiles v8.2.2 to you.

  8. #8
    Join Date
    Jun 2007
    Posts
    5
    i'm not sure optimazation profiles help me, since i'm not responsible of db system.
    but it is useful for me personally, konowledge-based

Posting Permissions

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