Participant and participant modifier

A participant defines the data (database, table, and columns) to be replicated on a specific database server. You can choose whether to allow the participant to both send and receive replicated data, or to only receive replicated data. You can choose whether to allow the participant to both send and receive replicated data, or to only receive or only send replicated data. You can choose to check for table owner permissions when applying operations. By default, permissions are not checked. The participant modifier is a restricted SELECT statement that specifies the rows and columns that are replicated.

Syntax

Participant

1 "
2.1! P
2.1 R
2.1 S
2.1 O
2.1! I
1database@server_group:owner.table
1"
Participant Modifier

1  "SELECT
2.2.1+ ,
2.2.1 column
2.1  *
1 FROM
1 table
1 WHERE_Clause"
Element Purpose Restrictions Syntax
column Name of a column in the table that is specified by the participant.

The replication key columns must be included.

 The column must exist. Long Identifiers
database Name of the database that includes the table to be replicated. The database server must be registered with Enterprise Replication. Long Identifiers
owner User ID of the owner of the table to be replicated. Long Identifiers
server_group Name of the database server group that includes the server to connect to. The database server group name must be the name of an existing Enterprise Replication server group in the sqlhosts information and must be used only once in the same replicate. Long Identifiers
table Name of the table to be replicated. Must be the same table name in the participant and participant modifier. The table must be an actual table. It cannot be a synonym or a view. Long Identifiers
WHERE_Clause Clause that specifies a subset of table rows to be replicated. Can include opaque user-defined types that are always stored in row.

Cannot contain a column of a TimeSeries data type.

 WHERE Clause of SELECT

The following table describes the participant options.

Option Meaning
I Default. Disables the table-owner option (O).
O

Enables permission checks for table owner that is specified in the participant to be applied to the operation (such as INSERT or UPDATE) that is to be replicated and to all actions fired by any triggers. When the O option is omitted, all operations are run with the privileges of user informix.

Enables permission checks for table owner that is specified in the participant to be applied to the operation (such as INSERT or UPDATE) that is to be replicated and to all actions fired by any triggers. When the O option is omitted, all operations are run with the privileges of user informix or the server owner.

On UNIX™, if a trigger requires any system-level commands (as specified by the system() command in an SPL statement), the system-level commands are run as the table owner, if the participant includes the O option.

On Windows™, if a trigger requires any system-level commands, the system-level commands are run as a less privileged user because you cannot impersonate another user without having the password, whether the participant includes the O option.

P For primary-target replicates, specifies that the participant is a primary participant, which both sends and receives replicated data.

Do not use for an update-anywhere replicate. Enterprise Replication defines all the participant as primary in an update-anywhere replication system.
R For primary-target replicates, specifies that the participant is a receive-only target participant, which only receives data from primary participants.
S For primary-target replicates, specifies that the participant is a send-only primary participant, which only sends data to target participants.

You cannot use this option for replicates that include TimeSeries columns.

Usage

Each participant in a replicate must specify a different database server. The participant definition includes the following information:
  • Database in which the table is located
  • Table name
  • Table owner
  • Participant type
  • Participant modifier with a SELECT statement

You must include the server group, database, table owner, and table name when you define a participant, and enclose the entire participant definition in quotation marks.

If you use a SELECT * FROM table_name statement, the tables must be identical on all database servers that are defined for the replicate.

If you use a SELECT * FROM table_name statement, the tables must be identical on all database servers that are defined for the replicate, unless you implement a data consolidation system by defining one server to receive data and several other servers that only send data.

Restriction: Do not create more than one replicate definition for each row and column combination to replicate. If the participant overlaps, Enterprise Replication attempts to insert duplicate values during replication.
You can define participants with the following commands:
  • cdr define replicate
  • cdr modify replicate
  • cdr change replicate
  • cdr define template
The following restrictions apply to a SELECT statement that is used as a participant modifier:
  • The statement cannot include a join or a subquery.
  • The statement cannot run operations on the selected columns.
  • The statement cannot exceed 15 000 ASCII characters in length.
  • For tables that have TimeSeries columns, all columns must be included.

Replicate only between like data types. For example, do not replicate between the following combinations of data types:

  • CHAR(40) to CHAR(20)
  • INT to FLOAT
You can replicate between the following types with caution:
  • SERIAL and INT
  • BYTE and TEXT
  • BLOB and CLOB
Note: The ERKEY shadow columns are not included in the participant definition if you use SELECT * in your participant modifier. To include the ERKEY shadow columns in the participant definition, use the --erkey option with the cdr define replicate, cdr change replicate, or cdr remaster commands.

Example 1: Defining update-anywhere participants

If you do not specify the participant type, Enterprise Replication defines the participant as update-anywhere by default. For example: 
db1@g_hawaii:informix.mfct select * from mfct \
db2@g_maui:informix.mfct select * from mfct

Example 2: Defining a primary server

For example, in the following participant definition, the P indicates that in this replicate, hawaii is a primary server: 
P db1@g_hawaii:informix.mfct select * from mfct

If any data in the selected columns changes, that changed data is sent to the secondary servers.

Example 3: Defining a server that only receives data

In the following example, the R indicates that in this replicate, maui is a server that only receives data: 
R db2@g_maui:informix.mfct select * from mfct

The specified table and columns receive information that is sent from the primary server. Changes to those columns on maui are not replicated.

Example 4: Defining a data consolidation system with servers that only send data

To implement a data consolidation system, you can define one server to receive and consolidate the data and configure several other servers that only send data. In the following example, the S options indicate that the rome, tokyo, perth, and ny servers can only send data:

"db0@london:user.world_sales" "select * from world_sales"\
"S db1@rome:user1.sales_rome" "select * from sales_rome"\
"S db2@tokyo:user2.sales_tokyo" "select * from sales_tokyo"\
"S db3@perth:user3.sales_perth" "select * from sales_perth"\
"S db4@ny:user4.sales_ny" "select * from sales_ny"\

The central server, london, is a standard replication server without restrictions on sending or receiving data.