SequenceValue Method
Given a database table and an integer table column, the SequenceValue method returns a positive integer that is the new sequence value for the column. You can use the sequence value as a surrogate key to assign to a table row or insert into other rows in other tables as a join attribute.
This method has the following syntax:
key = DBSessionObject.SequenceValue(
table_name = table_name, column_name = column_name
[, increment = increment,
start_value = start_value])
This method has the following parameters:
key
Specifies an integer variable that holds the sequence value generated
table_name
(Required) Specifies a string expression whose value is the name of the table for which the SequenceValue method generates a key value. The table must already exist in the database.
If this expression contains owner.tablename, both the table owner and table name are parsed and used to select the appropriate row in the II_SEQUENCE_VALUES table. Avoid putting two rows into the II_SEQUENCE_VALUES table by consistently passing either tablename or owner.tablename. Use tablename for databases that do not contain two tables with the same name owned by different users.
column_name
(Required) Specifies a string expression whose value is the name of the column for which the SequenceValue method generates a key value. The column must already exist in the table.
increment
Specifies a positive integer that specifies the number of sequence values to be returned and added to the value stored in the table
Default: 1
start_value
Specifies a positive integer that indicates where to start generating sequence keys for an empty table if the SequenceValue method has never been called before for this table/column combination
Default: 1
The SequenceValue method generates sequential values by retrieving the next row from an extended system catalog called II_SEQUENCE_VALUES. This catalog contains a row for each unique table/column pair for which sequence values are defined:
sequence_owner
Owner of a table. Varchar(32). Not nullable.
sequence_table
Name of a table. Varchar(32). Not nullable.
sequence_column
Name of column in a table. Varchar(32). Not nullable.
sequence_value
Integer. Not nullable.
If a record exists in the catalog, the SequenceValue method increments it and returns the result. If not, this method searches for the current maximum value for the column and returns that value + 1. In this case, the insert statement must be in the same transaction as the invocation of the SequenceValue method.
If the table contains no rows that have non-null values for the column, it uses the start_value instead. If a record does not exist in the catalog, the SequenceValue method adds one. The default is one. The return of a negative integer or zero indicates an error.
Consider the following issues when using the SequenceValue method:
• If you insert any rows into the table without using the SequenceValue method (for example, directly through SQL), these rows may not have sequential key values.
This is because only the SequenceValue method retrieves the next value from the II_SEQUENCE_VALUES catalog.
• To prevent non-unique values, the database table should have a structure that helps to ensure unique column values.
• There is no 3GL equivalent of this function. Therefore, you cannot use a 3GL procedure to generate sequence values.
• Uniqueness of the sequence value is guaranteed only if the method is executed within a transaction.
However, to improve concurrency and avoid deadlock, it is best if this operation is the only one in the transaction. If the transaction contains operations other than invoking the SequenceValue method, invoke the method as close to the end of the transaction as possible.