SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Ensuring unique primary keys

Filling and replenishing the key pool

Every time a user adds a new customer, their pool of available primary keys is depleted by one. The primary key pool table needs to be periodically replenished at the consolidated database using a procedure such as the following:

CREATE PROCEDURE ReplenishPool AS
BEGIN
   DECLARE @CurrTable  VARCHAR(40)
   DECLARE @MaxValue   INTEGER
   DECLARE EachTable   CURSOR FOR
            SELECT table_name, max(value)
            FROM KeyPool
            GROUP BY table_name
   DECLARE @CurrLoc    VARCHAR(6)
   DECLARE @NumValues  INTEGER
   DECLARE EachLoc       CURSOR FOR
            SELECT location, count(*)
            FROM KeyPool
            WHERE table_name = @CurrTable
            GROUP BY location

 OPEN EachTable
   WHILE 1=1 BEGIN
      FETCH EachTable INTO @CurrTable, @MaxValue
      IF @@sqlstatus != 0 BREAK
      OPEN EachLoc
      WHILE 1=1 BEGIN
         FETCH EachLoc INTO @CurrLoc, @NumValues
         IF @@sqlstatus != 0 BREAK
         -- make sure there are 10 values
         WHILE @NumValues < 10 BEGIN
            SELECT @MaxValue = @MaxValue + 1
            SELECT @NumValues = @NumValues + 1
            INSERT INTO KeyPool
                   (table_name, location, value)
            VALUES (@CurrTable, @CurrLoc, @MaxValue)
         END
      END
      CLOSE EachLoc
   END
   CLOSE EachTable
END
go

This procedure fills the pool for each user up to ten values. You may wish to use a larger value in a production environment. The value you need depends on how often users are inserting rows into the tables in the database.

The ReplenishPool procedure must be run periodically at the consolidated database to refill the pool of primary key values in the KeyPool table.

The ReplenishPool procedure requires at least one primary key value to exist for each subscriber, so that it can find the maximum value and add one to generate the next set. To initially fill the pool you can insert a single value for each user, and then call ReplenishPool to fill up the rest. The following example illustrates this for three remote users and a single consolidated user named Office:

INSERT INTO KeyPool VALUES( 'Customer', 40, 'rep1' )
INSERT INTO KeyPool VALUES( 'Customer', 41, 'rep2' )
INSERT INTO KeyPool VALUES( 'Customer', 42, 'rep3' )
INSERT INTO KeyPool VALUES( 'Customer', 43, 'Office')
EXEC ReplenishPool
go