Choosing Physical Tuning Options › Relationship Tuning Options › Linked and Unlinked Relationships

Linked and Unlinked Relationships

Linked and unlinked

When implementing these relationships, there are a number of physical tuning options from which to choose. You have already decided whether a relationship is a clustering relationship or not. You must now decide whether to define the relationship as linked or unlinked.

• A linked relationship is one in which related entity occurrences are linked to one another through embedded pointers.
• An unlinked relationship is one in which no embedded pointers are used to link related entity occurrences.

Advantages of linked relationships

Linked relationships have the following advantages:

• Since there is direct linkage between parent and related child occurrences, linked relationships provide the most efficient means (in terms of CPU and I/O) of retrieving related entity occurrences.
• Unlinked relationships require that a CALC key or index be defined on the foreign key of the child entity.
• An index adds both CPU and I/O to retrieve data and maintain the index. It also requires additional storage space.
• Defining a CALC key on the foreign key is almost as effective as using a linked relationship provided that it does not cause CALC overflow conditions, which increases I/O, CPU, and contention. However, you can define only one CALC key per entity, so that an entity participating as a child in more than one relationship must use indexes for all but one unlinked relationship.
• Linked relationships provide an ordering option that can reduce the need for additional indexes to enforce unique constraints and avoid sorting of retrieved information.

Considerations

Keep the following considerations in mind when using linked and unlinked relationships.

• Self-referencing relationships must always be unlinked.
• Linked relationships require physical restructuring of entity occurrences to add or remove relationships.
• The time required for and impact of maintenance operations, such as unload/reload, can be reduced if relationships between entities in different areas are unlinked. This is particularly important in designing large databases.

Non-SQL considerations

In a non-SQL environment:

• There is no integrity enforcement by the DBMS with an unlinked relationship. Integrity must be enforced by applications or logical record facility path logic.
• There is no relationship clustering with an unlinked relationship. You must use CALC clustering to achieve results similar to clustering.

Note: If CALC clustering results in long CALC chains, CPU, I/Os, and contention might all increase.

You can eliminate foreign keys from child entities if the relationship is linked. This has the following results:

• It reduces storage requirements
• It eliminates the need to update each child occurrence if the parent's key is changed.

  For example, if you change the value of DEPT ID in a department, related employees do not need to be updated.

If you choose to retain the embedded foreign keys, you:

• Have full update SQL access to the data
• Will reduce the number of I/Os required to retrieve foreign key values for nonclustered entities (for example, to retrieve the department ID of an employee)