Results 1 to 2 of 2

Thread: db2 optimizer

  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: db2 optimizer

    Hi All,

    Is DB2 optimizer cose based or rule based by default? How to check it and how to change the default.

    Thanks in advance,
    jfk

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume you mean “cost” based? DB2 uses a cost based optimizer; however, any database must sometimes make assumptions (using rules) about what the cost will be, especially when the query optimization is not set to the maximum of 9 (see below).

    On DB2 for Linux, Unix, and Windows there is a parameter which can be set to determine how much time (and resources) DB2 uses to obtain the best access plan. This default amount of query optimization is set by the dft_queryopt.

    The value of dft_queryopt is used unless the SET CURRENT QUERY OPTIMIZATION statement or the QUERYOPT option on the bind command is used.

    The query optimization classes currently defined are:

    0 - minimal query optimization.
    1 - roughly comparable to DB2 Version 1.
    2 - slight optimization.
    3 - moderate query optimization.
    5 - significant query optimization with heuristics to limit the effort
    expended on selecting an access plan. This is the default.
    7 - significant query optimization.
    9 - maximal query optimization

Posting Permissions

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