In Adaptive Server Anywhere, you can set the default column value to be GLOBAL AUTOINCREMENT. You can use this default for any column in which you want to maintain unique values, but it is particularly useful for primary keys. This feature is intended to simplify the task of generating unique values in setups where data is being replicated among multiple databases, typically by MobiLink synchronization.
When you specify default global autoincrement, the domain of values for that column is partitioned. Each partition contains the same number of values. For example, if you set the partition size for an integer column in a database to 1000, one partition extends from 1001 to 2000, the next from 2001 to 3000, and so on.
You assign each copy of the database a unique global database identification number. Adaptive Server Anywhere supplies default values in a database only from the partition uniquely identified by that database’s number.
For example, if you assigned the database in the above example the identity number 10, the default values in that database would be chosen in the range 10001–11000. Another copy of the database, assigned the identification number 11, would supply default value for the same column in the range 11001–12000.
Declaring default global autoincrement
You can set default values in your database by selecting the column properties in Sybase Central, or by including the DEFAULT GLOBAL AUTOINCREMENT phrase in a TABLE or ALTER TABLE statement.
Optionally, the partition size can be specified in parentheses immediately following the AUTOINCREMENT keyword. The partition size may be any positive integer, although the partition size is generally chosen so that the supply of numbers within any one partition will rarely, if ever, be exhausted.
For columns of type INT or UNSIGNED INT, the default partition size is 216= 65536; for columns of other types the default partition size is 232= 4294967296. Since these defaults may be inappropriate, especially if our column is not of type INT or BIGINT, it is best to specify the partition size explicitly.
For example, the following statement creates a simple table with two columns: an integer that holds a customer identification number and a character string that holds the customer’s name.
CREATE TABLE customer (
id INT DEFAULT GLOBAL AUTOINCREMENT (5000), name VARCHAR(128) NOT NULL,
PRIMARY KEY (id) )
In the above example, the chosen partition size is 5000.
☞
For more information on GLOBAL AUTOINCREMENT, see“CREATE TABLE statement”[ASA SQL Reference, page 407]. Setting the Global_database_id value
When deploying an application, you must assign a different identification number to each database. You can accomplish the task of creating and distributing the identification numbers by a variety of means. One method is to place the values in a table and download the correct row to each database based on some other unique property, such as user name.
❖ To set the global database identification number
1. You set the identification number of a database by setting the value of the public option Global_database_id. The identification number must be a non-negative integer.
For example, the following statement sets the database identification number to 20.
SET OPTION PUBLIC.Global_database_id = 20
If the partition size for a particular column is 5000, default values for this database are selected from the range 100001–105000.
Setting unique database identification numbers when extracting databases
If you use the extraction utility to create your remote databases, you can write a stored procedure to automate the task. If you create a stored procedure named sp_hook_dbxtract_begin, it is called automatically by the extraction utility. Before the procedure is called, the extraction utility creates a temporary table named #hook_dict, with the following contents:
name value
extracted_db_global_id user ID being extracted
If you write your 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 GLOBAL DEFAULT AUTOINCREMENT values.
Example Consider extracting a database for remote user user2 with a user_id of 101.
If you do not define an sp_hook_dbxtract_begin procedure, the extracted database will have Global_database_id set to 101.
If you define a sp_hook_dbxtract_begin procedure, but it does not modify any rows in the #hook_dict then the option will still be set to 101.
If you set up the database as follows:
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’
Then each extracted or re-extracted database will get a different
Global_database_id. The first starts at 1001, the next at 2001, and so on.
To assist in debugging procedure hooks,dbxtractoutputs the following when it is set to operate in verbose mode:
♦ 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.
How default values are chosen
The public option Global_database_id in each database must be set to a unique, non-negative integer. The range of default values for a particular database ispn+ 1 top(n+ 1), wherepis the partition size andnis the value of the public option Global_database_id. For example, if the partition size is 1000 and Global_database_id is set to 3, then the range is from 3001 to 4000.
If Global_database_id is set to a non-negative integer, Adaptive Server Anywhere chooses default values by applying the following rules:
♦ If the column contains no values in the current partition, the first default value ispn+ 1.
♦ If the column contains values in the current partition, but all are less than p(n + 1), the next default value will be one greater than the previous maximum value in this range.
♦ Default column values are not affect by values in the column outside of the current partition; that is, by numbers less thanpn+ 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink synchronization.
If the public option Global_database_id is set to the default value of 2147483647, a null value is inserted into the column. Should null values not be permitted, the attempt to insert the row causes an error. This situation arises, for example, if the column is contained in the table’s primary key.
Because the public option Global_database_id cannot be set to negative values, the values chosen are always positive. The maximum identification number is restricted only by the column data type and the partition size.
Null default values are also generated when the supply of values within the partition has been exhausted. In this case, a new value of
Global_database_id should be assigned to the database to allow default values to be chosen from another partition. Attempting to insert the null value causes an error if the column does not permit nulls. To detect that the supply of unused values is low and handle this condition, create an event of type GlobalAutoincrement.
Should the values in a particular partition become exhausted, you can assign a new database id to that database. You can assign new database id numbers in any convenient manner. However, one possible technique is to maintain a pool of unused database id values. This pool is maintained in the same manner as a pool of primary keys.
You can set an event handler to automatically notify the database
administrator (or carry out some other action) when the partition is nearly exhausted. For more information, see“Defining trigger conditions for events”[ASA Database Administration Guide, page 308].