OpenEdge sequences

A sequence generator is a database object that provides incremental values within any integer range. (You can specify any positive or negative increment.) During an OpenEdge DB to MS SQL Server migration, the DataServer has traditionally simulated support for OpenEdge sequences using native MS SQL Server stored procedures and a sequence tables. Starting with MS SQL Server 2012 and OpenEdge 11.4, the DataServer for MS SQL Server began providing the ability to support OpenEdge sequences using sequences native to MS SQL Server. You cannot create sequences for MS SQL Server tables directly through the Data Dictionary.

The DataServer for MS SQL Server now offers a legecy, a revised legacy and a native version of the sequence generator. Each legacy sequence has one stored procedure and one table object associated with it whose names are prefixed with _SEQP_ and _SEQT_ respectively. The same objects in the new sequence generator are prefixed with SEQP_REV_ and SEQT_REV respectively and include a single common table object named SEQT_REV_SEQTMGR. When a sequence is migrated to MS SQL Server, the new sequence generator is used if the Use Revised Sequence generator option is checked in the migration dialog. If you remigrate with the same option turned off, the sequences will revert to the legacy model in the foreign database and the schema holder. If the "Try Native Sequences" option is checked, then, if the version of MS SQL Server supports native sequences, OpenEdge sequences will be instead supported by native sequences in MS SQL Server. If the version of MS SQL Server does not support native sequences, then the selections on the options described above will determine which traditional sequence generator will instead be used to support OpenEdge sequences during migration.Only one sequence generator is implemented for the DataServer at a given time. If you elect to use the generator and the legacy version already exists, the legacy version is replaced, and visa versa. It is also possible to change the sequence version using the delta.sql utility by setting or unsetting the option.

The sequence procedures all run in separate connections from your ABL transactions. In that connection, a different transaction is established to handle modifications to the sequence table rows when the legacy or revised sequence generator is used. With all the sequence requests made against the legacy sequence generator, if a sequence is being actively modified when another user attempts to access the sequence record, the second user must wait for a lock. Under heavy use, it is possible for a lock timeout to occur on a legacy or revised sequence generator. To avoid any probability of sequence timeouts, sequence retry logic in the DataServer attempts to handle concurrency automatically. In the remote instance of a sequence lock timeout that is not resolved internally, the user is given the option to continue to wait and retry or to cancel the retries and abandon the sequence request. Setting a non-zero value for the -Dsrv switch PRGRS_NATIVE_LOCKWAIT may further reduce any possibility of an unmanaged sequence timeout. For more information on setting the PRGRS_NATIVE_LOCKWAIT value, refer to Handling lock timeouts or DataServer options.

The revised version of the sequence generator eliminates lock contention when using the commonly utilized NEXT-VALUE sequence function. Lock contention is also greatly minimized for the GET-VALUE function. Lock contention for SET-VALUE function is not reduced and may not be a better choice over the legacy sequence generator for this function. If your particular application is atypical in the sense that it uses the SET-VALUE function frequently as compared to your use of NEXT-VALUE and GET-VALUE, then the previous version of the sequence generator may provide an advantage. The above lock contention that can occur for a legacy and revised sequence generator does not exist if OpenEdge sequences are migrated using MS SQL Server native sequences instead.

The bit size of the legacy database sequences can be either 32-bit INTEGER or 64-bit INT64. The generator was introduced after the DataServer had already transitioned to 64-bit values. Therefore all generators are 64-bit. The following section highlights changes regarding 64-bit sequences.