When you define a subscription that replicates a table (or object treated as a table) or view the Columns tab properties for an existing subscription, you specify which source columns to replicate and the order of destination columns in the destination table. For a new subscription, all source columns are listed, with default destination column settings. An existing subscription displays only the columns defined for the saved subscription. For an existing subscription you can click Rebuild Column List to re-display all the available source columns and reset the destination columns to the default values.
Rebuilding the column list may remove derived columns unless they have been defined using the Import Mapping Script... option on the Destination tab. Using the Merge with Source button will retain existing derived columns, or you can re-import the derived column mapping script prior to rebuilding the column list.
If the structure of the source object(s) is changed in a way that affects the data that is replicated (such as dropping a source column that is subscribed to) you must rebuild the column list to reflect the new structure of the source object(s) before you save the modified subscription.
The Columns properties show:
Source Name: the name of the source table column
Source Type: the data type for the source column, with the precision and scale shown in parenthesis if they are set.
Destination Name: the name of the destination column that corresponds to the source column. Subscriptions that perform incremental replication from a Db2 for i or Oracle source may have an additional, derived Relative Record Number (RRN) or ROWID column to help track source changes that need to be replicated to the destination. The General Group Properties option, Force Use of RRN, determines whether this column is added to the staging table that tracks changes for the subscription.
Destination Type: the data type for the destination column, with the precision and scale shown in parenthesis if they are set
Nulls: an X appears if the destination column allows null values
PKey: an X appears if the column is being used as a Primary Key. Note that the X appears only after the subscription has been created.
non-Critical: an X appears if the column has been designated as non-Critical - A change to the column will not trigger an update to the destination.
The total number of columns that will be replicated, including any derived columns, appears below the column properties.
If you are replicating to and from different types of database systems, be sure to understand how the different DBMS support data types. The topic Mapping Data Types provides more information about how data types are mapped from one DBMS to another. If the source database is SQL Server and the Data Replicator service is running on a system that supports double byte character sets (DBCS), see the topic Service Properties Subscription Tab for how you can increase the precision to better accommodate DBCS data.
The Columns dialog includes several buttons that allow you to perform the following actions.
Button |
Action |
Add Columns |
Displays the Add Columns dialog. Note that, in conformance with standard SQL capabilities, you can add a column that is already designated for replication. This causes the column to appear more than once in the destination table, providing that you specify a unique name for each column in the destination table. Unique column names within a table are required for correct operation. |
Delete Column |
Deletes the selected column(s) from the list of columns to be replicated. Use Shift-Click or Ctrl-Click to select multiple columns to delete at one time. Do not delete any columns that serve as the key columns (primary key or unique index) for the destination table. You choose which constraints and indexes on the source data that you want to replicate in the Indexes pane. |
Modify Column |
Displays the Column Properties dialog so you can specify the name and other characteristics of the destination column. Use Shift-Click or Ctrl-Click to select multiple columns to modify at one time. |
Insert Derived Column |
Displays the Column Properties dialog so you can specify the name and other characteristics of the destination column. |
Rebuild Column List |
Lists all the source table columns that are available for replication and returns destination column settings to their default values. Rebuilding the column list does not affect derived columns that have been defined. You must rebuild the column list if the source object is altered in a way that affects the subscription, such as if a source column that the subscription replicates is dropped. |
Merge with Source |
When creating a subscription, especially between unlike database systems, you may have performed extensive column mapping customization. This button displays the Merge Source Schema Results dialog, which allows you to preserve existing customizations if the table on the source system is altered (e.g. columns are added or dropped). |
You also can use the Move Selected Column control to change the order in which the replicated columns of a snapshot subscription appear in a new destination table. Select the column that you want to re-order, and then click the up or down arrow buttons to move the column to a different position. The order of columns in the list determines the order of columns for a new destination table if the snapshot subscription destination option is set to create the table when the subscription is saved or to re-create the table when a baseline replication is run. However, if a snapshot or incremental subscription is set to Use Existing Table on the Destination tab of the subscription properties, the existing table structure determines the column order in the destination table, regardless of column position specified in the Columns tab.
Clicking a heading button will sort the list in ascending sequence. Click the same heading again, and the list is resorted in a descending sequence.
This checkbox is applicable only for Db2 for i and Oracle sources.
If the source table does not contain a primary key or unique index, then the checkbox is automatically enabled and cannot be changed.
If the source table contains a primary key or unique index, then the checkbox is enabled and editable, with its default value being checked if the Force RRN setting in the IR Group is enabled.