It can happen that there are differences between the column structure of a table in the source schema and its equivalent in the target schema. By default, the Load Manager rule assumes that the structure of the tables in the source and target schemas are identical. In other words, it is assumed that the columns in both tables have the same name and also have the same datatype. If this is not the case, the data being transferred must be adjusted during the copy process otherwise errors will be generated.
The transformation engine is a tool in the Load Manager rule which can manipulate the column information as necessary. The transformation engine modifies the data stream as it is read from the source table and inserted into the target table. It can remove data columns, add data items and perform function based modifications such as TO_STRING or TO_DATE on the existing values.
The following page discusses the components and usage of the transformation engine for a table. There is also a useful tutorial on the subject located here: [Using the Transformation Engine Tutorial (local) (internet)]
Activating the Transformation Engine
The Transformation Engine Button on the Load Manager Rule Form
Each table included in the load process can have a transformation associated with it. The transformation engine for that table is activated by pressing the elipsis button (...) in the Transformation Engine column on the Edit Load Manager Rule form. The screen shot above displays part of the Edit Load Manager rule form and shows the transformation engine button for each table. Note that tables which have no transformation operations specified for them have a value of Default in the transformation engine column, while those which have data modifications applied will change to a value of Custom.
Configuring the Transformation Engine
The Transformation Engine Form for a Table
Once the transformation engine for a table has been activated, the Transformation Engine form will display - as shown in the screens shot above. The Transformation engine contains two columns. The column on the left represents the columns in the target table and the column on the right contains the names of the columns in the source table. It is assumed, by default, that these columns are identical in name and datatype. If the column structure of the table is not identical, then the data may need to be transformed during the load process. The following types of transformation are possible
INSERT INTO TARGET.INVOICE (col1, col1, INVOICE_NO) select col1, col2, TO_NUMBER(INVOICE_NO) from SOURCE.INVOICE;
Important Note: The transformation engine for a table can be reset to the default value by pressing the Set to Defaults button on the Transformation Engine form. In particular, this button will restore any columns in the target table which were removed from the load process and make the assumption that the source table structure is identical to that of the source. It is important to be aware that the restored target column information will simply be that which is currently known to the Rule Controller. Pressing the Set to Defaults button does not cause the Transformation Engine form to contact the target schema and refresh the table structure - it only retrieves what the structure the Rule Controller thinks is in the target schema. If a full refresh of the table structure is required, then use the tools on the Edit Rule Controller form Options tab to refresh the table structure in the Rule Controller first.