Results 1 to 4 of 4

Thread: db2 selectivity

  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Unanswered: db2 selectivity

    what is the right value for setting the registry variable db2_selectivity?

    db2_selectivity=YES
    or
    db2_selectivity=ALL

    db2 manuals/ibm dw indicate db2_selectivity=YES

    Thx!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    From 9.7 manuals
    DB2_SELECTIVITY
    Operating system: All
    Default=NO, Values: YES or NO
    This registry variable controls where the SELECTIVITY clause can be used in search conditions in SQL statements.
    When this registry variable is set to YES, the SELECTIVITY clause can be specified for the following predicates:
    A basic predicate in which at least one expression contains host variables
    A LIKE predicate in which the MATCH expression, predicate expression, or escape expression contains host variables
    As per this info, there is no ALL in the values list. where did you get your info?

    Also, the default is NO. Use only as a last resort.

    HTH
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2007
    Posts
    21
    we are on db2 9.5

    selectivity works when db2_selectivity=ALL instead of db2_selectivity=YES

    some reference link's which indicates db2_selectivity=ALL alone enables selectivity

    very very slow query with parameters - DB2 Database

    Browsing whole table? - DB2 Database

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can trust Serge Rielau.

    I just tested - both ALL and YES work. There may be some subtle differences though of which I'm not aware.

Posting Permissions

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