DB2 statistics

When you run update operations against a directory server, the DB2® statistics of the directory server database is affected. You must update the DB2 statistics of the directory server database after large updates to the directory server.

When you update the DB2 statistics of a database, you must stop the directory server to prevent any failures. You can update the DB2 statistics, while the directory server is running. However, application that is accessing the directory server might timeout.

The DB2 optimizer uses DB2 system statistics to optimize DB2 queries. When LDAP entries are added to a directory server, the DB2 statistics on the affected tables can become out of date. The out-of-date system statistics can result in wrong choices by the DB2 optimizer, and can result in poor performance.

Not updating DB2 system statistics is one of the most common performance tuning mistakes that are made with DB2 databases. The first step in updating the DB2 system statistics is to run the DB2 runstats command on every table or run the DB2 reorgchk command. Later versions of DB2 provide method to tune automatic checks for out-of-date system statistics and update the out-of-date system statistic by using the runstats command.

Do not use the automatic runstats option with a directory server. Updating the DB2 system statistics for a directory server also involves overriding of the default DB2 system statistics by the runstats command.

You can also manually update the DB2 statistics by running the appropriate DB2 command.

Update system statistics with DB2 runstats or reorgchk

You can update the DB2 system statistics by running a DB2 runstats command on all tables in the database or by running the DB2 reorgchk command. You can obtain the list of tables in a database by running the following command:

db2 connect to ldapdb2
db2 list tables for all

It is only necessary to perform the DB2 runstats command on the tables having the DB2 instance schema. For example, in this document, the DB2 instance schema is ldapdb2. The DB2 runstats command is run as follows:

For DB2, Version 12.1.5:

db2 runstats on table tablename with distribution and detailed indexes all

Where, tablename is the name of the table on which to run the runstats command.

If runstats must be done while directory server is running, it is recommended to include shrlevel change and allow write access options. This allows database to become accessible to the directory server instance.

Note: Running the runstats command can temporarily impact the performance and lead to timeouts in directory server operations.

Running the runstats command on all tables can be tedious if not automated with a script. The alternative is the DB2 reorgchk command. To update DB2 statistics, run the DB2 reorgchk command. For example:

db2 connect to ldapdb2
db2 reorgchk update statistics on table all

The reogchk command does some additional checking and reports on the organization of the data in the database. It is useful when an application requires to read the database sequentially. When you access a directory server for common use, the database is accessed randomly.

The main advantage of the DB2 runstats command is the ability to select which tables to tune. When you run the DB2 runstats command, you must selectively limit it to the tables that are less than 100,000 rows. In most cases, tables with 100,000 rows no longer require tuning. By limiting the runstats command for relatively small tables, the frequency to run runstats decreases. For example, if a million users are added to a directory server that already contain a million users, it is likely that the affected tables already have a million rows and making it unnecessary to do runstats on that table.

If the second million users introduce a new LDAP attribute that the first million did not have, it is necessary to run runstats on the tables for the new attributes. But it is not required for those tables that already have a million rows. In both these cases, the time to update the system statistics by using the runstats command is less than it would take to run runstats on all tables of the directory database.

Updating system statistics with idsrunstats

You can run the idrunstats tool while the directory server is running. The idsrunstats command collects the distribution statistics on all the columns and indexes of LDAP_DESC and LDAP_ENTRY tables.

To know more about idsrunstats, see Optimization and organization (idsrunstats, reorgchk and reorg).

Improving disk utilization with DB2 row compression

If you installed a fully licensed version of DB2 Server, including DB2 Storage Optimization Feature license, you can use row-level compression.

You can improve the disk utilization and the overall performance with the DB2 row compression capability. You can select the rows that you want to compress by using the idsdbmaint tool that is provided with IBM® Verify Directory. For more information, see DB2 row compression.

Override system statistics

You can set the LDAP_MAXCARD environment variable to control system statistic overrides by a directory server. For more information about setting LDAP_MAXCARD and its behavior, see the https://www.ibm.com/support/pages/node/357777 website.

LDAP_DESC

When you set the variable, it sets the cardinality column of the LDAP_DESC table in the system statistic tables to a value of 9E18, which is the scientific notation for a large number. A directory server can also force this override.

To tune manually, run the following command:

db2 "update sysstat.tables set card = 9E18 where tabname = 'LDAP_DESC'"

When you override, it chooses the LDAP_DESC table last during an LDAP search. The LDAP_DESC table is used on subtree searches when the LDAP_DESC(AEID,DEID) index is defined.

If the LDAP_DESC(AEID,DEID) index is not defined, this override has no effect. This override allows small subtree searches to be fast if they are specified with an objectclass=* filter. The main purpose of this override is to prevent use of the LDAP_DESC(AEID,DEID) index with large subtrees. It ensures that the attributes on the filter are used first with an LDAP search, if attributes are specified.

LDAP_ENTRY

For one level searches, entry IDs are resolved using the PEID column of the LDAP_ENTRY table.

When you set the variable, it sets the cardinality column of the LDAP_ENTRY table in the system statistic tables to a value of 9E18, which is scientific notation for a large number. A directory server cannot force this override.

To tune manually, run the following command:

db2 "update sysstat.tables set card = 9E18 where tabname = 'LDAP_ENTRY'"

In an LDAP search, the LDAP_ENTRY table PEID index is queried last because of the override. The PEID index is used when you run a one level search. It ensures that the attributes on the filter are used first with an LDAP search, if attributes are specified.

CN

When you set the variable, it sets the cardinality column of the CN table in the system statistic tables to a value of 9E10. A directory server cannot force this override.

To tune manually, run the following command:

db2 "update sysstat.tables set card = 9E10 where tabname = 'CN'"

The override chooses the CN table before the subtree criteria (LDAP_DESC table) and the one level criteria (LDAP_ENTRY table).

This override also uses the OBJECTCLASS filter before the CN attribute with an LDAP search.

REPLCHANGE

When you set the variable, it sets the cardinality column of the REPLCHANGE table in the system statistic tables is to a value of 9E18, which is scientific notation for a large number. It also sets the colcard and high2key columns of the REPLCHANGE table with a colname of ID in the system statistic columns table to 9E18 and 2147483646. A directory server can also force this override.

To tune manually, run the following commands:

db2 "update sysstat.tables set card = 9E18 where tabname = 'REPLCHANGE'"
db2 "update sysstat.columns set colcard=9E18, high2key='2147483646'
where colname = 'ID' and tabname = 'REPLCHANGE'"

DB2 can use the index that is defined on the REPLCHANGE table when the override is set. The DB2 optimizer does not use indexes on an empty table.

db2look command

The db2look command is useful for reporting all the system statistic settings of the database. Use the mimic option, -m, to generate a report that contains the DB2 command which produces the current system statistic settings. For example:

db2look -m -d ldapdb2 -u ldapdb2 -o output_file

Where, output_file is the file location for storing the results.

Before you run the db2look command, switch the user context to the database instance owner.