SQL Guide : solidDB® SQL statements : SET : SET SYNC
  
SET SYNC
The SET SYNC statements are used to set up and configure the databases used in an advanced replication system.
SET SYNC master_or_replica
SET SYNC master_or_replica yes_or_no
where:
master_or_replica ::= MASTER | REPLICA yes_or_no ::= YES | NO
Supported in
This command requires solidDB® advanced replication.
Usage
When a database catalog is created and configured for synchronization use, you must use this command to specify whether the database is a master, replica, or both. Only a DBA or a user with SYS_SYNC_ADMIN_ROLE can set the database role.
The database catalog is a master database if there are replicas in the domain that refresh from publications from this database and/or propagate transactions to it. The database catalog is a replica catalog if it can refresh from publications that are in a master database. In multi-tier synchronization, intermediate level databases serve a dual role, as both master and replica databases.
Note that to use this command requires that you have already set the node name for the master or replica using the SET SYNC NODE command. For details, read SET SYNC NODE.
When you set the database for a dual role, you can use the statement once or twice. For example:
SET SYNC MASTER YES; SET SYNC REPLICA YES;
Note that when you set the database for dual roles, SET SYNC REPLICA YES does not override SET SYNC MASTER YES. Only the following explicit statement can override the status of the master database: SET SYNC MASTER NO;
Once overridden, the current database is set as replica only.
Examples
-- configure as replica
SET SYNC REPLICA YES;
-- configure as master
SET SYNC MASTER YES;
Return values
For details on each error code, see “Error codes” in solidDB® Administration Guide.
Error code
Description
13047
No privilege for operation
13107
Illegal set operation
13133
Not a valid license for this product
25051
Unfinished messages found
SET SYNC CONNECT
SET SYNC CONNECT 'connect_string [,connect_string]' TO MASTER master_name
SET SYNC CONNECT 'connect_string' TO REPLICA replica_name
Supported in
This command requires solidDB® advanced replication.
Usage
This statement changes the network name associated with the database name. Use this statement in a replica (or master) whenever you have changed network names in databases that a replica (or master) connects to. Network names are defined in the Listen parameter of the solid.ini configuration file.
The second connect string in SET SYNC CONNECT ... TO MASTER facilitates transparent failover of a Replica server to a standby Master server, should the Primary Master server fail. The order of the connect strings is not significant. The connection is automatically maintained to the currently active Primary server.
Usage in master
Use this statement in a master to change the replica’s network name.
Usage in replica
Use this statement in a replica to change the master’s network name.
Example
SET SYNC CONNECT 'tcp server.company.com 1313' TO MASTER hq_master;
Return values
For details on each error code, see “Error codes” in solidDB® Administration Guide.
Error code
Description
13047
No privilege for operation
13107
Illegal set operation
21300
Illegal network protocol
25007
Master master_name not found
25019
Database is not a replica database
SET SYNC MODE
SET SYNC MODE { MAINTENANCE | NORMAL }
Supported in
This command requires solidDB® advanced replication.
Usage
This command sets the current catalog’s sync mode to either Maintenance mode or Normal mode.
This command applies only to catalogs that are involved in synchronization (that is, are “master” catalogs or “replica” catalogs, or are both master and replica in a hierarchy with 3 or more levels).
This command applies only to the current catalog. If you want to set more than one catalog’s sync mode to Maintenance, then you will have to switch to each catalog (by using the SET CATALOG command) and then issue the SET SYNC MODE MAINTENANCE command for that catalog.
While a catalog’s sync mode is Maintenance, the following rules apply:
▪The catalog will not send or receive synchronization messages and therefore will not engage in synchronization activities (for example, refresh or respond to a refresh request).
▪DDL commands (for example, ALTER TABLE) will be allowed on tables that are referenced by publications.
▪When the sync mode changes, the server will send the system event
SYNC_MAINTENANCEMODE_BEGIN or SYNC_MAINTENANCEMODE_END.
▪If the master catalog’s publications are altered (dropped and re-created) by using the REPLACE option, then the publication’s metadata (internal publication definition data) is refreshed automatically to each replica the next time that replica refreshes from the changed publication. (This is true whether or not the database was in Maintenance sync mode when the publication was REPLACEd.)
▪Each catalog has a read-only parameter named SYNC_MODE in the parameter bulletin board so that applications can check the catalog’s mode. Values for that parameter are either “MAINTENANCE” if the catalog is in maintenance sync mode or “NORMAL” if the catalog is not in maintenance sync mode. The value is NULL if the catalog is not a master or a replica.
▪The user must have DBA or synchronization administrations privileges to set sync mode to Maintenance or Normal.
▪A user may have more than one catalog in Maintenance sync mode at a time.
▪If the session that set the mode ON disconnects, then mode is set off.
▪The normal synchronization history operations are disabled. For example, when a delete or update operation is done on a table that has synchronization history on, the synchronization history tables will not store the “original” rows (that is, the rows before they were deleted or updated). Note, however, that deletes and updates apply to the synchronization history table, for example:
DELETE * FROM T WHERE c = 5
will delete rows from the history table as well as from the base table. The table below shows how various operations (INSERT, DELETE, and so on) apply to the synchronization history tables in master and replica when sync mode is set to Maintenance.
Operation
Master
Replica
INSERT
Rows are inserted to base table.
Rows are inserted to base table and marked as official.
UPDATE
Both base table and history is updated.
Both base table and history is updated. Tentative/official status is not updated so tentative rows remains tentative and official rows remains official.
DELETE
Rows are deleted from base table and from history.
Rows are deleted from base table and from history.
Add, alter, drop column
Same operation is done to history also.
Same operation is done to history also.
Altering table mode
History mode is not altered
History mode is not altered
Create index
Same index is created to history also
Same index is created to history also
Create triggers
Triggers are not created on history
Triggers are not created on history
Example
SET SYNC MODE MAINTENANCE SET SYNC MODE NORMAL
Return values
For details on each error code, see “Error codes” in solidDB® Administrator Guide.
Error code
Description
13047
No privilege for operation.
13133
Not a valid license for this product.
25021
Database is not master or replica database. This operation only applies to master and replica databases.
25088
Catalog already in maintenance mode. You have set the mode on already.
25089
Not allowed to set maintenance mode off. Someone else has set the mode on so you can not set it off.
25090
Catalog is already in maintenance mode. Someone else has set the mode on so you can not set it on.
25091
Catalog is not in maintenance mode. You tried to set mode off and it is not currently on.
SET SYNC NODE
SET SYNC NODE {unique_node_name | NONE}
Supported in
This command requires solidDB® advanced replication.
Usage
Assigning the node name is part of the registration process of a replica database. Each catalog of a solidDB® environment must have a node name that is unique within the domain. One catalog can have only one node name. Two catalogs cannot have the same node name.
You can use the SET SYNC NODE unique_node_name option to rename a node name if:
▪If the node is a replica database and it is not registered to a master
and/or
▪If the node is a master database and there are no replicas registered in the master database
Following are examples for renaming a node name:
SET SYNC NODE A; -- Now the node name is A.
SET SYNC NODE B; -- Now the node name is B.
COMMIT WORK;
SET SYNC NODE C; -- Now the node name is C.
ROLLBACK WORK; -- Now the node name is rolled back to B.
SET SYNC NODE NONE; -- Now the node has no name.
COMMIT WORK;
The unique_node_name must conform to the rules that are used for naming other objects (such as tables) in the database. Do not put single quotation marks around the node name.
If you specify NONE, then this command will remove the current node name.
If you want to use a reserved word, such as “NONE”, as a node name, then you must put the keyword in double quotation marks to ensure that it is treated as a delimited identifier. For example:
SET SYNC NODE "NONE"; -- Now the node name is "NONE"
You can verify the node name assignment with the following statement: SELECT GET_PARAM('SYNC NODE')
The SET SYNC NODE NONE option removes the node name from the current catalog. This option is used when you are dropping a synchronized database and removing its registration.
Note When using the SET SYNC NODE NONE option, be sure the catalog associated with the node name is not defined as a master, replica, or both. To remove the node name, the catalog must be defined as SET SYNC MASTER NO and/or SET SYNC REPLICA NO. If you do try to set the node name to NONE on a master and/or replica catalog, solidDB® returns error message 25082.
Usage in master
Use this statement in the master to set or remove the node name from the current catalog.
Usage in replica
Use this statement in the replica to set or remove the node name from the current catalog.
Example
SET SYNC NODE SalesmanJones;
Return values
For details on each error code, see “Error codes” in solidDB® Administration Guide.
Error code
Description
13047
No privilege for operation
13107
Illegal set operation
25059
After registration nodename cannot be changed
25082
Node name can not be removed if node is master or replica.
SET SYNC PARAMETER
SET SYNC PARAMETER parameter_name 'value_as_string'; SET SYNC PARAMETER parameter_name NONE;
Supported in
This command requires solidDB® advanced replication.
Usage
This statement defines persistent catalog-level parameters that are visible via the parameter bulletin board to all transactions that are executed in that catalog. Each catalog has a different set of parameters.
If the parameter already exists, the new value overwrites the previous one. An existing parameter can be deleted by setting its value to NONE. All parameters are stored in the SYS_BULLETIN_BOARD system table.
These parameters are not propagated to the master.
In addition to system specific-parameters, you can also store in the system table a number of system parameters that configure the synchronization functionality. Available system parameters are listed at the end of the SQL reference.
Usage in master
Use the SET SYNC PARAMETER in the master for setting database parameters.
Usage in replica
Use the SET SYNC PARAMETER in replicas for setting database parameters.
Example:
SET SYNC PARAMETER db_type 'REPLICA' SET SYNC PARAMETER db_type NONE
Return values
For details on each error code, see “Error codes” in solidDB® Administration Guide.
Error code
Description
13086
Invalid data type in a parameter
See also
GET_PARAM, PUT_PARAM
SET SYNC PROPERTY
Syntax in master
SET SYNC PROPERTY <propertyname> = { 'value' | NONE } FOR REPLICA <replicaname>
Syntax in replica
SAVE SET SYNC PROPERTY <propertyname> = {'value' | NONE }
Supported in
This command requires solidDB® advanced replication.
Usage
This command allows you to specify a property name and value for a replica. Replicas that have properties may be grouped, and a group may be specified when using the START AFTER COMMIT statement. For example, you might have some replicas that are related to the bicycle industry and others that are related to the surfboard industry, and you may want to update each of those groups of replicas separately. You can use Property Names to group these replicas. All members of a group have the same property and have the same value for that property.
For more information, see “Replica Property Names” in solidDB® Advanced Replication Guide.
Examples
Master:
SET SYNC PROPERTY color = 'red' FOR REPLICA replica1; SET SYNC PROPERTY color = NONE FOR REPLICA replica1;
Replica:
SAVE SET SYNC PROPERTY color = 'red'; SAVE SET SYNC PROPERTY color = NONE;
SET SYNC USER
SET SYNC USER master_username IDENTIFIED BY password SET SYNC USER NONE
Supported in
This command requires solidDB® advanced replication.
Usage
This statement is used to define the username and password for the registration process when the replica database is being registered in the master database. To use this command, you are required to have SYS_SYNC_ADMIN_ROLE access.
Note The SET SYNC USER statement is used for replica registration only. Aside from registration, all other synchronization operations require a valid master user ID in a replica database. If you want to designate a different master user for a replica, you must map the replica ID on the replica database with the master ID on the master database. For details, see “Mapping Replica User ID With Master User ID” in solidDB® Advanced Replication Guide.
You define the registration username in the master database. The name you specify must have sufficient rights to execute the replica registration tasks. You can provide registration rights for a master user in the master database by designating the user with the SYS_SYNC_REGISTER_ROLE or the SYS_SYNC_ADMIN_ROLE using the GRANT rolename TO user statement.
After the registration has been successfully completed, you must reset the sync user to NONE; otherwise, if a master user saves statements, propagates messages, or refreshes from or registers to publications, the following error message is returned:
User definition not allowed for this operation.
Usage in master
This statement is not available in the master database.
Usage in replica
Use this statement in the replica to set the user name.
Example
SET SYNC USER homer IDENTIFIED BY marge; SET SYNC USER NONE;
See also
SET