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;
Thisprocedure fills the pool for each user up to 100 values. Thevalue you
needdepends on how often users are inserting rows into the tables in the
database.
TheReplenishPool procedure must be run periodically at the consolidated
databaseto refill the pool of primary key values in the KeyPool table.
TheReplenishPool procedure requires at least one primary key value to
existfor each subscriber, so that it can find the maximum value and add one
togenerate the next set. Toinitially fill the pool you can insert a single value
foreach user, and then call ReplenishPool to fill up the rest. The following
exampleillustrates this for three remote users and a single consolidated user
namedOffice:
INSERT INTO KeyPool VALUES( ’Customer’, 40, ’user1’ );
INSERT INTO KeyPool VALUES( ’Customer’, 41, ’user2’ );
INSERT INTO KeyPool VALUES( ’Customer’, 42, ’user3’ );
INSERT INTO KeyPool VALUES( ’Customer’, 43, ’Office’);
CALL ReplenishPool();
136