DB2 SELECTIVITY

There are two alternative ways that Tivoli® Directory Server influences the DB2® optimizer to make better choice about how to access data in the LDAP tables. These are controlled by environment variables, as follows:

LDAP_MAXCARD = YES | ONCE | NO

• If set to YES or by default, if neither environment variable is set, DB2's cardinality statistic for the LDAP_DESC table is artificially adjusted to prevent expensive scans of large subtree data. This setting of the cardinality is done at server startup and periodically thereafter.
• If set to ONCE, then the cardinality is set once during each server startup but not subsequently while the server is running.
• If set to NO, then the cardinality statistic is not changed.

IBMSLAPD_USE_SELECTIVITY = NO | YES

• If not set or set to NO, then selectivity is not used to influence DB2's access plans.
• If set to YES and LDAP_MAXCARD is not set to YES, then selectivity is used to influence DB2's decisions about accessing data during large subtree searches. The use of selectivity is explained further in the following paragraphs.

You can improve the performance of subtree searches on search bases that are high in a directory tree by using SELECTIVITY in Structured Query Language (SQL). The inclusion of SELECTIVITY in SQL enables the DB2 optimizer in the formation of access plans to resolve the search request (identifying which tables to access first during searches). Determination of the entries that are high in the tree (having a large number of children) is based on DB2 statistics. If a subtree search is done using one of these entries as the search base, the SELECTIVITY clause will be added to the SQL query. This causes DB2 to use the search filter to narrow down the search results before reading the table that identifies the entries that are descendants of a base in a search.

To use SELECTIVITY, the DB2 registry for the database instance must have DB2_SELECTIVITY set to YES. This is in addition to the environment variables described above. This is done when creating a database instance or while migrating from previous versions.

[ Top of Page | Previous Page | Next Page ]