Setting 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.

Prerequisites

Hook procedures can be created by any user with the MANAGE REPLICATION system privilege. However, to ensure that the hook can access the #hook_dict table, which is used to pass information in and out of hooks, hooks must meet one of the following requirements:

Be owned by a user with the SELECT ANY TABLE and UPDATE ANY TABLE system privileges.
Be defined using the SQL SECURITY INVOKER clause of the CREATE PROCEDURE statement.

Task

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 performs 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.

Results

The unique database identification numbers are set.

Setting database identification numbers when extracting databases

Task

See also