User's Manual

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( ’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