Troubleshooting
Problem
This technote reiterates that regular DB2 maintenance should be performed on Directory Server's backend DB2 database, and provides some instructions for doing this maintenance.
Resolving The Problem
The following tasks should be checked/completed on a regular basis to avoid performance problems in DB2 (some of the more complicated tasks may require a local DBA to be involved):
- Database optimization/runstats
- Check for unindexed attributes that are used in search filters
- Check if some DB2 tables need to be reorganized
Details:
Database optimization via idsrunstats (or other method):
On any server that receives regular LDAP update traffic, the Directory Server version of DB2 runstats needs to be run to keep DB2's system catalog statistics up-to-date (so the DB2 optimizer makes good choices when executing SQL queries).
There's a simple and a complicated method for doing runstats, depending on your requirements and experience with DB2.
(Important note: ITIM/ISIM and ITAM/ISAM have their own specific requirements for how to run a suitable db2 runstats and provide scripts to automate this. See each product's performance tuning scripts and documentation for more details. Their methods should replace the methods below. The latest performance tuning scripts provided by this ISIM tech note covers all requirements of SDS behind ISIM, ISAM or any other application.)
Runstats: Simple Method
The simplest way is to run the command provided by Directory Server called 'idsrunstats'. This command requires no arguments and runs 'db2 runstats ...' with the Directory Server recommended options, so just run:
idsrunstats
(Or, if you have more than one instance: 'idsrunstats -I <instance>'.)
or the "Optimize database" task in idsxcfg.
In versions 6.1 and earlier, running this task requires that ibmslapd be stopped. In version 6.2 and later, this can be run while the server is running, but some SQL statements may be cached by the server and won't benefit from the new statistics until a restart of ibmslapd is done or the cached statements are released.
Despite that, it is always better to run an idsruntats without a restart than to not run an idsrunstats at all. Regular idsrunstats without restarting ibmslapd will still provide the benefits even if not immediately.
A detailed description of the ldap-specific optimization and of DB2 tuning for ITDS can be found in the ITDS Performance Tuning Guide.
Runstats: Complicated Method
(ITIM/ISIM and ITAM/ISAM have their own specific requirements for how to run a suitable db2 runstats and provide scripts to automate this. See each product's performance tuning scripts and documentation for more details. Their methods should replace the methods below.)
There are more complicated ways to do this maintenance, that can provide additional benefits. First, doing a DB2 reorgchk/runstats manually allows you to avoid restarting the server. In addition, if the server receives a lot of modifications on a regular basis, it is possible that the DB2 tables that store ITDS data can become disorganized and perform very poorly. Running a 'DB2 reorgchk ...' in this case, is a good way to check to see if any DB2 tables need to be reorganized. However, running a 'DB2 reorgchk ...' automatically does a 'DB2 runstats ...' and, as a result, the ldap-specific optimization is lost. Here's how to do a DB2 reorgchk, save the output (for checking later) and restore the ldap-specific cardinality optimization:
db2 "connect to <ldapdbname>"
db2 "reorgchk update statistics on table all" > reorgchk.txt
(The following db2 update on sysstat.tables should only be run if DB2 selectivity has not been enabled in ibmslapd; skip this sql command if it has been enabled.)
db2 "update sysstat.tables set card=9E18 where tabname='LDAP_DESC' and card<>9E18"
db2 flush package cache dynamic
db2 terminate
Running the "db2 flush package cache" at the end allows one to not to have restart the server like one must do when using the idsrunstats/runstats tool. However, it is possible that some SQL statements cached by ITDS will not benefit from the new statistics immediately. The easiest way to guarantee all the benefits immediately is to restart ibmslapd.
NOTE: doing a runstats while ibmslapd is connected to DB2 can cause inconsistent statistics to be generated (which can negatively affect performance) because updates are occurring on a table while statistics are being generated. DB2 Support has a technote: SQL2314W when using runstats with inconsistent statistics that discusses this. In this case, the alternatives are to either try again when it's less busy, or use the simple method above.
The reorgchk.txt file will be available later for checking to see if any tables/indexes need to be reorg'd. Reading the output from db2 reorgchk generally requires a DB2 DBA. Instructions are provided in the Performance Tuning Guide above, although they generally err on the side of caution; using these instructions would have you run 'db2 reorg ...' in cases when it probably really wasn't necessary. (Although it never hurts to reorg a table or an index.)
How often should DB2 statistics be regenerated? It depends on the amount of activity and modifies to entries that occur on the server. For environments that run batch updates or synchronization tools, that synchronize data with other directories very frequently, this should probably be done at least once a week. In some cases, there are very busy environments that can benefit from running it even more frequently. A reasonable recommendation for most environments, though, is to regenerate statistics once a week.
To see when the last time statistics were generated for a specific table, run the following SQL as your DB2 instance owner user:
db2 connect to <ldap_db>
db2 -v "select substr(tabname,1,30),card,stats_time from syscat.tables"
db2 connect reset
Then review the last time runstats was run for the different tables (this output also includes the cardinality of the tables which can be helpful to know). If the timestamps are all very old (or there are none), you need to run an idsrunstats.
Check for unindexed attributes that are being used in search filters
Any attributes that are used in search filters and are not indexed can adversely affect performance.
See the following technote for instructions for how to check for unindexed attributes used in search filters and also how to index them:
GLPRDB091W Messages in ibmslapd.log indicate attribute needs to be indexed
Reorganizing DB2 tables
Of the three activities, this one is most likely to be optional (and the one mostly likely to required DBA involvement). However, if a large number of updates come into the server on a regular basis, it is possible for the DB2 tables and indexes to become disorganized, which can significantly affect performance. See the "Complicated method" of running a db2 runstats above to see an example for how to check if the tables or indexes are disorganized.
See the following documention in our Knowledge Center for more details how to check for disorganization and for how to reorg the tables and indexes:
Database organization (reorgchk and reorg)
Related Information
[{"Product":{"code":"SSVJJU","label":"IBM Security Directory Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"General","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"6.0;6.1;6.2;6.3;6.3.1;6.4","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Product Synonym
ITDS;Tivoli Directory Server;ldap;DB2;IBM Tivoli Directory Server;TDS
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21263999