Sybase DC38133-01-0902-01 manual Filling and replenishing the key pool, 177

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