Options for Target Structure Treatment

Target structure options define, how source and target structures are compared and how XDM handles structural differences.

Introducing Target structure options

During the copy process, XDM inserts data into a target environment. In most cases, the target database objects already exist. XDM can deal with both existing and missing target database objects. In the case of existing target objects, XDM is able to handle differences in the structures between source and target. Target structure options are used to specify exactly how XDM handles the differences.

During the task work flow, XDM compares source and target environments by comparing the structure of every source object with the corresponding target object, if it exists. When comparing two tables, each column of the source table is compared with the corresponding target column by name, regardless of its position in the target table.

There are three possible results of the comparison:

  • Equal, if the target table exists and has the same structure as the source table,

  • Different, if the target table exists, but has a different structure to the source table, and

  • Missing if the target table does not exist.

For every object in the source an corresponding object in the target is assigned. Each source object is assigned to none or one target object and each target object is assigned to none or one source object. The object assignment is done by the name of the object. If mapping rules are configured for the task, the assignment uses the mapping rule to match source objects to target objects. For indexes, the mapping happens based on the structure, not the name. This is because many indexes are automatically created with the creation of constraints and have generated names. So, for indexes a matching index with the same column list is detected on the target side and assigned to the source index.

There are three parameters that define how the target environment deals with conflicts during the copy process: Drop option, Toleration mode and Incompatible action.

The most important option is Drop option. It defines whether tables should be dropped and recreated during copy process. XDM uses the Toleration mode in the compare process to decide whether a table can be copied. Incompatible action specifies XDM’s behavior when source and target tables are deemed to be incompatible.

Content is always copied where the toleration mode determines this is viable.

Drop Option

The drop option defines how XDM should treat the target environment. Depending on the chosen option, XDM will drop and create or recreate tables during the copying process. There are four possible values:

Drop all and recreate

XDM drops all existing tables in the target environment that correspond to a selected source table and recreates them. Missing tables will be created. In both cases the structure of the source table is copied. The options Toleration mode and Incompatible action are not used. XDM creates a one-to-one copy of the source environment.

This option might be inefficient for large environments where source and target table structures are mostly equal.
Only differing objects

XDM drops and recreates only tables which are deemed to be different in the compare process and adds the tables marked as missing. XDM uses the Toleration mode in the compare process to decide whether a table can be copied.

Drop nothing and create missing

XDM retains the tables marked as equal or different and only adds missing tables to the target environment. XDM uses the Toleration mode in the compare process to decide whether a table can be copied, and the Incompatible action to determine how to handle incompatible tables.

Drop nothing and create nothing

XDM retains all tables in the target environment. Missing tables will not be created in the target environment. Only the data for both corresponding and compatible tables is copied. XDM uses the Toleration mode in the compare process to decide whether a table can be copied, and the Incompatible action to determine how to handle incompatible tables.

Default setting is Drop nothing and create missing for table copying tasks and table from icebox tasks. The default setting for row level processor and row level from icebox tasks is Drop nothing and create nothing. And for structure compare tasks and structure compare with icebox task the default setting is Only differing objects.
Structure compare tasks and structure compare with icebox task do not have the option Drop nothing and create nothing. If you do not want to generate DDL in a structure compare task, turn off the option Generate DDL.
For various reasons it can be useful to generate DDL with or without double quotes. This can be controlled in the tasks which generate DDL by using of a parameter. See use double quote delimiter for details.
When creating new tables, XDM is not able to create the corresponding schema for that table. Some database systems, especially Db2 for LUW and for z/OS, create schemas automatically. Other systems, like Oracle or PostgreSQL, do not. For those systems, the schemas for the created tables must already exist in the target database. Otherwise, the copy process will fail.
When creating new tables in PostgreSQL with XDM, they will always be put in the default tablespace.

More information about the drop option can be found here

Toleration Mode

Toleration mode defines how XDM decides whether two tables have similar structures and thereby permit data to be copied from the source to the target environment. In some cases it is possible to copy from one column of a source table into a column of the target table with different type without loss of data. For example, it is possible to copy data from a CHAR(5) to a VARCHAR(5) column or from a DECIMAL(5,1) to a DECIMAL(7,2) column. There are three possible values for toleration mode:

Strict

The structures of source and target tables must be exactly equal. This means every column of the source table must exist in the target table and must have exactly the same data type as in the source environment. The target table must not contain any additional columns.

Prevent truncation

The structures of source and target tables can differ, but there must be no data loss. More precisely, using this toleration mode, the following situations will not lead to an incompatibility:

  • Source and target columns have similar, though not equal, data types (e.g. CHAR(5) in the source and VARCHAR(5) in the target).

  • The target column is larger (e.g. VARCHAR(5) in the source and VARCHAR(7) in the target).

  • The target has higher precision (e.g. DECIMAL(5,1) in the source and DECIMAL(7,2) in the target).

Furthermore, if a target table has one or more additional columns (i.e. columns which exist in a target table, but not in the corresponding source table) then this does not lead to incompatibility provided these columns allow NULL values or have a default value specified. If, however, a target table has an additional column which allows neither NULL values nor has a default value defined, then the source and target tables are deemed to be incompatible.

Allow truncation

The structures of source and target tables can differ and data may be truncated during the copy process. Specifically, two columns are only deemed to be incompatible, if the base data types are different. A BLOB column, for example, cannot be copied to a CHAR(n) column. However, XDM provides a number of automatic data transformations which permit copying a source column of one type to a target column of a different type. For instance, an INTEGER column can be copied to a CHAR(n), provided the target column is large enough to accept the converted integer value.

In certain situations XDM can handle the necessary data transformation automatically, such as when numeric values lose precision data. None-the-less the results can be different depending on the target column definition. For example, a source value defined as DECIMAL(5,3) will be handled differently if the target column is defined as DECIMAL(5,0) or INTEGER. In the first instance a source value of 1.755 would be rounded to a target value of 2, whereas in the second instance the target value would be truncated to 1.

If a target table has one or more additional columns, then these are treated the same way as for Prevent truncation.

The toleration mode Allow truncation means XDM will ignore the possible loss of data. It does not guarantee that the copy will succeed. For instance, a source column defined as CHAR(10) cannot automatically be copied to a target column defined as VARCHAR(7), because XDM does not know how the values should be truncated (at the front or at the back). For such situations a modification rule with scope Target should be defined. See Modification Rules for details.
Default setting is Prevent truncation for all tasks.

More information about the toleration mode can be found here

Incompatible Action:

Incompatible action defines the behavior of XDM during the copy process if source and target tables are deemed to be incompatible and cannot be dropped and recreated. This is only relevant for the drop options Drop nothing and create nothing and Drop nothing and create missing. There are two possible values for this option:

  • Skip table: XDM skips incompatible tables and only copies the data of compatible tables into the target environment.

  • Abort task: The task will be aborted with return code 8 (RC=8).

    No data is copied if Abort task is chosen.
Default setting is Abort task for all tasks.

More information about the incompatible action can be found here

XDM Comparison

XDM compares source and target tables during the task execution as follows:

  1. Firstly XDM checks the drop option. If Drop all and recreate is selected, XDM drops all existing tables and creates new ones. Otherwise, XDM compares the structure of corresponding source and target tables. If source and target are equal, there is nothing to do.

  2. If source and target table are different, XDM uses the toleration mode to determine whether they are compatible. If not, XDM checks the drop option. The drop option Only differing objects causes the target table to be dropped and recreated, otherwise XDM uses the incompatible action to skip the table or abort the task.

  3. If a source table is missing in the target environment, XDM checks the drop option. If it is set to Drop nothing and create nothing, XDM skips the table, otherwise XDM creates the missing table in the target environment.

To show, which options must set depending on the chosen drop option, please refer to the following Necessary settings for Drop options table.

Table 1. Necessary settings for Drop options
Drop option Toleration mode Incompatible action

Drop all and recreate objects

Only differing objects

Drop nothing and create missing

Drop nothing and create nothing

Examples

  1. The source table TEST is missing in the target environment. XDM will create it, unless the drop option is set to Drop nothing and create nothing. If the drop option is set to Drop nothing and create nothing, XDM will skip the table TEST in the task.

  2. In target table CUSTOMER there is a new column MAIL_ADDRESS containing an address which can be NULL. The column is missing in the source environment. The task uses the drop option Drop nothing and create missing. The incompatible action is Skip table. If toleration mode is Strict, the table will be skipped, otherwise the data will be copied, and the MAIL_ADDRESS value will be set to NULL in the copied rows.

  3. In table ADDRESS there is a column STREET defined as VARCHAR(30) in source environment and as VARCHAR(50) in target environment. All other columns have the same structure. The task uses the drop option Only differing objects. If the toleration mode is Prevent truncation or Allow truncation, the table is compatible and will be copied. Otherwise, the table will be dropped and recreated.

  4. In table DATA there is a column VALUE1 defined as DECIMAL(7,3) in source environment and as DECIMAL(6,2) in target environment. All other columns have the same structure. The task uses the drop option Drop nothing and create missing. The incompatible action is Skip. If the toleration mode is Allow truncation, the table is compatible and will be copied, the third decimal place will be truncated. Otherwise, the table will be skipped.