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.

Automate setting unique database identification numbers

Create a stored procedure named sp_hook_dbxtract_begin.

For example, to extract a database for remote user user2 with a user_id of 1001, 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.

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:

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.

Calls the sp_hook_dbxtract_begin.
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.

Unique database identification numbers when extracting databases

Automate setting unique database identification numbers

See also