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.
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:
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 © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|