7. Sequence Replication

Altibase only supports replication of table objects by default. Thus, sequence replication means to create tables only for sequence replication, not copying the sequence itself.

This chapter will cover the conditions and methods supporting the sequence replication.

Overview

Altibase sequence replication is a feature allowing the remote and local server to use an identical sequence even in the situation of fail-over. Thus, the same sequence and program sources can be used in an application.

Sequence replication should replicate the cache start value so that the sequence values are not duplicated on the two servers. The sequence equivalent to the cache size is stored in the memory to use, and if the stored sequence is used all, the cache size sequence is stored again in the memory.

The tables for sequence replication is internally created because Altibase replication only supports tables.

Usage Condition

It is required to configure the following property as shown below. please refer to Replication Reference and General Reference for more detailed information on this property.

REPLICATION_TIMESTAMP_RESOLUTION=1

The sequence option for the local and remote servers should be identical. Please refer to SQL Reference for more detailed information on the sequence option.

START WITH
INCREMENT BY
MAXVALUE
MINVALUE
CACHE
FLUSH CACHE
CYCLE

Syntax

The major syntax for the sequence replication is as follows. Please refer to SQL Reference and Replication Reference for more detailed information.

Creating Sequence for Sequence Replication

If ENABLE SYNC TABLE option is specified, seq_name$ table for sequence replication is created. It is recommended to set cache size to 100 or more than 100.

CREATE SEQUENCE user_name.seq_name START WITH 1 CACHE 100 ENABLE SYNC TABLE;

Creating Sequence Replication

The following syntax creates a replication object so that a table for sequence replication can be duplicated.

CREATE REPLICATION repl_name WITH 'remote_host_ip', remote_host_port_no FROM user_name.seq_name$seq TO user_name.seq_name$seq; 

Startup and Shutdown of Sequence Replication

The following syntax starts or shuts down the sequence replication.

ALTER REPLICATION repl_name START;
ALTER REPLICATION repl_name STOP;

Remove of Sequence Replication

After executing a query dropping a replication (DROP REPLICATION) or excluding a table from the replication target (ALTER TABLE table_name DROP COLUMN), the seq_name$seq table is removed.

ALTER SEQUENCE user_name.seq_name DISABLE SYNC TABLE; 

Cautions

• It is recommended to perform sequence replication in the active-standby environment. The sequence value can be duplicated due to the replication gap between servers if it is performed in the active-active environment.

• The larger the size of the sequence cache, the faster the sequence creation. The cache size should be modified in advance since it cannot be modified after the table the for sequence replication is created.

  ALTER SEQUENCE username.seq_name CACHE 100;
  ALTER SEQUENCE user1.seq1 ENABLE SYNC TABLE;
  

• The sequence replication with tables is not recommended. If a fail-over occurs in the situation of sequence replication delay caused by a table replication delay, sequence duplicate key error might occur.

• If the fail-over occurs, there would be blanks among key values since the remote server start from the next cache values when referencing the sequence.

• Replication recreation, sequence recreation, and modification should be equivalently applied to all servers.

Example

The following example verifies the status of replication server when fail-over occurs during the execution of sequence replication.

A Server	B Server
iSQL> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 CACHE 1000 ENABLE SYNC TABLE; Create success.	iSQL> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 CACHE 1000 ENABLE SYNC TABLE; Create success.
iSQL> CREATE REPLICATION rep1 WITH '192.168.1.2', 20002 FROM SYS.seq1$seq TO SYS.SEQ1$seq; Create success.	iSQL> CREATE REPLICATION rep1 WITH '192.168.1.1', 20001 FROM SYS.seq1$seq TO SYS.SEQ1$seq; Create success.
iSQL> ALTER REPLICATION rep1 START; Alter success.
iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ---------------------- 1 1 row selected.	iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ----------------------- 1 1 row selected.
iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 1 1 row selected.
iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ---------------------- 1001 1 row selected.	iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ---------------------- 1001 1 row selected.
iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 2 1 row selected. iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 3 1 row selected.
Fail-Over Occurrence
	iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 1001 1 row selected. iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 1002 1 row selected. iSQL> SELECT seq1.NEXTVAL FROM DUAL; SEQ1.NEXTVAL ---------------------- 1003 1 row selected.
iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ------------------- 2001 1 row selected.	iSQL> SELECT LAST_SYNC_SEQ FROM seq1$seq; LAST_SYNC_SEQ ---------------------- 2001 1 row selected.