Sequences
A sequence is a database object used to generate unique sequential integer values. Sequences are typically used to create a temporary primary key value that must be unique for each row. An internal routine generates the sequence and then increments or decrements it.
You can create, modify, or delete sequences. You can also set permissions and view their properties.
Note: Sequences, events, and rules cannot be created for Actian Vector tables.
Sequence Dialog
The Sequence dialog contains the following pages:
• General
• Options
These pages are described in the following sections.
General Page Options
This page contains the following fields:
Name
Data Type
Specifies the data type of the sequence as one of the following:
• Integer
• Big Integer
• Decimal (with an optional precision, but 0 scale)
Default: Integer
Note: Both bigint and integer sequences are managed internally as 64-bit integers.
Precision
If you specified the Decimal data type, specifies decimal precision
Default: 5
Options Page Options
This page lets you specify the following sequence options:
Cache Size Defined
Specifies whether sequence values are to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.
Default: not defined
Note: Gaps between two sequence values may occur: If the DBMS is restarted between two requests of a sequence value, the cached but unused values are lost, and the cached values are not written back when shutting down. After restarting, the Sequence Cache is empty, so the next value is taken from iisequence.
Note: In a multiple DBMS shared cache environment, sequence values may not be in chronological order. Each DBMS maintains its own Sequence Cache. No value appears twice, but the order of the requested values depends on which DBMS is returning the sequence value.
Cache Size Value
Specifies the number of sequence values held in server memory. When the supply of numbers is exhausted, Ingres requires a catalog access to acquire the next set. This value is used only if Cache Size Defined is checked.
Default: 20, which promotes low catalog overhead
Cycle
Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).
Default: do not cycle
Random
Specifies whether values are returned sequentially or in unordered sequence. Random is a synonym for unordered. This option is ignored for decimal-based sequences.
Increment Defined
Specifies whether a user-defined increment is used to increment the sequence
Default: not defined
Increment Value
Specifies the increment value (positive or negative) that produces successive values of the sequence. This value is used only if Increment Defined is checked.
Default: 1
Maximum Defined
Specifies whether sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).
Default: not defined
Maximum Value
Specifies the maximum value allowed for the sequence. This value is used only if Maximum Defined is checked.
Defaults:
• For positive integer sequences: 2**31-1
• For positive bigint sequences: 2**63-1
• For positive decimal(n) sequences: 10**(n+1)-1
• For negative sequences: -1
Minimum Defined
Specifies whether sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).
Default: not defined
Minimum Value
Specifies the minimum value allowed for the sequence. This value is used only if Minimum Defined is checked.
Defaults:
• For positive sequences: 1
• For negative bigint sequences: -2**63
• For negative integer sequences: -2**31
• For negative decimal(n) sequences: -(10**(n+1)-1)
Restarts with Defined
Specifies whether a restart value is to be defined explicitly for the sequence
Restarts with Value
Specifies a new start value for the sequence. This value is used only if Restarts with Defined is checked. This option is valid only when altering sequences.
Starts with Defined
Specifies whether a start value is to be explicitly defined for the sequence
Starts with Value
Specifies the start of the sequence as an integer constant. This value is used only if Starts with Defined is checked. This option is valid only when creating sequences.
Defaults:
• For positive sequences: 1 (positive increment)
• For negative sequences: -1 (negative increment)
For more information, see
Create a Sequence.
Create a Sequence
Before you can create a database sequence, the
Ingres installation containing the database must be connected in the Instance Explorer (see
Connect to an Actian Database Instance).
Note: Sequences, events, and rules cannot be created for Actian Vector tables.
You create sequences using the
New Sequence dialog (see
Sequence Dialog). You can open this dialog the following ways:
• From the context menu of a database’s Sequences folder or sequence node: New Sequence
• On the main menu ribbon when the Sequences folder or a sequence node is selected in the Instance Explorer:
Database, Sequence, New Sequence (see
Database Tab)
To create a database sequence
1. Open the New Sequence dialog in one of the ways explained previously.
3. Select a data type.
4. Click the Options link in the “Select a page” portlet.
6. When you are finished, click OK.
The sequence is created and stored in the Sequences folder of the database.
Modify a Sequence
Before you can modify a database sequence, the
Ingres installation containing the database must be connected in the Instance Explorer (see
Connect to an Actian Database Instance).
You alter sequences using the
Modify Sequence dialog (see
Sequence Dialog). You can open this dialog the following ways:
• From the context menu of a database’s sequence node: Modify
• On the main menu ribbon when a sequence node is selected in the Instance Explorer:
Database, Sequence, Modify Sequence (see
Database Tab)
To modify a database sequence
1. In the Instance Explorer, right-click the sequence node you want to modify and select Modify from the context menu.
The Modify Sequence dialog opens.
2. Select a data type.
3. Click the Options link in the “Select a page” portlet.
5. When you are finished, click OK.
The sequence is modified and stored in the Sequences folder of the database.
Delete a Sequence
Before you can delete a database sequence, the
Ingres installation containing the database must be connected in the Instance Explorer (see
Connect to an Actian Database Instance).
You delete sequences using the Delete Sequence dialog. You can open this dialog the following ways:
• From the context menu of a sequence node: Delete
• On the main menu ribbon when a sequence node is selected in the Instance Explorer:
Database, Sequence, Delete Sequence (see
Database Tab)
To delete a database sequence
1. Open the Delete Sequence dialog in one of the ways explained previously.
2. Click OK.
The sequence is removed from the Sequences folder of the database.
View Sequence Properties
Before you can view the properties of a database sequence, the
Ingres installation containing the database must be connected in the Instance Explorer (see
Connect to an Actian Database Instance).
You view sequence properties using the Sequence Properties dialog. You can open this dialog the following ways:
• From the context menu of a sequence node: Properties
• On the main menu ribbon when a sequence node is selected in the Instance Explorer:
Database, Sequence, Sequence Properties (see
Database Tab)
To view the properties of a database sequence
1. Open the Sequence Properties dialog in one of the ways explained previously.
The properties of the sequence are displayed.
2. Click individual properties to display descriptions at the bottom of the dialog.
3. Click OK to close the dialog.