Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Remote » Creating SQL Remote systems » Duplicate primary key errors » Primary key pools


Filling and replenishing the key pool

Every time a remote user adds a new customer, the remote user's pool of available primary keys is depleted by one. Periodically, you need to replenish the contents of the primary key pool table on the consolidated database and then replicate the new primary keys to the remote databases.


The primary key pool table must already have been created.

  1. On the consolidated database, create a procedure to fill the primary key pool.


    You cannot use a trigger to replenish the key pool, as trigger actions are not replicated.

    For example:

    CREATE PROCEDURE ReplenishPool()
       FOR EachTable AS TableCursor
          SELECT table_name
          AS CurrTable, max(value) as MaxValue
          FROM KeyPool
          GROUP BY table_name
          FOR EachRep AS RepCursor
          CURSOR FOR
             SELECT location
             AS CurrRep, COUNT(*) AS NumValues
             FROM KeyPool
             WHERE table_name = CurrTable
             GROUP BY location
             // 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)
                (CurrTable, CurrRep, MaxValue);
             END LOOP;
          END FOR;
       END FOR;
  2. Insert an initial primary key value in the primary key pool for each user.

    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( 'Customers', 40, 'user1' );
    INSERT INTO KeyPool VALUES( 'Customers', 41, 'user2' );
    INSERT INTO KeyPool VALUES( 'Customers', 42, 'user3' );
    INSERT INTO KeyPool VALUES( 'Customers', 43, 'Office');
    CALL ReplenishPool();

    The ReplenishPool 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.

  3. Periodically run ReplenishPool.

    The ReplenishPool procedure must be run periodically on the consolidated database to refill the pool of primary key values in the key pool table.


The primary key pool table on the consolidated database is replenished and the new primary keys are replicated to the remote databases.