Use the CREATE REPLICATION SUBSCRIPTION statement to create a replication subscription. The subscription determines how a given partition from the source database is replicated to the target database.
Parameters, clauses, keywords, and variables
▪ subscription‑name: Name of subscription. Use this name to start, stop, and check the status of replication. The subscription name must be unique in the target database.
▪ partition‑name: Defines the partition on the source database that needs to be replicated. By default the partition name is the same as subscription name.
▪ connect‑name : Refers to the connection that was created in the target database and is to be used by this subscription.
▪ SYMMETRICAL and IGNORE LOOP: Replication modes for symmetrical replication configurations. When replication is deployed in configurations where multiple databases replicate from all other databases, any insertion causes potential conflict. The first insert operation is replicated from the one database, and then from every other database that replicates with the first database, causing a transaction loop.
– Specify SYMMETRICAL to specify that the target database does not apply any transactions that do not originate from the immediate source.
– Specify IGNORE LOOP to turn off transaction loop detection. For example, when a transaction that is initiated on one database is replicated to another database and from there replicated back to the original database but mapped to update a different table.
▪ conflict‑resolution‑rule: Defines the rule that is used to resolve any conflicts that occur when data is replicated from the source database to the target database.
▪ automatic‑load: Defines the rules to be used when the target database loads the data from the source database. Load is used when the source database replicates with the target database for the first time, or when online replication is interrupted for a significant period of time and Catchup is not possible (too many transactions were executed on the source database, and the transaction log does not have all the information needed for Catchup). Load can be executed by using one of the following rules:
– NONE: Leaves the table contents unchanged.
– TRUNCATE: (default) Deletes all the data from the target table and copies everything from the source table. The TRUNCATE method follows WHERE conditions that are specified in partition definitions and neither deletes, nor copies the rows that do not match the WHERE conditions.
– INSERT: Inserts the data from the source table to the target table. In the case of data conflict, the conflict resolution rules are followed, and all the rows that were in the target table before load started and which do not conflict with the data in the source table remain as they were before the load.
▪ FROM SOURCE (default) or FROM TARGET: Specifies the load direction. If the data is copied from the source database to the target database, the data is copied in multiple simultaneous streams. Multiple tables are copied at the same time and parts of the same source table might be copied simultaneously to maximize the copying performance, and minimize the time needed to synchronize the data. This optimization is not possible when data is copied from the target database to the source database; in this case, the data is always copied over only one database connection, which limits the copying throughput.
▪ NO TRIGGERS: Disables DELETE and INSERT triggers when running data load. You might use this option if you want applications to invoke triggers only when the subscription state is ACTIVE.
▪ table‑mapping‑list: Defines which source tables replicate with which target tables. Use this option when the table names in the source database do not match the table names in the target database. Table name patterns are enclosed in double-quotes in format "catalog‑name.schema‑name.table‑name", where any component can be a wildcard character (*). For example, if you map TABLE "CAT1.*.*" INTO "CAT2.*.*" changes to tables in catalog CAT1 in the source database are propagated to tables with the same name and schema in catalog CAT2 in the target database.
▪ column‑name‑list: Defines which columns in the source table update which columns in the target table. Use this option when the column names in the source table do not match the column names in the target table. For example, if you map TABLE "*.*.TAB1" (col_a, col_b) INTO "*.*.TAB2" (a_col, b_col), changes to columns col_a and col_b in the table TAB1 are propagated to columns a_col and b_col respectively in the table TAB2.
Examples
CREATE REPLICATION SUBSCRIPTION sub1 PARTITION share1 CONNECT TO conn1;