TDBM performance considerations

The z/OS® LDAP server TDBM backend uses IBM® Database 2 (Db2®), a powerful and scalable database product, for its data storage facility. In the most optimal LDAP environments, directory data is fairly static and the access for TDBM cached data is repetitive. In other environments, where directory data is updated frequently and the access for non-cached data is random, the power and scale of Db2 is used to enhance performance.

The following is included in this section:
  • Db2 tuning to improve database access
  • TDBM tuning that affects Db2 usage

Db2 tuning is important to ensure that TDBM requests that access the database in Db2 operate efficiently, and that response times do not increase as the database grows in size. Many general Db2 tuning guidelines are applicable to TDBM databases.

Also, there are choices in the initial setup of the TDBM database and in the TDBM backend section of the LDAP server configuration file that influence performance within Db2.

TDBM caches provide a significant benefit to performance, allowing the server to bypass read operations to the database. Optimizing the cache size is important to ensure a high percentage hit rate, without requiring excessive storage. See LDAP server cache tuning for more information about TDBM caches.

Db2 tuning

The following tasks relating to Db2 tuning are crucial to maintaining good performance. These tasks are typically performed by database administrators on most production Db2 data:
  • Periodically reorganizing the TDBM database by using the Db2 REORG utility
  • Periodically maintaining the database statistics by using the Db2 RUNSTATS utility
  • Allocating Db2 buffer pools large enough to minimize I/O to the TDBM database

The TDBM table spaces and indexes should be reorganized periodically by using the Db2 REORG utility. This helps to improve database access performance and to reclaim fragmented space.

DB2 Managing Performance contains the Db2 real-time statistics stored procedure (DSNACCOX) that is a sample stored procedure that makes recommendations to help you maintain your Db2 databases. DSNACCOX uses data from catalog tables, including real-time statistics tables, to make its recommendations.

The need to reorganize the data is based on the amount of update activity that occurs. If a date/time based REORG strategy is chosen rather than using the real-time statistics capability of Db2, then weekly REORGs can be scheduled, or some other regularly scheduled maintenance interval can be chosen that best fits the customer environment and typical volume of updates. REORGs should be scheduled frequently enough to maintain good organization of the data as determined by the recommendations. More frequent REORGs can be beneficial if large percentage changes in inserts/updates/deletes occur. For example, when preparing for new workloads and populating the database with large amounts of data, or when introducing new types of LDAP data with new entry attributes and object classes, it can be beneficial to schedule a REORG after the new data is populated.

You should collect and store statistics with the REORG utility. Either run the RUNSTATS utility immediately after the REORG utility, or include the STATISTICS option directly on the REORG TABLESPACE and REORG INDEX utility control statements when reorganizing the data. Collecting and storing current, accurate statistics in the Db2 catalog for the TDBM database, table spaces, tables, indexes, and partitions allow Db2 to select efficient access paths to the TDBM database. The parameters that you want to specify when using the RUNSTATS utility are shown as follows:
 //SYSIN  DD *
      RUNSTATS TABLESPACE GLDDB.SEARCHTS REPORT YES
        TABLE (LDAPTB1.DIR_SEARCH)
        INDEX (GLDSRV.DIR_SEARCHX1 KEYCARD
                FREQVAL NUMCOLS 1 COUNT 100
                FREQVAL NUMCOLS 2 COUNT 100,
               GLDSRV.DIR_SEARCHX2 KEYCARD
                FREQVAL NUMCOLS 1 COUNT 100
                FREQVAL NUMCOLS 2 COUNT 100
        COLGROUP (VALUE) FREQVAL COUNT 100
        SHRLEVEL CHANGE UPDATE ALL SORTDEVT SYSDA
      RUNSTATS TABLESPACE GLDDB.ENTRYTS REPORT YES
        TABLE (ALL)
        INDEX (GLDSRV.DIR_ENTRYX1 KEYCARD
                FREQVAL NUMCOLS 1 COUNT 100)
      RUNSTATS TABLESPACE GLDDB.DESCTS REPORT YES
        TABLE ALL INDEX ALL KEYCARD
      RUNSTATS TABLESPACE GLDDB.LENTRYTS REPORT YES
        TABLE ALL INDEX ALL KEYCARD
      RUNSTATS TABLESPACE GLDDB.LATTRTS REPORT YES
        TABLE ALL INDEX ALL KEYCARD
      RUNSTATS TABLESPACE GLDDB.MISCTS REPORT YES
        TABLE ALL INDEX ALL KEYCARD
      RUNSTATS TABLESPACE GLDDB.REPTS REPORT YES
        TABLE ALL INDEX ALL KEYCARD
   /*
Note:
  1. In the example shown, GLDDB is the TDBM database name and GLDSRV is the user ID used to create the TDBM tables and indexes. GLDSRV is the same value that is used in the LDAP server configuration file for dbuserid.
  2. In the example shown:
    • GLDSRV.DIR_SEARCHX1 is the index on columns ATTR_ID,VALUE,EID of table GLDSRV.DIR_SEARCH.
    • GLDSRV.DIR_SEARCHX2 is the index on columns EID,ATTR_ID of table GLDSRV.DIR_SEARCH.
    • GLDSRV.DIR_ENTRYX1 is the index on columns PEID,EID of table GLDSRV.DIR_ENTRY.
  3. In the example shown, the values that are specified for COUNT are intended as guidelines and needs to be updated depending on the data in the LDAP TDBM database. These numbers represent a minimum frequency. If there is a large amount of data in a TDBM database and potentially many frequent values, these numbers needs to be increased until the optimum frequency is found.
  4. When the LDAP server is started, it caches the statistics that are recorded by the RUNSTATS utility in the Db2 catalog. These statistics influence some of the SQL queries that are generated by the LDAP server in regard to the use of literal values versus parameter markers. Doing so provides frequent values to the Db2 optimizer, while avoiding the flooding of the prepared statement cache with highly varying literal values, such as userIDs. Additionally, informational messages are issued to the server output file detailing the column statistics and messages that are issued if any statistics appear to be insufficient. See LDAP database definitions in your SPUFI files to correlate the table space and index names in the RUNSTATS input with the table and column names that appear in these messages. If you run the Db2 RUNSTATS utility after the LDAP server has completed initialization, you can use the LDAP server REFRESH DB2RUNSTATS operator modify command to refresh the cache and reexamine the statistics. The cached statistics also get refreshed each time the TDS-Db2 health check runs for the backend.

Many installations populate the z/OS LDAP directory with a large amount of initial data and then gradually grow the directory over time with routine updates and additions. In such cases, it is suggested that the REORG and RUNSTATS utilities be run immediately after the directory is populated with this initial data before rollout to production. If the initial population of data is done by using an application (as opposed to the ldif2ds load utility provided with the z/OS LDAP server), it can be necessary to run REORG and RUNSTATS one or more times during the process of initially populating the directory. This is needed to ensure that Db2 uses efficient access paths based on the statistics that are gathered from the database, once it contains a representative amount of information. Without this information, poor access paths can be chosen that cause increasing response times as the size of the database increases, and gradual slowing of the process of populating the directory.

To determine the need of running REORG and RUNSTATS utilities, the LDAP administrator can enable the TDS-Db2 health check support on a TDBM or Db2-based GDBM backend, and customize the specific DSNACCOX parameters in the LDAP server configuration file. The Db2 DSNACCOX stored procedure uses the customized parameters as the criteria to generate REORG and RUNSTATS recommendations for the backend related tablespaces and indexes. Some of the criteria parameters can be changed to the actual value by DSNACCOX and written to debug INFO output if either REORG or RUNSTATS is required. The returned values are beneficial for tuning the DSNACCOX criteria parameters. The LDAP administrator can also utilize the DSNACCOX exception table to exclude some tablespace or index from the health check report. For more information, refer to the DSNACCOX stored procedure information in Appendix A in DB2 Managing Performance.

Db2 buffer pool allocations should also be examined to ensure that they are sufficient for the LDAP TDBM database. It is often useful to isolate specific TDBM table spaces and indexes to their own buffer pools. In particular, separating the indexes from the table spaces can help ensure that the index buffers remain in the buffer pools. This technique can also help evaluate overall behavior of the LDAP database regarding its buffer pool usage when specific tables and indexes correlate to specific buffer pools. Products such as the Db2 Performance Monitor for z/OS are especially useful for monitoring buffer pool activity.

See Range-partitioned Db2 table spaces for TDBM for more information about increasing TDBM performance for Db2 partitioning, if you have a large directory and do many update operations on the directory.

TDBM database tuning

Several choices might ultimately affect the performance of TDBM when accessing its data in Db2:
  • The LOCKSIZE chosen on the TDBM table spaces can be important if you perform many database updates. The default LOCKSIZE of ANY is generally preferred, and is typically sufficient if you perform mostly query activity and low volumes of updates to the database. This usually results in PAGE locking, that causes locking of rows for directory entries other than the one being updated. However, if you have high volumes of update activity, you might experience Db2 deadlocks in the TDBM database with PAGE locking. If a deadlock occurs, you might want to set LOCKSIZE ROW on the TDBM table spaces that contains the DIR_ENTRY and the DIR_SEARCH table.
  • The size of the DN_TRUNC column of the DIR_ENTRY table specified at database creation time.

    The DN_TRUNC column is used to index data in the DIR_ENTRY table to speed up retrieval of directory entries by way of their distinguished name (DN). This column holds the leading portion of each DN, and should be defined long enough to make most values unique.

    Some applications generate directory entries where the leading portion of the DN is identical. For example, Tivoli® Access Manager (TAM) generates entries under each user entry in the namespace where the DN starts with "cn=secPolicyData,secAuthority=Default,". To provide uniqueness, it is suggested that installations that use TAM with the z/OS LDAP server, define the DN_TRUNC column to be 64 bytes in length.

    Define this column at its correct length during initial setup of the directory. Changing the size requires the DIR_ENTRY table to be redefined, and the directory must be unloaded and reloaded to implement the change.

  • The size of the VALUE column of the DIR_SEARCH table specified at database creation time.

    The VALUE column is used to index data in the DIR_SEARCH table to speed up retrieval of directory entries for search requests by using the search filter values. This column holds the leading portion of textual attribute values, and should be defined long enough to accommodate most values specified in search filters. However, this column should not be made significantly larger than needed, since it might cause the DIR_SEARCH table and its index to substantially increase in size.

    Define this column at its correct length during initial setup of the directory. Changing the size requires the DIR_SEARCH table to be redefined, and the directory must be unloaded and reloaded to implement the change.

  • The attrOverflowSize value specified in the TDBM section of the LDAP server configuration file.

    This configuration option specifies the threshold size of attribute values that are stored separately from the DIR_ENTRY data and are instead stored in the DIR_LONGATTR overflow table.

    This option can avoid unnecessarily reading this overflow data for searches that do not request the attribute. For example, if your directory entries contain JPEG data, but many searches ask for specific attributes and omit the large JPEG attribute from those requested, this option can help avoid reading unnecessary data from the database.

    Specify the option value large enough so that data that is typically retrieved with the entry remains in the DIR_ENTRY data. Because entries with overflow data are not eligible for the entry cache, making this option value too small might affect search performance.

    Note: The LDAP server does not automatically reorganize the database when the attrOverFlowSize value changes. Because the updated overflow size applies only to new attribute values, previously overflowed attribute values will stay in the DIR_LONGATTR table even if they are no longer overflowed. To make sure that the new attrOverFlowSize value is applied to the entire backend, follow these steps:
    1. Unload the backend data with the ds2ldif utility.
    2. Shut down the LDAP server and update the attrOverFlowSize value in the server configuration file.
    3. Drop the Db2 database of the backend and rerun the SPUFI script to create a new one.
    4. Start the LDAP server and reload the backend data with the ldif2ds utility.
  • The fetchSize value specified in the TDBM section of the LDAP server configuration file.

    This configuration option specifies the maximum buffer size, in bytes, that is used to hold the result set returned by ODBC. The fetch size determines the number of rows in the row set, if multi-row fetch is applicable. An appropriate fetch size is beneficial regarding performance, as a single retrieval of several rows is more cost efficient than multiple retrievals of fewer rows. In addition, raising the fetch size also increases the storage that is used by the LDAP server.

    The default value is 65536 (64 KB).