Database organization (reorgchk and reorg)

You can tune the organization of the data in a DB2® database to improve database performance and to save disk space.

To tune the organization of the data in DB2 database, you can use the reorgchk and reorg commands. You can reorganize table spaces to improve access performance and to reorganize indexes so that they are more efficiently clustered.

The reorgchk command does the following operations:

Updates the DB2 optimizer with database statistics to improve database performance.
Reports statistics on the organization of the database tables.

Based on the statistics that are generated by the reorgchk command, you must run the reorg command to update database table organization. The reorgchk and reorg commands can improve both search and update operation performance.

The DB2 reorg command reorganizes the table and its indexes by rebuilding the index data into unfragmented and contiguous area on the disk. This operating requires locking the data for movement. If there are applications that access this data, then the application performance might be degraded. You must not run the DB2 reorg command while the directory server and DB2 database is in production phase. You must run the reorg command against the directory server and the DB2 database during the maintenance phase.

After you complete the database organization operation, you might require to restart the directory server if you do not observe performance benefits. If the SQL statements are cached by DB2 and directory server instance, you might require to clear the package cache and restart the server.

You must run the reorgchk command periodically. For example, you must run reorgchk after you update a directory server instance with entries. The performance of the database must be monitored and you must run reorgchk if server performance starts to degrade.

In a replication environment, you must run the reorgchk command on all the replica servers because each replica uses a separate database. If database is optimized on a master server, the replication process does not propagate database optimizations to replica servers.

Guidelines for reorganization

You must consider the following guidelines before you run DB2 database reorganization:

You can skip reorganization of a table or index, if you meet the following conditions:
- A reorganization attempt is already done.
- The number on the column that contains an asterisk is close to the suggested value that is described in the header of each section.
From the LDAP_ENTRY_TRUNC index and the SYSIBM.SQL index in the LDAPDB2.LDAP_ENTRY table, preference must be given to the SYSIBM.SQL index.
When an attribute length is defined with less than or equal to 240 bytes, the attribute table contains three columns: EID, attribute, and reversed attribute. In this case, the forward index is created by using the EID and attribute columns as index keys. For example, the SN attribute is defined with the maximum length, which is less than or equal to 240 bytes. The attribute table contains the EID, SN, and RSN columns and the following indexes are created for the attribute table:
```
LDAPDB2.RSN <------A reverse index whose defined index keys are the EID 
	and RSN columns. 
LDAPDB2.SN<------A forward index whose defined index keys are the EID 
	and SN columns. 
LDAPDB2.SNI <------An update index whose defined index key is the EID column. 
```
Reorganize all the attribute tables that you want to use in searches. In most cases, you might want to reorganize to the forward index. For searches that begin with * (wildcard), reorganize to the reverse index.
When an attribute length is defined with greater than 240 bytes, the attribute table contains four columns: EID, attribute, truncated attribute, and reversed truncated attribute. In this case, the forward index is created by using the EID and truncated attribute columns as index keys. For example, the CN attribute is defined with the maximum length, which is greater than 240 bytes. The attribute table contains the EID, CN, CN_T, and RCN_T columns and the following indexes are created for the attribute table:
```
LDAPDB2.RCN <------A reverse index whose defined index keys are the EID 
	and RCN_T columns. 
LDAPDB2.CN<------A forward index whose defined index keys are the EID 
	and CN_T columns. 
LDAPDB2.CNI <------An update index whose defined index key is the EID column.
```

An example output with reverse, forward, and update indexes:

Table: LDAPDB2.SECUUID
LDAPDB2 RSECUUID <— This is a reverse index
LDAPDB2 SECUUIDI <— This is an update index
LDAPDB2 SECUUID <— This is a forward index