Define copy requests - Connect_CDC - connect_cdc_mimix_share - Latest

Copy method

Select Use load mode when possible (available for MS SQL Server, MySQL, Oracle, PostgreSQL and Teradata). This applies the database's fast load utility. See Fast Loader for more information.

Note: When using the load option for a copy or Sync request, for any source control characters, Unicode characters in the range 0x0000 through and inclusive of 0x001F, in a CHAR or VARCHAR column, will be substituted with a space character when the data is applied to a Teradata target.

Error mode

This determines how the kernel handles errors when applying rows to a target server.

• Terminate run, the default, terminates the request upon a recoverable error instead of trying to continue copying to this table or to another table.

• Skip Record: Go on to the next target row if a recoverable error is encountered. Note that for a Teradata load, this option is currently ignored.

• If the Target apply mode is Delete on target, refresh, and deletion of the table data fails, the copy phase is skipped for the table even if Skip Record is set.

• Next table: Go on to the next table in the request if a recoverable error is encountered.

Commit cycle

This determines how many row operations are applied at the target before a commit operation is performed.

The default, Count, is to apply the number of rows you specify, commit the transaction, then repeat similar applies and commits until all the records are applied.

Other options are:

• All records each table: Copy all rows for each table per commit. Each table is committed as a unit.

• All records all tables: Copy all rows for all tables per commit. The entire request is committed as a unit.

Isolation level

SQL transaction isolation level set at the source server.

The exact set of options and the default may vary with the server DBMS. Example options are:

• Read uncommitted:  Select this button for the fastest processing. The extracted copy may include uncommitted changes from running transactions.

• Read committed: Extract only committed changes. Although the extracted copy will contain only committed changes, the rows being extracted can be updated by other concurrent transactions.

• Repeatable read: Extract only committed changes and ensure that the rows being extracted cannot be updated during the extract transaction. However, other concurrent transactions may add new rows that satisfy a search condition of the extraction. Your extract transaction will not include those rows.

• Serializable: Extract only committed changes and ensure that during your extract transaction:

Rows being extracted cannot be updated by other concurrent transactions

New rows that satisfy a search condition of the extraction cannot be added by other concurrent transaction

Source locking

Table locking approach used at the source during the copying.

The default, By individual table, is to access and lock, one at a time, the source tables selected for copying in this request; and to commit and unlock the table after it is processed.

The other option is:

• All tables: Access and lock, one at a time, the source tables selected for copying in this request; and commit (and unlock the tables) only after processing all the tables.

Source table retrieval order is important

Whether any referential integrity constraints on your source tables require them to be arranged and copied in parent-before-child order. The default option No, retrieve in any order specifies that the tables you are copying do not have referential integrity constraints.

The other option is Yes, which retrieves in order of mappings indicates Source table retrieval order is important.

Select this option if the tables you are copying have referential integrity constraints. In this case, parent tables must be copied before their child tables.

To do this, map the parent tables before you map the child tables, because tables are copied in the order (top-to-bottom) in which their target table mappings appear in the Mappings branch of the distribution that contains the copy request. By default, this order is the time sequential order in which the table mappings are defined.

Instead of being concerned with the order in which mappings are defined, you can rearrange the order in the target table mapping list by using your mouse to select, drag, and drop individual mappings. Note that this drag and drop functionality is enabled only if this copy request option (Yes, retrieve in order of mappings) has been selected.

Additional options for ensuring that parent tables are copied before their child tables include using views of the source tables, or grouping parent and child tables in different copy requests and first executing the request to copy parent tables.

If you arrange your tables in parent-before-child order, and you select Delete on target, refresh for Target apply mode on this tab, the target rows are deleted in child-before-parent order to preserve referential integrity.

Target apply mode

Defines how the kernel handles the existing target table data when applying the source rows.

• Delete on target, refresh–Performs a complete replacement of the receiving rows from all target tables before inserting any of the copied rows. This is the default. This is the option used for the first distribution in this example.

• Source correct, refresh–In contrast, using the Source correct, refresh option deletes existing target records that have the same key values as copied source records and then inserts the source records. If columns from more than one source table were copied to the same target table, the source record inserts of duplicate keys overwrite each other. The resulting target record contains the column values of only the last copied source row and thus is not a merged record.

• Append only–Adds source records with new key values to the target and ignores source records with keys that match target records. Note the following for Teradata:

• If duplicates exist on the target, Multiload for a unique primary index will do an UPSERT type of load. However, if a row does not exist for the key, an INSERT will be done. If a row already exists for the key being processed, then an UPDATE will be done. If there are many duplicates, this significantly slows down the load.

• If there is a unique secondary index that will be dropped, you must ensure that there will not be any unique constraint violations between the rows that currently exist in the table and any rows that will be added.

• Target correct, merge–Under this merge option, when the Distribution Key values are the same, Copy only updates the columns selected for distribution. If the source row keys are new, Copy inserts them. Existing target rows with key values different from the source are left as is.

To accommodate the requirement to preserve the existing target column values for the columns that are not being copied from the source, the second distribution in the example is moved by a copy request using the Target correct, merge apply mode.

Use this option when columns from more than one source table are being copied to the same target table.