How to update the sequence definition in the DataServer schema and the MS SQL database in order to use MS SQL native sequences instead of the revised sequence generator? - Progress Community

Loading

Skip to Main Content

How to update the sequence definition in the DataServer schema and the MS SQL database in order to use MS SQL native sequences instead of the revised sequence generator?

The following describes how to update the sequence definition in the DataServer schema and the MS SQL database so they can use the MS SQL native sequences instead of the revised sequence generator

Dec 2, 2015
Knowledge

Title

How to update the sequence definition in the DataServer schema and the MS SQL database in order to use MS SQL native sequences instead of the revised sequence generator?

URL Name

How-to-update-the-sequence-definition-in-the-DataServer-schema-and-the-MS-SQL-database-in-order-to-use-MS-SQL-native-sequences-instead-of-the-revised-sequence-generator

Article Number

000182577

Environment

Product: OpenEdge Version: 11.4, 11.5, 11.6 OS: Windows Database: MS SQL Server 2012

Question/Problem Description

How to migrate existing revised sequence generators with the native MS SQL sequences?

How to replace the revised sequence generator by MS SQL native sequence object?

How to update the sequence definition in the DataServer schema and the MS SQL database in order to use MS SQL native sequences instead of the revised sequence generator?

Steps to Reproduce

Clarifying Information

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 sequence tables.

However, the initial implementation of the sequence generator is sensitive to lock contention under moderate to high load, when the NEXT-VALUE is called multiple times in a short period.

The revised sequence generator was introduced in OpenEdge 10.2A onwards (and made the default option in OpenEdge 11.1 for any new proToMSS migration) to address those contention issues and unreliability for the NEXT sequence value by reading the sequence as part of a record write. Yet, it appeared that under very high load (the NEXT-VALUE funtion being called multiple times per second), contention would still occur and errors 2624, 14961, 2913 be returned to the user.

Starting with MS SQL Server 2012 and OpenEdge 11.4, the DataServer for MS SQL Server provides the ability to support OpenEdge sequences using sequences native to MS SQL Server, instead of sequence tables. Using the SQL Server native SEQUENCE object should eliminate contention and unreliability concerns experienced with the previous implementations of the sequence generator.

Error Message

Defect Number

Enhancement Number

Cause

Resolution

The following describes how to update the sequence definition in the DataServer schema and the MS SQL database so they can use the MS SQl native sequences instead of the revised sequence generator

For the explanations, nextcustnum will be used as the sequence name and sports2000db will be used as the MS SQL database name.

It is also assumed that you are using OpenEdge 11.4 or later, MS SQL Server 2012 or later and that the DataServer schema has been updated accordingly, has it been generated with a previous OpenEdge or MS SQL Server release.

1- Retrieve the current sequence value which will be reuse to initialize the native sequence. In an ABL session run:

MESSAGE NEXT-VALUE (nextcustnum).

Delete the revised sequence generator definitions on SQL Server

Sequence table for OpenEdge sequence nextcustnum:

DROP TABLE _SEQT_REV_nextcustnum

Table that keeps the properties of all the sequences definitions from the OpenEdge sequence:

DROP TABLE _SEQT_REV_nextcustnum _SEQT_REV_SEQTMGR

Stored procedure that gets the sequence value and passes it to the OpenEdge client:

DROP PROCEDURE _SEQP_REV_nextcustnum

Create the native sequence generator definitions on SQL Server

Native sequence object:

CREATE SEQUENCE nextcustnum START WITH <startValue> INCREMENT BY 1 MAXVALUE 9223372036854775807 MINVALUE 1 CYCLE

Note: Replace <startValue> by the value returned by "MESSAGE NEXT-VALUE (nextcustnum)" (see step 1), incremented by 1

Stored procedure that gets the sequence value and passes it to the OpenEdge client:

CREATE PROCEDURE _SEQP_NAT_SEQUENCE

   (
   @op INT ,
   @val BIGINT OUTPUT,
   @seqN VARCHAR(100)
   )
   AS
   BEGIN
   DECLARE @SeqSchema NVARCHAR(50)
   DECLARE @SeqDb NVARCHAR(50)
   DECLARE @tmp NVARCHAR(64)
   DECLARE @SeqName NVARCHAR(50)
   DECLARE @SQLQuery NVARCHAR(500)
   DECLARE @err int
   IF @seqN IS NOT NULL AND LEN(@seqN) < 1
   BEGIN
   SET @op = -1 /* Bad Sequence Name */
   RETURN
   END
   ELSE
   BEGIN
   SELECT @SQLQuery =
   N'SELECT @SeqDb = LEFT(' + char(39) + @seqN + char(39) + ', CHARINDEX(''.'' ,' + char(39)+ @seqN + char(39) + ')-1)';
   EXEC sp_executesql @SQLQuery, N'@SeqDb NVARCHAR(50) output', @SeqDb output;
   SELECT @SQLQuery =
   N'SELECT @tmp = RIGHT(' + char(39) + @seqN + char(39) + ', LEN (' + char(39) + @seqN + char(39) + ') - CHARINDEX(''.'' ,' + char(39)+ @seqN + char(39) + '))';
   EXEC sp_executesql @SQLQuery, N'@tmp NVARCHAR(64) output', @tmp output;
   SELECT @SQLQuery =
   N'SELECT @SeqSchema = LEFT(' + char(39) + @tmp + char(39) + ', CHARINDEX(''.'' ,' + char(39)+ @tmp + char(39) + ')-1)';
   EXEC sp_executesql @SQLQuery, N'@SeqSchema NVARCHAR(50) output', @SeqSchema output;
   SELECT @SQLQuery =
   N'SELECT @SeqName = RIGHT(' + char(39) + @seqN + char(39) + ', CHARINDEX(''.'', ' + ' REVERSE(' + char(39)+ @seqN + char(39) + '))-1)';
   EXEC sp_executesql @SQLQuery, N'@SeqName NVARCHAR(50) output', @SeqName output;
   END
   IF @op = 0 /* Current_Value function */
   BEGIN
   SELECT @SQLQuery = N'SELECT @val = CONVERT(bigint,current_value)
   FROM sys.schemas AS T1 INNER JOIN sys.sequences AS T2 ON (T1.schema_id = T2.schema_id)
   WHERE T2.NAME = ' + char(39) + @SeqName + char(39) + ' AND T1.NAME = ' + char(39) + @SeqSchema + char(39);
   EXEC sp_executesql @SQLQuery, N'@val bigint output', @val output;
   RETURN /* Success */
   END
   ELSE IF @op = 1 /* NEXT VALUE FOR function */
   BEGIN
   DECLARE @ParamDef NVARCHAR (512)
   DECLARE @cycle bit
   DECLARE @incval bigint
   DECLARE @maxval bigint
   DECLARE @minval bigint
   DECLARE @curval bigint
   DECLARE @strtval bigint
   SET @SQLQuery = N'SELECT @cycle = is_cycling,
   @incval = CONVERT(bigint,increment) ,
   @minval = CONVERT(bigint,minimum_value) ,
   @maxval = CONVERT(bigint,maximum_value) ,
   @curval = CONVERT(bigint,current_value),
   @strtval = CONVERT(bigint,start_value)
   from SYS.sequences where name = ' + char(39)+ @SeqName + char(39);
   SET @ParamDef = N'@cycle bit output, @incval bigint output, @minval bigint output,
   @maxval bigint output, @curval bigint output, @strtval bigint output'
   EXEC sp_executesql @SQLQuery, @ParamDef, @cycle output, @incval output, @minval output,
   @maxval output, @curval output, @strtval output;
   SET @err = @@error
   IF @err <> 0 goto Err
   IF (@incval > 0 and @maxval - @curval < @incval) or (@incval < 0 and @maxval + @curval > @incval)
   BEGIN
   IF @cycle = 0
   BEGIN
   SET @SQLQuery = N'SELECT @Val = CONVERT(bigint,current_value) from SYS.sequences where name = ' + char(39)+ @SeqName + char(39);
   EXEC sp_executesql @SQLQuery, N'@Val bigint output', @val output; RETURN /* Success */
   SET @err = @@error
   if @err <> 0 goto Err
   END
   ELSE
   BEGIN
   EXEC (' ALTER SEQUENCE ' + @SeqName + ' RESTART WITH ' + @strtval )
   EXEC('SELECT NEXT VALUE FOR ' + @SeqName)
   SET @err = @@error
   if @err <> 0 goto Err
   SET @SQLQuery = N'SELECT @Val = CONVERT(bigint,current_value) from SYS.sequences where name = ' + char(39)+ @SeqName + char(39);
   EXEC sp_executesql @SQLQuery, N'@val bigint output', @val output; RETURN /* Success */
   SET @err = @@error
   if @err <> 0 goto Err
   END
   END
   ELSE
   BEGIN
   SET @SQLQuery = 'SELECT @Val = NEXT VALUE FOR ' + @SeqName ;
   Execute sp_executesql @SQLQuery,N'@val bigint output', @val output
   SET @err = @@error
   IF @err <> 0 goto Err
   RETURN
   END
   END
   ELSE IF @op = 2 /* Set-Value function */
   BEGIN
   EXEC (' ALTER SEQUENCE ' + @SeqName + ' RESTART WITH ' + @val )
   EXEC('SELECT NEXT VALUE FOR ' + @SeqName)
   SET @err = @@error
   IF @err <> 0 goto Err
   RETURN
   END
   Err:
   return @err
   END

Dump the dataserver .df, update the FOREIGN-NAME field and add the SEQ-MISC4, SEQ-MISC5, SEQ-MISC6 to the sequence:

Replace:

ADD SEQUENCE "nextcustnum"
INITIAL 1
INCREMENT 1
CYCLE-ON-LIMIT yes
MIN-VAL 1
FOREIGN-NAME "_SEQT_REV_nextcustnum"
FOREIGN-OWNER "dbo"
SEQ-MISC3 dbo,_SEQP_REV_nextcustnum,@op,@val,

By:

ADD SEQUENCE "nextcustnum"

INITIAL 1
INCREMENT 1
CYCLE-ON-LIMIT yes
MIN-VAL 1
FOREIGN-NAME "nextcustnum"
FOREIGN-OWNER "dbo"
SEQ-MISC3 sports2000db,nextcustnum,@op,@val,dbo,
SEQ-MISC4 sports2000db
SEQ-MISC5 19,0
SEQ-MISC6 bigint

Reload the .df

Workaround

Notes

References to Other Documentation:

Progress Article(s):

"How the sequence is generated when "Use revised sequence Generator" is selected during the protoMSS process"
"Errors 2624, 14961, 2913 occur calling NEXT-VALUE function against DataServer for MS SQL Server"

Keyword Phrase

Last Modified Date

11/20/2020 7:26 AM

Drop Files

Upload FilesOr drop files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Loading

How to update the sequence definition in the DataServer schema and the MS SQL database in order to use MS SQL native sequences instead of the revised sequence generator? - Progress Community