Sybase DC38133-01-0902-01 manual 136, Cursor for

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