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 and the volatile table definitions 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.

After you update the DB2 system statistics, you must define all tables as volatile. 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 11.5.7.0:

db2 runstats on table tablename with distribution and detailed indexes all 
shrlevel change

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

If you provide the shrlevel change and allow write access options, the database becomes accessible to the directory server instance. When the runstats commands are running, it can result in poor performance and 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 that is provided with IBM® Security Directory Server, version 6.2 and later, while the directory server is running. From IBM Security Directory Server, version 6.3.1, the idsrunstats command collects the distribution statics 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 Enterprise Server Edition, in addition to a 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 Security Directory Server. For more information, see DB2 row compression.

Defining DB2 tables as volatile

DB2, Version 11.5.7.0 supports the option to define tables as volatile. Defining the tables as volatile, enables the DB2 optimizer to use the indexes that are defined on the table. If you tune the DB2 optimizer, the DB2 optimizer makes the correct optimization choice when the statistics are out of date. For example, when a table suddenly grows in size before the system statistics is updated. Run the following command to define a table as volatile:

db2 connect to ldapdb2 db2 alter table tablename volatile

Where, tablename is the name of the table to be defined as volatile.

Override system statistics

In some cases, the DB2 optimizer makes a wrong choice in optimizing a query even if the system statistics are up to date. In such cases, it is necessary to override the DB2 system statistics to influence the DB2 optimizer to make the correct choice. A directory server can override some of the system statistic. For example, when the directory server is started as part of the idsrunstats command.

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 http://www-01.ibm.com/support/docview.wss?uid=swg21316267 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. For DB2, Version 11.5.7.0, if you define the table as volatile then the override is redundant.

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.