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