DB2 selectivity

Edit online

You can set the environment variables to influence the DB2® optimizer to make better choice about how to access data in the LDAP tables.

You can use the following environment variables to control the DB2 optimizer:

LDAP_MAXCARD = YES | ONCE | NO: You can use the LDAP_MAXCARD environment variable to set the cardinality of the LDAP_DESC table. When you set this variable, a cardinality of 9E18 is assigned to the LDAP_DESC table. The cardinality value influences the data access sequence of the DB2 optimizer. DB2 resolves all attribute filters before it considers the LDAP_DESC table for query evaluation.; If the variable is set to YES, the cardinality statistic for the LDAP_DESC table is tuned to prevent expensive scans of large subtree data.The cardinality statistic is set at the server startup and periodically thereafter.; If the variable is set to ONCE, the cardinality is set during the server startup and not later when the server is running.; If the variable is set to NO or not set, the cardinality statistic is not set during the server startup.
IBMSLAPD_USE_SELECTIVITY = NO | YES: If the IBMSLAPD_USE_SELECTIVITY variable is not set to any value or is set to NO, selectivity is not used to influence DB2 access sequence.; If IBMSLAPD_USE_SELECTIVITY is set to YES and LDAP_MAXCARD is not set to YES, selectivity is used to influence the data access sequence of DB2 during the subtree search on a large subtree.; Note: If LDAP_MAXCARD and IBMSLAPD_USE_SELECTIVITY are set to YES, the directory server generates a message and does not use selectivity.

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 data access sequence to resolve the search requests. The data access sequence identifies which tables to access first during searches. Identifying the entries that are high in the tree (having many subentries) is based on DB2 statistics. If a subtree search is done by using one of these entries as the search base, the SELECTIVITY clause is added to the SQL query. When the SELECTIVITY clause is added, DB2 uses the search filter to narrow down the search results. DB2 narrows down the search results before it reads from the table that identifies the entries that are descendants of a base in a search.

To use SELECTIVITY, DB2_SELECTIVITY must be set to YES in the DB2 registry for the database instance. You must set DB2_SELECTIVITY in addition to the environment variables. You can set DB2_SELECTIVITY when you create a database instance.

Examples

Example 1:

To check the status of DB2_SELECTIVITY for a directory server instance, myinst1, run the following commands:

su – myinst1
db2 connect to myinst1
db2set –all | grep –i selectivity

Example 2:

To set DB2_SELECTIVITY for the directory server instance, myinst1, run the following commands:

su – myinst1
db2 connect to myinst1
db2set DB2_SELECTIVITY=YES

Example 3:

To set DB2_SELECTIVITY in the configuration file of the directory server instance, myinst1, run the following commands:

idsldapmodify -h host -p port -D adminDN -w adminPW
dn: cn=Front End, cn=configuration
changetype: modify
add: ibm-slapdSetEnv
ibm-slapdSetEnv: IBMSLAPD_USE_SELECTIVITY=YES