db2 selectivity

usc · #1 (**permalink**) 08-23-11, 19:22

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!

sathyaram_s · #2 (**permalink**) 08-23-11, 19:40

From 9.7 manuals

Quote:

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

usc · #3 (**permalink**) 08-24-11, 15:00

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

n_i · #4 (**permalink**) 08-24-11, 15:39

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech