cdr define replicate
The cdr define replicate command defines a replicate on the specified replication servers.
Syntax
- 1 See Connect Option.
- 2 See Replicate Types.
- 3 See Conflict Options.
- 4 See Replication to SPL routine.
- 5 See Scope Options.
- 6 See Frequency Options.
- 7 See Special Options.
- 8 See Shadow Replicate Options.
| 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.
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.
| 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:
|
| --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
|--+--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.
Conflict Options
The --conflict options specify how Enterprise Replication resolves data conflicts at the database server.
| 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.
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:
|
Scope Options
The --scope options specify the scope of Enterprise Replication conflict resolution.
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.
When you specify the scope, you can abbreviate transaction to tra. |
Special Options
| 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:
|
|
| --anyUniqueKey | -U | Specifies that the replication key is detected
automatically from the following sources in the follow order:
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:
|
| --ignoredel= | -D | Specifies whether to retain deleted rows on other
nodes:
|
| --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.
|
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.
| 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.