Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

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?

Information

 
Article Number000064859
EnvironmentProduct: 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/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).
  1. 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
  1. 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
 
  1. 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
  1. Reload the .df 
Workaround
Notes
Attachment 
Last Modified Date12/2/2015 1:34 PM