Configuration Best Practices › Monitor KEY Usage Statistics

Monitor KEY Usage Statistics

The CA Datacom system allows multiple indexes or keys to be created for each CA Datacom table. Keys are added to the table’s definitions to improve data access capabilities.

Each key on a given table has three specific costs:

• Each add or delete of a row, requires that all keys on the table must be added or deleted for the matching row. In addition, an update request against a specific row can cause key values to be updated if a data column or columns used to build the key have been changed.
• For each key and its unique key-values, there is a storage requirement in the IXX data set.
• There is a more subjective cost of having many keys in a given IXX data set that is based on the number of binary levels that is required to house all of the key values for a given database. Each key that is defined, adds to the amount of index blocks that must be maintained. These index blocks are part of CA Datacom binary b-tree+ index structure. The more index blocks you have, the more likely that you have a multiple level index tree structure. The more levels in the index, the more logical processing that every indexed process (for the entire database) must take to find the requested data row.

The Dynamic System Table DIR_KEY provides a count of accesses by RAAT (USES_RAAT), SAAT (USES_SAAT) and SQL (USES_SQL). These counts show key usage since the last time the IXX was rebuilt.

You can monitor these key usage statistics and determine if specific keys exist that no longer are being used. Consider removing these keys.

In addition, you can review keys that have limited or light use. You can determine which programs are using these keys and, if possible, change the program to other keys on the table and remove the lightly used keys.

Business Value

Reducing the number of unused keys (per table) reduces the processing (CPU usage and I/O) needed to add or delete rows and saves IXX data set space. In addition, reducing the number of keys in the IXX can have a positive effect on the index processing levels.

Additional Considerations

For a lightly used key, you can set up the CA Datacom/DB Accounting Facility to collect the names of the programs or transaction that are using the key. This could aid in determining if the lightly used key could be removed and the programs changed to another existing key.

For more information about using the Dynamic System Tables database, see the CA Datacom/DB Dynamic System Tables Reference Guide.