Using the same Datacom-ID for different keys defined in different tables in a database

Products

• Datacom
• DATACOM - AD
• Datacom/AD
• Datacom/DB

Issue/Introduction

Is it possible to use the same Datacom-ID for keys defined in different tables in the database?

Environment

Release: All supported releases.

Resolution

Datacom/DB allows up to 999 distinct keys within a single database, and in most cases, each key will be defined uniquely from any other key. However, there are times where using the same Datacom-ID (called a Shared ID) could be beneficial.

The database index is created based on the Datacom-ID, and therefore, a Shared ID will cause the index records to be loaded for multiple tables all together. This provides a good benefit if data from the multiple tables is retrieved together, such as with a customer order header and payment header. As order information and payment information might often be retrieved at the same time, and since there is generally a one-to-one relationship of these tables (there might be occasionally more payments per order), the keys used for these two tables might be defined with the same Shared ID.

The benefit is achieved by having the index entries located close enough together that they could be retrieved in a single I/O and stored in memory together.

To best utilize this Shared-ID, the high-order fields in the key should be the same. In the above example, the key might have Customer-ID and Order-ID as the entire key, or as the high-order portion of the key. Other fields can be part of the key, and there is no problem if those other fields do not match.

The Shared-ID is best used where there is a nearly one-to-one ratio of the rows that belong to the key entry, so the index entries are retrieved within the same block in a single I/O. A poor use of this would be in a case where a single Order Header would have a large number of Order Detail records. In this case, even though the high-order portions of the keys would match, the volume of data rows for a given key means that it could still take more than a single I/O to retrieve the related records.

Since the Shared-ID works best where the high-order portions of the key match, having a Shared-ID for two keys with no matching fields will reduce the overall efficiency of the index, and should be avoided. In this case, the default action of having a distinct Datacom-ID is the best approach.

Additional Information

Designing Keys > Sharing Key IDs
Designing Data Areas › Data Area Space Management Options › Option 3: Clustering