Choosing Physical Tuning Options › Non-SQL Tuning Options

Non-SQL Tuning Options

Sorted relationship considerations

When you store an entity occurrence in a sorted chained relationship, the DBMS searches the relationship in the next direction, starting with the current entity occurrence. If the new occurrence cannot be inserted in the next direction, the DBMS establishes currency on the parent entity occurrence and begins the search from this occurrence (moving in the next direction). When you store an entity occurrence in a sorted indexed relationship, the DBMS searches the occurrences starting from the top of the index structure.

Note: If the DUPLICATES FIRST option is specified for a sorted relationship and the key of the current entity of set is equal to the key of the entity to be stored, the DBMS must begin its search for the insertion point from the owner entity.

Store operations are executed most efficiently when the new entity can be inserted either at the very beginning or the very end of the relationship. If new entities are consistently stored in ascending order, you should perform one of the following procedures to ensure that insertions of new entity occurrences into the relationship will be performed efficiently:

• Assign the descending sort sequence to the relationship. In this case, the sequence in which entities are sorted in the relationship is the opposite of the sequence in which new entities are added, as shown below.

  

  

  Note: When you write a program to perform the initial load of the database, plan to sort the entities in the same order as the relationship order to optimize processing efficiency. For example, if dated entities are maintained in a relationship that is sorted in descending order, sort the initial load file in descending order before performing the load.

• If you have the option to sort input entities before executing the store operation, you may want to define the sort order as ascending and allow the programmer to issue program statements that optimize efficiency. In this case, you should ensure that the programmer establishes currency at the end of the relationship before issuing the store statement command:

  FIND OWNER
  FIND LAST IN SET
  STORE
  

  Remember that you must include PRIOR pointers if you plan to let programmers issue FIND/OBTAIN LAST statements against a chained linked relationship. Without PRIOR pointers, the DBMS must walk the entire linked relationship in the next direction to access the LAST entity.

For more information on pointers, see "Linkage" later in this chapter.

If input entities are consistently stored in descending order, perform one of the following procedures:

• Assign the ascending sequence to the relationship.
• Have programmers establish currency at the beginning of the relationship before issuing the store command:

  FIND OWNER
  STORE
  

For further information on the DML statements used to access the database, see CA IDMS DML Reference Guide for COBOL.

Representing a sorted relationship

Represent a sorted relationship on the data structure diagram by specifying ASC or DES and the name of the sort key as part of the relationship specification.

There are additional tuning options available to non-SQL implementations. These are described in this chapter.