Filter
Top Products
Chapter 8. SQL Remote Design for Adaptive Server Enterprise
The subscription argument is the location identifier.
In some circumstances it makes sense to add the KeyPool table to an
existing publication and use the same argument to subscribe to each
publication. Here we keep the location and rep_key values distinct to
provide a more general solution.
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
177