DB2 query optimization for one level searches

To retrieve one level search results faster, you must optimize the SQL queries that access the LDAP_ENTRY table and other tables.

For one level searches, entry IDs are resolved by using the PEID column of the LDAP_ENTRY table. The SQL query that contains the LDAP_ENTRY table is evaluated by using a parameterized value of PEID. If a directory server hierarchy is deep nested or large, the one level search might take considerable time when the parameterized value of PEID is used. You must run the idsrunstats command against the directory server instance to optimize the database and update DB2 statistics.

When you run a one level search against a directory server, the following process occurs:

• In IBM® Security Directory Server, version 6.3.1 and later, the parameterized value for PEID is replaced with the evaluated PEID literal value for further processing of the SQL queries.
• The DB2 optimizer uses the PEID literal value to determine the subset of data in the large table. The DB2 optimizer uses the collected distribution statistics to decide whether to resolve the attribute indexes or the LDAP_DESC index first in its data access plan.
• Depending on the type of subtree and filter, the DB2 optimizer creates an optimal data access plan for subtree searches against both large and small subtrees.