Managing Database Sequences
When a unique identifier is needed by a program, the system often uses a control field to store the last number used. The system also supports the use of a special schema element called a sequence.
A sequence is a database element used to generate a stream of sequential values for assigning unique identifiers to records. Sequences allow fast, accurate numbering, and reduce the amount of time the system spends validating uniqueness.
Note: Because the sequence is generated at the database level, records viewed from within a domain may appear to have gaps.
Use Sequence Report (36.16.15) to display a list of sequences defined in the database. The sequence description indicates the database table and field that is updated by the sequence. For example, the description of sequence cmt_sq01 is cmt_det.cmt_indx.
Sequences have the important advantage of speed and reducing the possibility of record locking and contention. However, each sequence is a separate database element, distinct from the table to which it applies. This means that sequences must be initialized correctly whenever you use Database Table Dump/Load.
If sequences are not initialized correctly, Duplicate Unique Key errors may occur when users attempt to create transactions.
If dumping and loading are done as part of installing a software upgrade, sequence initialization is automatically performed by the installation utilities. However, if you perform a dump/load to consolidate tables or increase database size, you must initialize sequences yourself. This is true also if you consolidate data from two different databases.
• Use Database Sequence Initialization (36.16.17) to reset sequences to the highest value plus 1 after loading data.This program works with both Progress and Oracle databases.
• Use Sequence Maintenance (36.16.13) to manually reset a sequence number to a specific value in a Progress database.
• Use Sequence Inquiry (36.16.14) or Sequence Report (36.16.15) to view sequence information.
To guarantee database integrity, perform sequence maintenance:
• In single-user mode sessions only
• As a required part of your standard database maintenance
Note: To avoid accidental update to sequence structures, use menu security to protect sequence maintenance functions.