Setting unique database identification numbers when extracting databases

If you use the Extraction utility (dbxtract) or the Extract Database Wizard to create your remote databases, you can write a stored procedure to automate the task of setting unique database identification numbers.

To automate setting unique database identification numbers
  1. Create a stored procedure named sp_hook_dbxtract_begin.

    For example, to extract a database for remote user user2 with a user_id of 101, execute the following statements:

    SET OPTION "PUBLIC"."global_database_id" = '1';
    CREATE TABLE extract_id (next_id INTEGER NOT NULL) ;
    INSERT INTO extract_id VALUES( 1 );
    CREATE PROCEDURE sp_hook_dbxtract_begin
    AS
        DECLARE @next_id  INTEGER
        UPDATE extract_id SET next_id = next_id + 1000
        SELECT @next_id = (next_id )
        FROM extract_id
        COMMIT
        UPDATE #hook_dict
        SET VALUE = @next_id
        WHERE NAME = 'extracted_db_global_id';

    Each extracted or re-extracted database gets a different global_database_id. The first starts at 1001, the next at 2001, and so on.

  2. Run the Extraction utility (dbxtract) with the -v option or the Extract Database Wizard to extract your remote databases. The Extraction utility does the following tasks:

    1. Creates a temporary table name #hook_dict, with the following contents:

      name value

      extracted_db_global_id

      user ID being extracted

      When you write a sp_hook_dbxtract_begin procedure to modify the value column of the row, that value is used as the global_database_id option of the extracted database, and marks the beginning of the range of primary key values for DEFAULT GLOBAL AUTOINCREMENT values.

      • When you do not define an sp_hook_dbxtract_begin procedure, the extracted database has a global_database_id set to 101.

      • When you define a sp_hook_dbxtract_begin procedure that does not modify any rows in the #hook_dict, then the global_database_id is still set to 101.

    2. Calls the sp_hook_dbxtract_begin.

    3. Outputs the following information to assist in debugging procedure hooks:

      • The procedure hooks found.

      • The contents of #hook_dict before the procedure hook is called.

      • The contents of #hook_dict after the procedure hook is called.

See also