Objects Mapping and Possible Transforms

Transforms are primarily used where there is a need to reformat any statements. The transformation can be specified for either Capture or Apply. The Capture transformation (global transformation) affects all the Apply Processes (local transformation) consuming data from the Capture, while the Apply transformation affects the Apply process only.

Repstance supports transformation rules, which can be applied to the following objects:

• schema name
• table name
• column name
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute
• data type
• data type length
• data

Transformation rules can be triggered based on:

• schema name mask
• table name mask
• column name mask
• “identity” attribute (valid only for SQL Server DB)
• reference “identity” column (valid only for SQL Server DB)
• “primary key” attribute
• data type mask
• data type length

Transformation rules can be used to exclude the following objects:

• table
• column
• “identity” attribute (valid only for SQL Server DB)
• reference “identity” column (valid only for SQL Server DB)
• “primary key” attribute

Transformation rules can be used to add the following objects:

• column
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute

Transformation rules can be used to change the following objects:

• schema name
• table name
• column name
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute
• data type
• data type length
• data

Note – Data can be changed to the :

• constant value
• original value of another column from this table
• result of the database function execution. The original values of any columns from this table can be passed to the Function as parameters. The Function must exist in the Target Database, where the Apply Process is to be run.

There are normally three separate parts that comprise a Transformation. They are:

• id – the id, which is unique to each Transform has the purpose of determining the order in which the Transform is executed by the Process
• rule – specifies the object to be transformed and specifies how it is to be transformed
• description – a meaningful description of this Transformation

The syntax used is:

where:

The SearchCriteria consists of:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_mask – the mask is used to specify column name

The attr_spec is:

• PK – the specification of PK attribute, it can accept the following values:
• N– to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”, the possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• 2 – to process as reference “identity” column
• TYPE – name of the data type
• LEN – length of data type if applicable

TransformCriteria=schema_mask.table_mask.column_mask.[attr_spec]

data_specification=DATA="SQL_function_or_operation"

The TransformCriteria consists of:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_mask – the mask is used to specify column name

The attr_spec is:

• PK – the specification of PK attribute, it can accept the following values:
• N – to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”, the possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• 2 – to process as reference “identity” column
• TYPE – name of the data type
• LEN – length of data type if applicable
• DATA – this is the data that will result from the transformation specified on the captured data. This can be:
• predefined value in format "predefined_value"
• any operations based on the values of this column. In order to use the values found in the column the % needs to be used
• any operations based on the values of any columns in this table. In order to use the values of any column the following format is used "C:number_of_column". Table name can be passed as parameter as well. The format is "T:0".

To exclude columns the following syntax is used:

where:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name

To include any columns that were not represented in the source table the following syntax is used:

where:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_name – column name (this must be included and can not use the % symbol)
• TYPE – data type definition of the column to be created (this must be included and can not use the % symbol)
• LEN – length of data type, where necessary must be specified here
• PK – the specification of PK attribute. It accepts the following values:
• N – to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”. The possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• DATA – this is the data that will result from the transformation specified on the captured data. This can be:
• predefined value in format "predefined_value"
• any operations based on the values of this column. In order to use the values found in the column the % needs to be used
• any operations based on the values of any columns in this table. In order to use the values of any column the following format is used "C:number_of_column". Table name can be passed as parameter as well. The format is "T:0".

Examples

The following examples show how to use the Transformation Rules:

• For all tables located in the dbo schema change the schema name to report:

• Rename dbo.emp table to report.employees table:

• Exclude info column in the dbo.customer table from the replication:

• For any tables in the dbo schema having name starting with rep_ replace the prefix to the report_:

• For all tables in the dbo schema having ident column change the column name to id:

• Exclude from the replication any columns having name id and defined as primary key:

• For any tables having column id of int type handle this column as primary key. Any update and delete statements will be built to use id column as primary key or the part of composite primary key:

• Put the value of the desc column in the branch table into the uppercase:

• Add new column named tag of the nvarchar(100) type into the customer table contains predefined value rep:

• Add new column named name of the nvarchar(255) type to the person table contains concatenation of the firstname and lastname columns (in the table firstname has the position 2 and lastname has the position 3):

• For any tables in the dbo schema having name starting with rep_ and column id, which is defined as primary key and identity_insert, rename the table into the report schema and without rep_ prefix and use custom function report.generateID to reproduce the value of this column. This column will still be used as a primary key, but not as identity_insert:

Transformation Rules and Triggering Order

It is imperative that you understand that the order in which the rules are applied can significantly impact on the expected results. The following logic is used to handle rule order processing. The specified Capture Process will go and look for any possible data that meets the first of the configured rules, to apply. In the case where there is more than one rule to be processed, the rule which will “take precedence“ will be the rule which best fulfills the data found. If the objects found match the criteria given in the first rule to be processed any subsequent rules will be ignored because the previous criteria have been met.

The logic used in transformation does not necessarily follow the rules for human logic, in that, the least significant amount of data to be processed should be the first rule, and the rule which affects the largest amount of data should be the last rule. In this way the user will be able to apply the requirements of the transform in the desired way.

The following example demonstrates this program logic. Assume that you need to rename all tables in the dbo schema having a name starting with emp into the schema hr and keep the original table name. Another requirement is to rename the ident columns to be the id for any tables in the dbo schema, in the case where we have an employee table that contains the ident column, this matches both rules. The examples demonstrate the replication behavior depending on the order that the rules are presented:

• In this case you can’t expect the ident column to be renamed into id column together with renaming dbo schema in hr schema. The dboschema for employee table will be changed to hr schema but the ident column will not be changed:

• In this case the dbo schema will not be changed into the hr schema with renaming the ident column to the id column. The dbo schema for employee table will not be changed but the ident column will be changed to id:

• In this case the dbo schema for employee table will be changed and the ident column will be changed to id: