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.
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.
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:
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.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|