Schema Statements › SET Statement › Usage

Usage

Set Automatically Deleted if Owner Record is Deleted

If a set's owner record is deleted (by a DELETE RECORD statement), the set is automatically deleted. Additionally, the deleted record and set are deleted from all subschema descriptions associated with the current schema. But if a set's member record is deleted (by a DELETE RECORD statement), the set remains.

Explicitly deleting a set

To delete the set (if it has no other member records), use the DELETE SET statement. DELETE deletes the named set description from the data dictionary. Consequently, the set is removed not only from the current schema, but also from the descriptions of all subschemas associated with the current schema. No optional clauses are valid for DELETE operations.

Default automatic pointer assignments for owner records

A valid set description requires pointer positions for the owner record and for each member record.

The defaults for the owner pointer positions depend on the set's mode specification as shown in the following table. Positions for which "none" is indicated have no default and must not be specified; there is no such pointer position for these modes.

Default automatic pointer assignments for member records

A valid set description requires pointer positions for the owner record and for each member record. The defaults for the member record pointer positions depend on the set's mode specification as shown in the following table. Positions for which "none" is indicated have no default and must not be coded; these modes have no such pointer position.

Unlinked indexes

An unlinked index is a system-owned index in which there are no index pointers in the member records. You specify an unlinked index by using the OMITTED option on the INDEX DBKEY POSITION clause of the MEMBER RECORD clause. Unlinked indexes provide the following advantages:

• You can load and rebuild unlinked indexes faster
• You can add or remove an unlinked index without restructuring the database, provided the control length of a compressed or variable length member record is not changed

However, unlinked indexes may increase processing overhead. For a more thorough discussion of the considerations, see the CA IDMS Database Design Guide.

The set options for an unlinked index must be MANDATORY AUTOMATIC.

Pointer positions in a record

Note that for a given record, each position must be assigned to only one set pointer, and the positions within the record must be contiguous.

SAME AS SET clause reduces coding

Because SAME AS SET copies an existing description, it can relieve the DBA of a considerable amount of coding. The DBA can create a base set description with SAME AS SET and code additional clauses to alter the description of the new set as desired.

Restrictions for SAME AS SET clause

SAME AS SET must not be specified for a set to which order, mode, owner, or member already is assigned. Consequently, placement of the SAME AS SET clause is restricted as follows:

• ADD operation—When used in an ADD operation, SAME AS SET must precede all other optional clauses.
• MODIFY operation—SAME AS SET cannot be used in a MODIFY operation unless the set was added with no optional clauses.

Don't change set pointers for existing databases

Do not change set pointers for existing databases. Use the NEXT DBKEY POSITION, PRIOR DBKEY POSITION, INDEX DBKEY POSITION only when adding new sets or when changing sets in a schema for which a database is not yet defined. If you must change set pointers, for example because a set is deleted, you must restructure your database.

Determine pointer positions before assigning pointers

For a given record, each position must be assigned to only one set pointer, and the positions within the record must be contiguous. When assigning positions manually, determine the pointer positions for all sets in the schema before coding set descriptions. This will avoid any conflicts (such as attempting to use the same position twice) and will speed up the mechanical process of adding set descriptions to the schema description.

Percentage offsets assist database maintenance

Of the page limiting options, OFFSET with percentage specifications is the most flexible. As a database grows and must eventually be expanded, the physical areas of the database must also be expanded. If the DBA originally expressed the owner record's page range as a percentage of an area, the range need not be respecified to fit the new physical area description; the runtime system will automatically assign the owner record to the same relative position in the new physical area.

Foreign keys and control length

The specification of a foreign key does not affect the control length of the member record. Foreign key elements may occur beyond the last control key even if the record is compressed or variable in length. However, if a foreign key element does begin after the control length and the record has a database procedure which will change the value of the foreign key field on a store or modify (for example, to convert it to upper case), then you should not use SQL INSERT statements to store new occurrences, nor SQL UPDATE statements to change the value of the foreign key. If you do use these statements, the value of the foreign key field before the procedure is executed will be used to validate the primary/foreign key relationship. This may cause the update to fail on a referential constraint violation or it may cause the member record to be associated with an incorrect owner.

Mixed page groups

Chained sets may not cross page group boundaries regardless of the MIXED PAGE GROUP BINDS ALLOWED option setting.