cdr define replicate

The cdr define replicate command defines a replicate on the specified replication servers.

Syntax


1  cdr define replicate?  %Connect Option  (1)?  %Replicate
Types (2)  %Conflict Options  (3)?  %Replication to SPL Options(4)?  %Scope Options  (5)?  %Frequency Options  (6)?  %Special Options  (7)
1 replicate
1?  %Shadow Replicate Options
 (8)
2? +  participant modifier
Element Purpose Restrictions Syntax
modifier Specifies the rows and columns to replicate. Participant and participant modifier
participant Name of a participant in the replication. The participant must exist. Participant and participant modifier
replicate Name of the new replicate. The replicate name must be unique. Long Identifiers

Usage

All servers that are specified as participants for the replicate must be online and the cdr utility must be able to connect to each participant.

To be useful, a replicate must include at least two participants. You can define a replicate that has one or no participant, but before you can use that replicate, you must use the cdr change replicate command to add more participants. You cannot start and stop replicates that have no participants.

If you run this command as a DBSA instead of as user informix, you must have INSERT, UPDATE, and DELETE permission on the replicated tables on all the replication servers in the domain.

When you define a replicate, the replicate does not begin until you explicitly change its state to active by running the cdr start replicate command.
Important: Do not create more than one replicate definition for each row and column combination to replicate. If the participant is the same, Enterprise Replication attempts to insert duplicate values during replication.

The maximum number of replicates that you can define as participants on a particular replication server is 32767.

You can run this command from within an SQL statement by using the SQL administration API.

Replicate Types

By default, replicates are master replicates. If you do not specify a master server, the master replicate is based on the first participant. A master replicate uses saved dictionary information about the attributes of replicated columns to verify that participants conform to the specified schema. You must specify at least one participant when you create a master replicate. All participants that are specified are verified when the cdr define replicate or cdr change replicate command is run. If any participant does not conform to the master definition, the command fails and that local participant is disabled. If a participant you specify does not contain the master replicate table, Enterprise Replication automatically creates the table on the participant, based on the master replicate dictionary information. All database servers that have master replicates must be able to establish a direct connection with the master replicate database server.

When you create a master replicate and do not include a participant modifier, the database server internally generates a participant modifier with SELECT statement that lists each column name in the table. The database server requires the individual column names to verify the schema. If the length of the SELECT statement exceeds 15 000 ASCII characters, replicate creation fails. If your column names are too long, you can create a classic replicate, which has a generated participant modifier of SELECT *.

If you do not want to verify the schema, create a classic replicate. For example, if you want to create a data consolidation system in which one server only receives data from other servers that only send data, create a classic replicate by including the --classic option.

Replicate Types

1  --classic
1 %Master Replicate Options
Master Replicate Options

1  --master=server
1  --empty
2?  --name=
3.1! y
3.1 n
1  --verify
1?   --autocreate
Element Purpose Restrictions Syntax
server Name of the database server group from which to base the master replicate definition. The name must be the name of a database server group. Long Identifiers

The following table describes the replicate type options.

Long Form Short Form Meaning
--autocreate -u Specifies that if the tables in the master replicate definition do not exist in the databases on the target servers, they are created automatically. However, the tables cannot contain columns with user-defined data types. The tables are created in the same dbspace as the database.
Note: Tables that are created with the --autocreate option do not automatically include indexes that are not based on the replication key, defaults, constraints (including foreign constraints), triggers, or permissions. If the tables you create with the --autocreate option require the use of these objects you must manually create those objects.

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

--classic Specifies that the replicate being created is a classic replicate.
--empty -t Specifies that the participant on the server that is specified with the --master option is used as the basis of the master replicate, but is not added to the replicate.
--master= -M Specifies that the replicate being created is a master replicate.

If you omit this option, the master replicate is based on the first participant.

--name= -n Specifies whether the master replicate has column name verification in addition to column data type verification. Valid values are:
  • --name=y = Default. Column names are verified to be the same on all participants.
  • --name=n = Column names are not verified and discrepancies can exist.
--verify -v Specifies that the cdr define replicate command verifies the database, tables, and column data types against the master replicate definition on all listed servers.

Replication to SPL routine

At target participant, ‘replication to SPL routine’ type replicate definition causes SPL routine to be executed instead of applying data to target table. Target participant for “replication to SPL routine" replicate definition can be configured to be same as source database, different database on the same server, or remote peer Enterprise Replication server. “Replication to SPL routine" replicate definition does not enforce the requirement to have primary key, unique index or ER key on the replicated table.
Note: Even though data is applied to stored procedure routine, target table definition must exist.
For more information see, Replication to SPL routine

|--+--splname=spl_routine_name----+-------+---------------------+--------->
   |                              |       |               .-y-. |   
   
'--'--jsonsplname=spl_routine_name-'      '- -- cascaderepl=-+-n-+-'   
Long Form Meaning
--splname Stored procedure routine name to apply data to. SPL routine must exist at all participants. Column list for SPL routine extracted from replicate participant select statement column projection list.
--jsonsplname Stored procedure routine name to apply data to. SPL routine and table definition must exist at all participants. Input argument for SPL routine must be a JSON document. --jsonsplname option is mutually exclusive to --splname option.
--cascaderepl Enable cascade replication. Required if replication to SPL needs to be executed for the data applied through Enterprise Replication.

--splname option stored procedure argument list:

  • Optype char(1) – operation type. Values include
    • I – Insert
    • U – Update
    • D – Delete
  • Soucre_id integer – Source server id. Same as group id.
  • Committime integer – Transaction commit time.
  • Txnid bigint – Transaction id.
  • Before value column list.
  • After value column list.
Note: Column list for SPL routine extracted from select statement projection list

Conflict Options

The --conflict options specify how Enterprise Replication resolves data conflicts at the database server.

Conflict Options

1  --conflict=
1 always
1 ignore
1  SPL_routine?  --optimize
1  timestamp?  , SPL_routine?  --optimize
1 deletewins
Element Purpose Restrictions Syntax
SPL_routine SPL routine for conflict resolution The SPL routine must exist. Long Identifiers

The following table describes the --conflict options.

Long Form Short Form Meaning
--conflict= -C Specifies the rule that is used for conflict resolution.
  • Use the always option if you do not want Enterprise Replication to resolve conflicts, but you do want replicated changes to be applied even if the operations are not the same on the source and target servers. Use the always-apply conflict resolution rule only with a primary-target replication system. If you use always-apply with an update-anywhere replication system, your data might become inconsistent. You must use the always-apply rule if your replicate includes TimeSeries data types.
  • Use the ignore option if you do not want Enterprise Replication to resolve conflicts.
  • Use the timestamp option to have the row or transaction with the most recent time stamp take precedence in a conflict.
  • Use the deletewins option to have the row or transaction with a DELETE operation, or otherwise with the most recent time stamp, take precedence in a conflict. The delete wins conflict resolution rule prevents upserts.

The action that Enterprise Replication takes depends on the scope.

--optimize -O Specifies that the SPL routine is optimized. An optimized SPL routine is called only when a collision is detected and the row to be replicated fails to meet one of the following two conditions:
  • It is from the same database server that last updated the local row on the target table.
  • It has a time stamp greater than or equal to that of the local row.
When this option is not present, Enterprise Replication always calls the SPL routine that is defined for the replicate when a conflict is detected.

Scope Options

The --scope options specify the scope of Enterprise Replication conflict resolution.

Scope Options

1  --scope=
1! transaction
1 row

The following table describes the --scope option.

Long Form Short Form Meaning
--scope= -S Specifies the scope that is used when Enterprise Replication encounters a problem with data or a conflict occurs.
  • --scope=row = Evaluate one row at a time and apply the replicated rows that win the conflict resolution with the target rows.
  • --scope=transaction = Default. Apply the entire transaction if the replicated transaction wins the conflict resolution.

When you specify the scope, you can abbreviate transaction to tra.

Special Options

Special Options

1+ 
1  --ats
1  --ris
1  --floatieee
1  --floatcanon
1  --firetrigger
1  --fullrow=
2.1! y
2.1 n
1  --ignoredel=
2.1 y
2.1! n
2.1 ?  --erkey?   --key + , column_name
2.1  --anyUniqueKey
1  --UTF8=
2.1 y
2.1! n
1  --serial
1  --alwaysRepLOBs=
2.1 y
2.1! n
Element Purpose Restrictions Syntax
column_name The name of a column that is included in a unique index or constraint The column must exist. Long Identifiers

The following table describes the special options to the cdr define replicate command.

Long Form Short Form Meaning
--alwaysRepLOBS= Specifies whether columns that contain unchanged large objects are included in replicated rows:
  • --alwaysRepLOBS=n: Default. Columns that contain unchanged large objects are not replicated.
  • --alwaysRepLOBS=y: Columns that contain large objects are always included in replicated rows.
--anyUniqueKey -U Specifies that the replication key is detected automatically from the following sources in the follow order:
  • A primary key that is defined on the table
  • ERKEY shadow columns that are included in the table
  • Any unique key or unique constraint that is defined on the table

The replicate must be a strictly mastered replicate.

If the table includes ERKEY shadow columns, those columns are included in the participant definition only if the table does not have a primary key.

--ats -A Activates aborted transaction spooling for replicate transactions that fail to be applied to the target database.
--erkey -K Adds the ERKEY shadow columns, ifx_erkey_1, ifx_erkey_2, and ifx_erkey_3, to the participant definition, if the table includes the ERKEY shadow columns. An index that is created on the ERKEY shadow columns is used as the replication key, unless the --key option is included.
--firetrigger -T Specifies that the rows that the replicate inserts fire triggers at the destination.
--floatieee -I Transfers replicated floating-point numbers in either 32-bit (for SMALLFLOAT) or 64-bit (for FLOAT) IEEE floating-point format. Use this option for all new replicate definitions.
--floatcanon -F Transfers replicated floating-point numbers in machine-independent decimal representation. This format is portable, but can lose accuracy. This format is provided for compatibility with earlier versions only; use --floatieee for all new replicate definitions.
--fullrow= -f

Specifies whether to replicate full rows or only the changed columns:

  • --fullrow=y = Default. Indicates to replicate the full row and to enable upserts. If you also specify deletewins as the conflict resolution rule, upserts are disabled.
  • --fullrow=n = Indicates to replicate only changed columns and disable upserts.
--ignoredel= -D Specifies whether to retain deleted rows on other nodes:
  • --ignoredel=y = Indicates that rows are retained if they are deleted on other nodes in the Enterprise Replication domain. You cannot use this option if you specify deletewins as the conflict resolution rule.
  • --ignoredel=n = Default. Indicates that deleted rows are deleted on all nodes in the Enterprise Replication domain.
--key= -k Specifies the columns that are included in an existing unique index or unique constraint to use as the replication key. All the columns that are included in the unique index or constraint must be listed, in the same order as the columns are listed in the index or constraint definition. The replicate must be a strictly mastered replicate.

The unique index or constraint that the --key option specifies is used as the replication key even if the table has an existing primary key or ERKEY columns.

--ris -R Activates row-information spooling for replicate row data that fails conflict resolution or encounters replication order problems.
--serial -s Specifies that replicated transactions for the replicate are applied serially instead of in parallel.
--UTF8= None Specifies whether to enable conversion to and from UTF-8 (Unicode) when you replicate data between servers that use different code sets.
  • --UTF8=y Default. Indicates that character columns are converted to UTF-8 when the row is copied into the transmission queue. When the replicated row is applied on the target server, the data is converted from UTF-8 to the code set used on the target server. No attempt is made to convert character data that is contained within opaque data types. You cannot use --UTF8=y for replicates that contain TimeSeries data types, user-defined data types, or DataBlade® module data types.
  • -UTF8=n Indicates that code set conversion is ignored.

Shadow Replicate Options

A shadow replicate is a copy of an existing, or primary, replicate. You must create a shadow replicate to manually remaster of a replicate that is defined with the -n option. After you create the shadow replicate, the next step in manual remastering is to switch the primary replicate and the shadow replicate by running the cdr swap shadow command.

Shadow Replicate Options

1?   --mirrors primary_replicate shadow_replicate
Element Purpose Restrictions Syntax
primary_replicate Name of the replicate on which to base the shadow replicate. The replicate must exist. The replicate name must be unique. Long Identifiers
shadow_replicate Name of the shadow replicate to create. The replicate name must be unique. Long Identifiers

The following table describes the shadow replicate option to cdr define replicate.

Long Form Short Form Meaning
--mirrors -m Specifies that the replicate created is a shadow replicate based on an existing primary replicate.

Example 1: Define a replicate with two participants

The following example defines a replicate with two participants:

cdr define repl --conflict=timestamp,sp1 \
--scope=tran --ats --fullrow=n --floatieee newrepl \
"
db1@iowa:antonio.table1"
 "
select * from table1"
 \
"
db2@utah:carlo.table2"
 "
select * from table2"

Line 1 of the example specifies a primary conflict resolution rule of timestamp. If the primary rule fails, the SPL routine sp1 is run to resolve the conflict. Because no database server is specified here (or on any later line), the command connects to the database server named in the INFORMIXSERVER environment variable.

Line 2 specifies that the replicate has a transaction scope for conflict resolution scope and enables aborted transaction spooling. Enterprise Replication replicates only the rows that changed and uses the IEEE floating point format to send floating-point numbers across dissimilar platforms. The final item specifies the name of the replicate, newrepl.

Line 3 defines the first participant, "db1@iowa:antonio.table1", with the SELECT statement "select * from table1".

Line 4 defines a second participant, "db2@utah:carlo.table2", with the SELECT statement "select * from table2".

Example 2: Define a replicate with a frequency of every five hours

This example is the same as the preceding example with the following exceptions:

  • Line 1 instructs Enterprise Replication to use the global catalog on database server ohio.
  • Line 2 specifies that the data is replicated every five hours.
cdr def repl -c ohio -C timestamp,sp1 \
-S tran -A -e 5:00 -I newrepl \
"db1@iowa:antonio.table1" "select * from table1" \
"db2@utah:carlo.table2" "select * from table2"

Example 3: Define a master replicate

The following example defines a master replicate:

cdr def repl -c iowa -M iowa \
-C deletewins -S tran newrepl \
"db1@iowa:antonio.table1" "select * from table1"

Line 1 instructs Enterprise Replication to create a master replicate based on the replicate information from the database server iowa. Line 2 specifies the delete wins conflict resolution rule, a transaction scope, and that the name of the replicate is newrepl. Line 3 specifies the table and columns included in the master replicate.

Example 4: Define a master replicate and create a table on a participant

This example is the same as the previous example except that it specifies a second participant in Line 4. The second participant (utah) does not have the table table1 specified in its participant and modifier syntax. The -u option specifies to create the table table1 on the utah server.

cdr def repl -c iowa -M iowa \
-C deletewins -S tran newrepl -u\
"db1@iowa:antonio.table1" "select * from table1 \
"db2@utah:carlo.table1" "select * from table1"

Example 5: Define a replicate with the ERKEY shadow columns

This example defines a master replicate similar to the one in example 3, and includes the ERKEY shadow columns for the replication key.

cdr def repl -c iowa -M iowa \
-C deletewins -S tran newrepl --erkey\
"db1@iowa:antonio.table1" "select * from table1"

Example 6: Define a data consolidation system

This example defines a replicate for a data consolidation system in which one target server receives replicated data from four primary servers.

cdr def repl -c london \
sales -C always\
"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 S options in the participant definitions indicate that the rome, tokyo, perth, and ny servers can only send replicated data to the london server.