Skip to content
≡

Administrator

Developer

Tools

External Tools

2. altiComp#

This chapter describes the altiComp utility and its features such as consistency control.

Introducing altiComp#

The altiComp utility monitors the progress of replication between two Altibase databases and resolves data inconsistencies that arise during the course of replication.

altiComp compares Altibase with another HDB on a table-by-table basis, and outputs information about any inconsistencies it finds. It also has a feature for synchronizing two databases in the event of data inconsistencies.

altiComp Terms#

Master Server#

Master Server is a server to be corrected when a discrepancy record is found between two servers. When running altiComp, either server can be designated as master.

Master DB#

The database on the master server.

Slave Server#

When the discrepancy record between two servers is found, Slave Server is the server to be corrected according to the reference database. When running altiComp, either server can be designated as a slave.

Slave DB#

The database on the slave server.

Different Records#

Different records are records whose column values do not match based on the primary key between the Master DB and Slave DB.

There are three types of differences:

MOSX difference#

When a record based on a primary key can be found in the Master DB but not in the Slave DB.

MOSO difference#

When a record based on a primary key can be found in both the master and slave tables but the record contents are different.

MXSO difference#

When a record based on a primary key can be found in the Slave DB but not in the Master DB.

Synchronization Policy#

A synchronization policy is a policy that specifies how to synchronize different records. alticomp usually treats the Master DB as the reference DB and synchronizes the Slave DB with it.

Altibase provides four synchronization policies:

SU Policy#

This policy resolves MOSO differences by updating the Slave DB with the contents of the Master DB.

SI Policy#

This policy resolves MOSX differences by inserting records from the Master DB into the Slave DB.

MI Policy#

This policy resolves MXSO differences by inserting records from the Slave DB into the Master DB.

SD Policy#

This policy resolves MXSO differences by deleting records from the Slave DB.

The SU policy, SI policy, MI policy, and SD policy are set in the altiComp environment file. Note that the MI policy and the SD policy are mutually exclusive, meaning that they cannot both be enabled at the same time.

DIFF#

Creates an execution result file that identifies inconsistent records found during replication between the Master DB and the Slave DB.

SYNC#

Identifies inconsistent records between the Master DB and the Slave DB, bidirectionally resolves inconsistencies according to the synchronization policy set in the altiComp environment file, and creates an execution result file including execution summary information and error information.

altiComp Environment File#

An environment file is for setting options for altiComp. This file includes conneciton information, altiComp function settings, synchronization policies, etc.


How to Use altiComp#

This chapter describes the altiComp environment file that contains information for running altiComp, and explains the DIFF and SYNC features.

How to Execute altiComp#

To use altiComp, an altiComp environment file, which contains information about the table(s) on which DIFF or SYNC is to be executed, must first be created. The altiComp environment file will be explained in the How to Use altiComp section.

altiComp commands have the following form: 

$ altiComp -f script_file_name
  • script_file_name : File name including the path of the environment file

If the current directory is: /user/charlie/altibase_home/altiComp:

/user/charlie/altibase_home/altiComp$ altiComp script_file_name

Or

/user/charlie/altibase_home/altiComp$ altiComp ./script_file_name

How to Set altiComp Properties#

Each comparison and synchronization task that is described in the environment file, has its own unique properties. The properties provide information necessary for running altiComp (Please refer to the sample.cfg file in the ALTIBASE_HOME/altiComp directory).

Rules for Setting Properties#

Properties follow the format property name = property value and are case-insensitive.

The following symbols have special meanings when used in the environment file:

  • " # " (sharp) indicates a comment and causes the remainder of the line to be ignored.
  • " { } " (curly braces) used to indicate that a property value spans multiple lines.
  • " ; " (semicolon) serves as a delimiter to separate multiple values.
  • " " " (double quotation marks) are used to enclose a string (such as a user name, password, table name, or column name) that includes one or more reserved words or special characters.
  • In Altibase, the following are special characters: ~, !, \@, #, $, %, ^, &, *, (, ), _, +, |

Property Names#

A property name consists of characters other than spaces, and identifies a property within a group.

Property Values#

A property can take a single value, multiple values, or an expression.

  • An expression may include blanks. Most properties follow this format:

    Example: TABLE = EMPLOYEE

  • Multiple values consist of several values separated by the ";" delimiter, and must be contained within "{}" if they occupy more than one line. The "EXCLUDE" group allows multiple values.

    Examples:
    - EXCLUDE = ENO; DNO; ENAME - EXCLUDE = {ENO; DNO; ENAME}

  • Expressions are character strings, can include spaces, and must be enclosed within "{}". The "WHERE" property is an expression.

    Example: WHERE = { ENO > '1000' and ENO < '2000' }

Data Type Support#

The EXCLUDE property is used as follows to exclude a particular column or columns from altiComp targets.

Example: Exclude a certain column from altiComp targets, if a CLOB column exists in the EMP table. 

TABLE = EMP
EXCLUDE = { CCC }

Property Options#

Use the following properties to specify information for accessing the local and remote servers, comparison (DIFF) and synchronization (SYNC) tasks, and synchronization policies for inconsistent records.

DB_MASTER#

This is used to set the server whose contents are to be accepted as correct if inconsistent records are found between two servers.

Sets the user name and password, the name or IP address of the server, and NLS_USE. The property values must match the information in the property file in the home directory of Altibase.

  • TCP Connection:

    DB_MASTER = altibase://sys:manager@DSN=192.188.1.1;PORT_NO=20300;NLS_USE=US7ASCII

  • SSL Connection:

    DB_MASTER = altibase://sys:manager@DSN=192.188.1.1;PORT_NO=${ALTIBASE_SSL_PORT_NO};NLS_USE=US7ASCII;CONNTYPE=6;SSL_CA=/home/    altibase/cert/ca-cert.pem;SSL_CERT=/home/altibase/cert/client-ert.pem;SSL_KEY=/home/altibase/cert/client-key.pem
    • For more detailed information about connection string attributes for SSL, please refer to the SSL/TLS User's Guide.

DB_SLAVE#

This is used to set the other server.

This sets the user name and password, the name or IP address of the server, and NLS_USE. The property values must match the information in the property file in the home directory of Altibase.

OPERATION#

This is set to "DIFF" for a comparison task, or to "SYNC" for a synchronization task.

INSERT_TO_SLAVE#

This sets the SI policy used to resolve MOSX inconsistencies. Specifies whether to insert the record in question into the Slave DB. The property value is set to "ON" to specify that the record is to be inserted, and "OFF" to specify that it is not to be inserted.

INSERT_TO_MASTER#

This sets the MI policy used to resolve MXSO inconsistencies. Specifies whether to insert the 52 Utilities Manual record in question into the Master DB. The property value is set to "ON" to specify that the record is to be inserted, and "OFF" to specify that it is not to be inserted.

This property and DELETE_IN_SLAVE cannot both be set to "ON" simultaneously.

DELETE_IN_SLAVE#

Sets the SD policy used to resolve MXSO inconsistencies. Specifies whether to delete the record in question from the Slave DB. The property value is set to "ON" to specify that the record is to be deleted, and "OFF" to specify that it is not to be deleted.

This property and INSERT_TO_MASTER cannot both be set to "ON" simultaneously.

UPDATE_TO_SLAVE#

This sets the SU policy used to resolve MOSO inconsistencies. Specifies whether to update the record in question in the Slave DB. The property value is set to "ON" to specify that the record is to be changed, and "OFF" to specify that it is not to be changed.

CHECK_INTERVAL#

This sets the interval between the completion of a SYNC operation on a table and the start of a SYNC operation on the next table. Expressed in units of ms (milliseconds).

MAX_THREAD#

This specifies the maximum number of threads that can run concurrently. Set to -1 to specify an unlimited number of threads.

COUNT_TO_COMMIT#

This specifies the number of changed data(INSERT, DELETE or UPDATE) to be committed at once. The default value is 1000.

FILE_MODE_MAX_ARRAY#

If its value is greater than 1, altiComp writes the fetched data to a file and then starts a SYNC or DIFF operation on the file. This value is used to set the maximum size of array(s) for fetching data. altiComp fetches a number of records equal to this value and writes them to a csv file.

This option can be used to realize better performance. However, when a target table has many LOB type columns, this option may not improve performance.

This option can only be used between Altibases.

Example: FILE_MODE_MAX_ARRAY = 1000

TABLES Group#

This defines information related to target table(s). The number of descriptions in the group must equal the number of target tables, and the name of each group must correspond to the name of a table in the Master DB.

The following properties can be set:

WHERE#

Sets conditions for selecting table records. This property is described in the same way as a WHERE clause of a SQL statement. Multiple values are permitted, but the ";" delimiter cannot be used to specify multiple values. Moreover, this property cannot be commented.

This applies to the comparison (DIFF) and synchronization (SYNC) functions.

EXCLUDE#

This sets conditions for the projection of table records. The property may have multiple values. The specified columns are excluded from comparison and synchronization operations.

With the proper combination of WHERE and EXCLUDE, the result of combining selection and projection altiComp can be used.

TABLE#

This sets the Slave DB table name. In cases where the table names on the Master DB and the Slave DB differ from each other, this must be explicitly described in order to use the comparison (DIFF) and synchronization (SYNC) functions. if omitted, it is assumed that the table name on the Slave DB is the same as that on the Master DB.

The table name can contain Roman alphabetic characters, numbers, and the following special characters: ((space,~, !, \@, #, $, %, ^, &, *, (, ), _, +, |)

However, it cannot contain Korean characters.

SCHEMA#

This specifies the table schema in the Slave database

If the schema name of the connecting user of Slave is different from the schema of the target table, it must be described. If omitted, the schema of the connecting user of Slave is used.


Comparison (DIFF) Function#

This function identifies different records that are found during replication between the Master and Slave databases, and creates an execution result file.

Environment File#

In the altiComp environment file, set the OPERATION property to "DIFF".

All execution option properties must be specified, and the table group properties WHERE, EXCLUDE, TABLE, and SCHEMA can be optionally specified.

Execution#

The comparison (DIFF) function is executed as follows:

$ altiComp -f script_file_name
  • script_file_name: File name including the path of the environment file

Execution Results#

This function compares the contents of the Master and Slave Databases with each execution log file and table, and creates an execution result file that includes the contents of inconsistent columns of inconsistent records.

For example, if the following altiComp command is running successfully, /user/charlie/altibase_home/altiComp$ altiComp sample.cfg a "mastertable-username.slavetable.log" file is created for each table in the altiComp directory, alongside sample.log.

Execution Log File#

This file is created as "script_file_name.log" and displays the contents of the executed environment file with a summary of the comparison (DIFF) task for each table in the TABLES group.

The contents of the environment file are displayed as follows:

INFO[ MNG ] Tread # 0 init is OK!
INFO[ MNG ] Tread # 0 start is OK!

[TAB_2->TAB_2]
Fetch Rec In Master: 3
Fetch Rec In Slave : 2
MOSX = DF, Count : 1
MXSO = DF, Count : 0
MOSO = DF, Count : 1
MOSO = EQ, Count : 1

SCAN TPS:   20547.95
Time:       0.00 sec

Execution Result File#

This file is created as master table-username.slave table.log and displays the comparison results in the following format. 

DF[m,n]-> COL_N (Vn_M, Vn_S):PK->{ PCOL_V }
  • DF : The type of inconsistency (MOSX, MOSO, MXSO)
  • m : The record number on the Master server
  • n : The record number on the Slave server
  • COL_N : The name of the first column that has different values after comparison
  • Vn_M : The value in the corresponding column on the Master server
  • Vn_S : The value in the corresponding column on the Slave Server

However, for records that have LOB type columns, the LOB column value is not output.

Comparison (DIFF) Examples#

The following examples compare the EMP table of host1 with the EMPLOYEES table of host2, and the DEPARTMENTS table of host1 and the DEPARTMENTS table of host2.

DIFF Example 1#

Specify DB_MASTER as host1 and DB_SLAVE as host2. The environment file for comparing all the records in each table should look like this:

DB_MASTER = "altibase://sys:manager@DSN=host1;PORT_NO=10111;NLS_USE=US7ASCII"
DB_SLAVE = "altibase://sys:manager@DSN=host2;PORT_NO=20111;NLS_USE=US7ASCII"
OPERATION = DIFF
MAX_THREAD = -1

DELETE_IN_SLAVE = ON
INSERT_TO_SLAVE = ON
INSERT_TO_MASTER = ON
UPDATE_TO_SLAVE = ON

LOG_DIR = "./"
LOG_FILE = "sample.log"

[EMP]
TABLE = EMPLOYEES
SCHEMA = SYS 

[DEPARTMENTS]
TABLE = DEPARTMENTS
SCHEMA = SYS

The name of the target table for the Master Server (host1) and Slave Server (host2) may differ, as shown in the above example.

DIFF Example 2#

From the EMP table, select the values to be compared according to the ENO column, and exclude the JOIN_DATE and GENDER columns as below.

The CONDITION property specifies that the EMP records to be compared as limited to "ENO >= 1 and ENO <= 20".

The EXCLUDE property specifies that the JOIN_DATE and GENDER columns are not to be compared.

In other words, if all columns (other than the JOIN_DATE and GENDER columns) are identical, it is assumed that the record is the same. 

[EMP]
TABLE = EMPLOYEES
WHERE = {ENO >= 1 and ENO <= 20}
EXCLUDE = {JOIN_DATE; SEX}
[DEPARTMENTS]

DIFF Example 3#

From the EMP table, select the values to be compared according to the ENO and JOIN_DATE columns, and exclude the GENDER column as below.

[EMP]
TABLE = EMPLOYEES
WHERE = {(ENO >= 1 and ENO <= 20) or (JOIN_DATE >= '20001010')}
EXCLUDE = {SEX}

[DEPARTMENTS]

The WHERE property determines whether the EMP records for comparison are to be limited by "ENO >= 1 and ENO <= 20" or "JOIN_DATE >= 20001010". The EXCLUDE property specifies that the GENDER column is not to be compared.


Synchronization (SYNC) Function#

This function identifies records that are inconsistent between the Master and Slave databases, bidirectionally resolves the differences according to the synchronization policy in the altiComp configuration file, and creates an execution result file including execution summary information and error information.

Environment File#

In the altiComp environment file, set the OPERATION property to "SYNC".

All execution option properties must be described, and the table group properties WHERE, EXCLUDE, TABLE, and SCHEMA can be optionally specified.

Execution#

The synchronization (SYNC) function is executed as follows:

$ altiComp -f script_file_name
  • script_file_name: File name including the path of the environment file

Execution Results#

This function compares the contents of the Master and Slave databases with each execution log file and table, and creates an execution result file that consists of information about synchronization tasks conducted on different records and an error log that includes information about errors which occurred during synchronization.

Execution Log File#

This file is created as script_file_name.log and displays the contents of the executed environment file as well as the summary of the synchronization (SYNC) task for the table(s) in each TABLES group.

The contents of the environment file are written to the log file as follows:

INFO[ MNG ] Tread # 0 init is OK!
INFO[ MNG ] Tread # 0 start is OK!

[TAB_2->TAB_2]
Fetch Rec In Master: 3
Fetch Rec In Slave : 2
MOSX = -, SI 
MXSO = -, -
MOSO = -, SU
MXSX = -, -

-----------------------------------------
Operation  Type      MASTER           SLAVE
-----------------------------------------
INSERT     Try           0               1
           Fail          0               0

UPDATE     Try           X               1
           Fail          X               0

DELETE     Try           X               0
           Fail          X               0
-----------------------------------------
UPDATE     Try           0               2
           Fail          0               0
OOP  TPS:  13698.63
SCAN TPS:  20547.95
Time:       0.00 sec

If a failure occurs for any record, the cause of the error and the record contents are written to the log file.

Synchronization (SYNC) Examples#

The following examples show how to specify OPERATION and TABLE for the synchronization policy to resolve data inconsistency.

SYNC Example 1#

Insert an MOSX inconsistent record (a record that exists in the Master server, but not in the Slave server) into the Slave server, and ignore an MXSO inconsistent record ( a record that exists in the Slave server, but not in the Master server). 

Master Server = "altibase://sys:manager@DSN=host1;PORT_NO=10111;NLS_USE=US7ASCII"
Slave Server = "altibase://sys:manager@DSN=host2;PORT_NO=20111;NLS_USE=US7ASCII"
OPERATION = SYNC
MAX_THREAD = -1

DELETE_IN_SLAVE = OFF
INSERT_TO_SLAVE = ON
INSERT_TO_MASTER = OFF
UPDATE_TO_SLAVE = ON

LOG_DIR = "./"
LOG_FILE = "sample.log"

[EMP]
TABLE = EMPLOYEES
SCHEMA = SYS

[DEPARTMENTS]
TABLE = DEPARTMENTS
SCHEMA = SYS

The INSERT_TO_SLAVE property has been set to "ON" because the SI policy is required to resolve MOSX inconsistency. Likewise, the INSERT_TO_MASTER and DELETE_IN_SLAVE properties have been set to "OFF" because the MI and SD policies required to resolve MSXO inconsistency have been ignored.

SYNC Example 2#

Insert an MOSX inconsistent record (a record that exists in the Master Server, but not in the Slave server) into the Slave server, and an MSXO inconsistent record (a record that exists in the Slave server, but not in the Master server) into the Master server. 

Master Server = "altibase://sys:manager@DSN=host1;PORT_NO=10111;NLS_USE=US7ASCII"
Slave Server  = "altibase://sys:manager@DSN=host2;PORT_NO=20111;NLS_USE=US7ASCII"
OPERATION = SYNC
MAX_THREAD = -1

DELETE_IN_SLAVE = OFF
INSERT_TO_SLAVE = ON
INSERT_TO_MASTER = ON
UPDATE_TO_SLAVE = ON

LOG_DIR = "./"
LOG_FILE = "sample.log"

[EMP]
TABLE = EMPLOYEES
SCHEMA = SYS

[DEPARTMENTS]
TABLE = DEPARTMENTS
SCHEMA = SYS

The INSERT_TO_SLAVE property has been set to "ON" because the SI policy is required to resolve MOSX inconsistency. Likewise, the INSERT_TO_MASTER property has been set to "ON" because the MI policy is required to resolve MXSO inconsistency. However, the DELETE_IN_SLAVE property has been set to "OFF" because the SD policy is unnecessary.

SYNC Example 3#

Synchronize the Master and Slave servers. 

Master Server = "altibase://sys:manager@DSN=host1;PORT_NO=10111;NLS_USE=US7ASCII"
Slave Server  = "altibase://sys:manager@DSN=host2;PORT_NO=20111;NLS_USE=US7ASCII"
OPERATION = SYNC
MAX_THREAD = -1

DELETE_IN_SLAVE = ON
INSERT_TO_SLAVE = ON
INSERT_TO_MASTER = OFF
UPDATE_TO_SLAVE = ON

LOG_DIR = "./"
LOG_FILE = "sample.log"

[EMP]
TABLE = EMPLOYEES
SCHEMA = SYS

[DEPARTMENTS]
TABLE = DEPARTMENTS
SCHEMA = SYS

The SI and SD policies are necessary to synchronize the Master and Slave servers. Therefore, the INSERT_TO_SLAVE and DELETE_IN_SLAVE properties have been set to "ON".

SYNC Example 4#

Please refer to schema.sql in the $ALTIBASE_HOME/sample/APRE/schema directory.

Synchronize the EMPLOYEES table of the local server host1 with the EMPLOYEES table of the remote server host2 (delete 16 to 20 from the ENO column), and the DEPARTMENTS table of host1 with the DEPARTMENTS table of host2.

First, set up replication between the local and remote servers.

For the local server (IP: 192.168.1.11)

iSQL> CREATE REPLICATION rep1 WITH '127.0.0.1', 56342 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments;
Create Success.

For the remote server (IP: 127.0.0.1)

iSQL> CREATE REPLICATION rep1 WITH '192.168.1.11', 65432 FROM sys.employees TO sys.employees, FROM sys.departments TO sys.departments;
Create Success.

If the current directory is /user/charlie/altibase_home/altiComp:

$ vi sample.cfg
Master Server = "altibase://sys:manager@DSN=127.0.0.1;PORT_NO=20582;NLS_USE=US7ASCII"
Slave Server  = "altibase://sys:manager@DSN=192.168.1.11;PORT_NO=20582;NLS_USE=US7ASCII"

OPERATION  = SYNC
MAX_THREAD = -1

DELETE_IN_SLAVE = ON
INSERT_TO_SLAVE = ON
INSERT_TO_MASTER = OFF 
UPDATE_TO_SLAVE = ON

LOG_DIR = "./"
LOG_FILE = "sample.log"

[ EMPLOYEE S]
WHERE   = {ENO >= 1 and ENO <= 20}
TABLE   = EMPLOYEES 
SCHEMA  = SYS
[ DEPARTMENTS ]
TABLE   = DEPARTMENTS 
SCHEMA  = SYS

$ altiComp -f sample.cfg 
$ cat sample.log
INFO[ MNG ] Tread #  0 init is   OK!
INFO[ MNG ] Tread #  1 init is   OK!
INFO[ MNG ] Tread #  0 start is  OK!
INFO[ MNG ] Tread #  1 start is  OK!

[DEPARTMENTS->DEPARTMENTS]
Fetch Rec In Master: 5
Fetch Rec In Slave : 5
MOSX = NO
MXSO = NO
MOSO = SU

--------------------------------------------
Operation  Type      MASTER           SLAVE    
--------------------------------------------
INSERT     Try            0               0 
           Fail           0               0 

UPDATE     Try            X               0 
           Fail           X               0 

DELETE     Try            X               0 
           Fail           X               0
--------------------------------------------
UPDATE     Try            0               0 
           Fail           0               0 
OOP  TPS:       0.00
SCAN TPS:   60240.96
Time:       0.00 sec

[EMPLOYEES->EMPLOYEES]
Fetch Rec In Master: 20
Fetch Rec In Slave : 15
MOSX = NO
MXSO = NO
MOSO = SU

-------------------------------------------
Operation  Type      MASTER           SLAVE    
-------------------------------------------
INSERT     Try            0               5 
           Fail           0               0 

UPDATE     Try            X               0 
           Fail           X               0 

DELETE     Try            X               0 
           Fail           X               0
-------------------------------------------
UPDATE     Try            0               5 
           Fail           0               0 
OOP  TPS:     576.04
SCAN TPS:    2304.15
Time:       0.01 sec