Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Remote » Creating SQL Remote systems » Unique identification numbers for remote databases


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.


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.

  1. 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
        DECLARE @next_id  INTEGER
        UPDATE extract_id SET next_id = next_id + 1000
        SELECT @next_id = (next_id )
        FROM extract_id
        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.

  2. 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:

    1. Creates a temporary table name #hook_dict, with the following contents:

      name value


      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.

    2. Calls the sp_hook_dbxtract_begin.

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


The unique database identification numbers are set.

 See also