Can I use the same Datacom-ID for different keys defined in different tables in my database?

book

Article ID: 35423

calendar_today

Updated On:

Products

CA Datacom - DB CA Datacom CA Datacom - AD CA Datacom - Server CA CIS CA Common Services for z/OS CA 90s Services CA Database Management Solutions for DB2 for z/OS CA Common Product Services Component CA Common Services CA Datacom/AD CA ecoMeter Server Component FOC CA Easytrieve Report Generator for Common Services CA Infocai Maintenance CA IPC Unicenter CA-JCLCheck Common Component CA Mainframe VM Product Manager CA Chorus Software Manager CA On Demand Portal CA Service Desk Manager - Unified Self Service CA PAM Client for Linux for zSeries CA Mainframe Connector for Linux on System z CA Graphical Management Interface CA Web Administrator for Top Secret CA CA- Xpertware

Issue/Introduction

Question: 

Can I use the same Datacom-ID for keys defined in different tables in my database?

Answer: 

CA 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:

For more information about Shared-IDs for keys and data clustering, please refer to the following Guides:

Environment

Release:
Component: DB