Configuration Best Practices › Implement Multi-Dataset Index

Implement Multi-Dataset Index

This best practice is associated with two of the preceding best practices.

In the previous section on using MRDF COVERED Index and data areas to reduce physical I/O processing, we discussed the use of MRDF COVERED data sets to keep Index Areas memory-resident to improve performance and reduce physical I/O. One of the constraints on MRDF processing is the limit of 9 GB of 64-bit memory per Index Area (or 2 GB, if using 31-bit memory). Many of the largest database Index Areas may exceed the 9 GB limit. In these cases, you can choose to cover part of the IXX to gain performance. However, there is no way to tell MRDF to only cover the high-use keys.

In the previous section on monitoring KEY usage statistics, we described methods that you can use to monitor which keys are heavily used and which keys are lightly used.

The Multi-Dataset Index allows you to spread the database index across multiple data sets. The database must still have an IXX data set which houses all CA Datacom internal index entries, such as the free space index, URI index, and the default storage location for all user-specified table indexes (keys).

In addition to the IXX, you can specify additional Inn data sets, where the nn is a number between 01 and 99. The Inn data sets are initialized like the IXX and can be selectively used to house user-specified keys.

Three specific user benefits are provided:

• It allows large index areas to be spread across multiple data sets. Because there are multiple data sets, each data set has a subset of the index blocks that were housed in the global IXX. The internal binary b-tree+ structures have fewer blocks to manage, and therefore can have fewer levels and be more efficient to process.
• Since you can selectively move only certain indexes (keys) to one of these new data sets, you can separate heavy use indexes from lightly used indexes for performance reasons.
• Since there are multiple data sets, any failure on a given data set can be recovered by running an index rebuild on only the failed data set’s key IDs. More information is provided in the sections describing the DBUTLTY RETIX function.

Once you use the key usage statistics to determine what keys can be moved to the additional Inn data sets, monitor the multiple index data sets to determine which of the data sets have the heaviest read I/O activity. Since the Inn data sets most likely are smaller in size, it makes it easier for you to employ the MRDF COVERED option on the high-use index data sets and get an overall better return for the memory allocated.

Business Value

The multiple data sets can be used to segment large index data sets by key ID. This segmentation provides three significant advantages:

• Since there are multiple data sets, and a data set that is lost or damaged can be recreated separately, the database outage is greatly reduced.
• Multiple data sets have smaller subsets of blocks which should provide an improved efficiency of processing.
• By separating high-use keys to their own data sets, the CA Datacom MRDF processing is used to significantly improve the processing for those high-use keys.

Additional Considerations

To implement a Multi-Data Set Index, use the CA Datacom Datadictionary to change the definitions for the selected keys to select your alternate Inn data set. When you are finished, catalog the database definition to the CXX. The CXX definition shows the new alternate Inn area or areas. Allocate the Inn data set and then run a DBUTLTY INIT AREA=Inn function. Once these additional areas are created and initialized, reload the database table or recreate the index areas using DBUTLTY RETIX.

A given key ID can only be directed to the IXX or to an Inn data set.

More Information

• For more information about the DBUTLTY INIT and RETIX functions, see the CA Datacom/DB DBUTLTY Guide.
• For more information about making theCA Datacom Datadictionary changes to the key definitions, see the CA Datacom Datadictionary guides