If you use the extraction utility to create your remote databases, you can write a stored procedure to automate the task. If you create a stored procedure named sp_hook_dbxtract_begin, it is called automatically by the extraction utility. Before the procedure is called, the extraction utility creates a temporary table named #hook_dict, with the following contents:

If you write your 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 GLOBAL DEFAULT AUTOINCREMENT values.

Example

Consider extracting a database for remote user user2 with a user_id of 101. If you do not define an sp_hook_dbxtract_begin procedure, the extracted database will have global_database_id set to 101.

If you define a sp_hook_dbxtract_begin procedure, but it does not modify any rows in the #hook_dict then the option will still be set to 101.

If you set up the database as follows:

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';

Then each extracted or re-extracted database will get a different global_database_id. The first starts at 1001, the next at 2001, and so on.

To assist in debugging procedure hooks, dbxtract outputs the following when it is set to operate in verbose mode: