Create or modify a copy request - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869

Usage

Creates or modifies a request for a snap-shot copy.

Syntax
  CREATE V5 COPY REQUEST name AT [DISTRIBUTION] distname
Or   ALTER V5 COPY REQUEST name AT [DISTRIBUTION] distname TO
      [PATHGROUP name]
      [SCHEDULE START {NOW | ON month/day/year [AT hours:minutes:seconds]}
         [DURATION n {HOUR[S]|MINUTE[S]|SECOND[S]|DAY[S]|MONTH[S]}]
         [STOP ON month/day/year [AT hours:minutes:seconds] ]
         [EVERY n {HOUR[S]|MINUTE[S]|SECOND[S]|DAY[S]|MONTH[S]}]
      ]
      [CONNECT TO [SERVER] server-name USING username] ...
      [ON_ERROR {TERMINATE | SKIP_RECORD | NEXT_TABLE}]
      [COMMIT {EACH_TABLE | ALL_TABLES | EVERY n RECORDS}]
      [ISOLATION {READ_UNCOMMITTED | READ_COMMITTED |
            REPEATABLE_READ | SERIALIZABLE | VERSIONING}]
      [LOCKING {BY_TABLE | ALL_TABLES}]
      [[NOT] ORDERED_RETRIEVAL]
      [[NOT] USELOADER]
      [APPLY {DELETE | APPEND | REFRESH | MERGE}
         [DESCRIPTION ‘descriptive text’]
      ;

The CREATE default is COMMIT EVERY 100 RECORDS.

Parameters

Parameter

Description

CREATE

Establish a new copy request in a previously defined distribution.

ALTER

Modify a previously established copy request. Specify the distribution for this request.

PATHGROUP

Specify the path group (if any) to use for this request.

SCHEDULE...

Set copy request times:

  • Start

  • Stop

  • Duration

  • Pattern of repetition every n hours, minutes, seconds, days, or months.

CONNECT

Specify a username for a server connection.

ON_ERROR

Determines what to do if recoverable errors are encountered:

  • TERMINATE

Terminate the request. This is the default option.

  • SKIP_RECORD

Copy Retriever/Applier goes on to the next record.

If the request’s Target apply mode is Delete on target, refresh, and deletion of the table data fails, the copy phase is skipped for the table.

  • NEXT_TABLE

Copy Retriever/Applier goes on to the next table in the request.

COMMIT

Determine the number of rows to copy per commit(the default value is 100 rows per commit):

•EACH_TABLE

All rows for each table. Each table is committed as a unit.

•ALL_TABLES

All rows for all tables. The entire request is committed as a unit.

•EVERY n RECORDS

Every specified number of rows.

ISOLATION

Determine the transaction isolation level on the source. The higher the isolation level you use, the more consistent the results are that Copy produces. For details on transaction isolation levels, see your SQL manual. Not all SQL DBMSs provide all four isolation levels. Check what levels are available for your DBMS.

  • READ_UNCOMMITTED–Processing is the fastest, but the extracted copy may include uncommitted changes from running transactions.

Note: DB2 provides this isolation level only when a table has a Type 2 index. If your source table does not have a Type 2 index and you specify READ_UNCOMMITTED, you will get READ_COMMITTED functionality instead.

  • READ_COMMITED–Extracts only committed changes, but the rows in the extracted copy can be updated by other concurrent transactions.

  • REPEATABLE_READ–Extracts only committed changes, and ensures that the rows being extracted cannot be updated during the extraction.

New rows that satisfy a search condition of the extraction may be added by other concurrent transactions. However, your extract transaction will not include those rows.

  • SERIALIZABLE–Provides serial ordering of concurrent transactions to produce consistent results.

LOCKING

Determine whether a source table is locked for the entire request or only while this single table is being copied:

  • BY_TABLE–Access and lock, one at a time, all source tables specified in the TABLES list for copying, and commits on the source after processing each table, thus unlocking the table.

  • BY_REQUEST–Access and lock, one at a time, all source tables specified in the TABLES list for copying, and commits on the source and unlocks the tables after the entire request is processed. This is the default.

ORDERED_RETRIEVAL

Specify that the order in which tables are retrieved is important. They are retrieved in the order defined to the distribution.

APPLY

Determine how to apply rows to the receiving table:

  • DELETE–Complete replacement of the target records. Old records on the target are deleted, then new records from the source are inserted. Any duplicate rows are treated as an error.

If your request has multiple tables, the Copy Applier completes its work in two passes: In the first pass, all the deletions from all the tables involved are completed. In the second pass, all the sending table rows are inserted.

In the first pass, the selected tables are processed from the bottom to the top in their copy request display order. In the second pass, the tables are processed in top-to-bottom order.

Important: If you select this mode, all the rows in the target table will be deleted, even if you defined specific rows to copy based on a gate condition specified in the CREATE TABLE MAPPING statement. If this is not what you want, select a different mode, or manually delete the rows on the target and then run the Copy request.

  • APPEND–The target data is assumed to be correct. Only new source records are inserted. Duplicate rows are ignored and processing continues.

  • REFRESH–The source data is assumed to be correct. Duplicate rows are deleted and the source rows inserted.

  • MERGE–The target data is assumed to be correct, but the source data overwrites it where there is a difference. To merge the source and the target tables, inserts are changed to updates on duplicate rows. This mode may be used to merge columns, creating a “materialized join” table.

DESCRIPTION

User-defined text.