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()
BEGIN
   FOR EachTable AS TableCursor
   CURSOR FOR
      SELECT table_name
      AS CurrTable, max(value) as MaxValue
      FROM KeyPool
      GROUP BY table_name
   DO
      FOR EachRep AS RepCursor
      CURSOR FOR
         SELECT location
         AS CurrRep, COUNT(*) AS NumValues
         FROM KeyPool
         WHERE table_name = CurrTable
         GROUP BY location
      DO
         // make sure there are 100 values.
         // Fit the top-up value to your
         // requirements
         WHILE NumValues < 100 LOOP
            SET MaxValue = MaxValue + 1;
            SET NumValues = NumValues + 1;
            INSERT INTO KeyPool
            (table_name, location, value)
            VALUES
            (CurrTable, CurrRep, MaxValue);
         END LOOP;
      END FOR;
   END FOR;
END;

This procedure fills the pool for each user up to 100 values. 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( 'Customers', 40, 'user1' );
INSERT INTO KeyPool VALUES( 'Customers', 41, 'user2' );
INSERT INTO KeyPool VALUES( 'Customers', 42, 'user3' );
INSERT INTO KeyPool VALUES( 'Customers', 43, 'Office');
CALL ReplenishPool();

Do not use a trigger to replenish the key pool

You cannot use a trigger to replenish the key pool, as trigger actions are not replicated.

Send feedback about this page via email or DocCommentXchange