Writing a Custom Create Table Rule

When setting up a replication, it is often the case that the replication source table does not exist on the target table. Syniti Data Replication can be used to create the target table via the Create Target Table wizard which generates the SQL code for the target database environment. While it is possible to modify the way that the table is created in the target database by editing the SQL code in the wizard, this approach is not very convenient in situations where many target tables will be created with the same type of modification (adding a column, for example.) Syniti DR also provides a metadata-level feature to customize the way that target tables are created. Here is a brief overview of the steps involved.

Create a global script with a definition for a CreateTableRule class.
The global script template contains a new class definition:
```
   public class CreateTableRule : ICreateTableRule
```
Compile and save the script.
Edit the Target Connection Property CREATE TABLE Custom Rule to specify use of the new class in creating target tables. The default value for this property is ‘Automatic.’ After a custom rule has been defined in the global script, it can be selected as a value in this field, and used by default for all tables created for the connection. A second property, Default Mapping Rule, can be set if you have also created a mapping rule in the global script (see example below.)
In the Create Target Table wizard, or the Multiple Replications wizard select or deselect the new rule as needed for each table.

Step-by-Step Example

Below is a complete example that shows the steps to implement an automatic rule for target table creation together with a field mapping rule to map source to target fields after table creation.

In the Management Center Metadata Explorer, select the metadata for which you want to create a global script.
From the right mouse button menu, select Global Script.
In the Global Script Editor, edit the template for CreateTableRule.
Write a CreateTableAttribute definition to determine the function name that appears in the Target Connection Properties dialog, the Create Table wizard and the Multiple Replications wizard. The first string contains the text that will appear in drop-down menus. The second string contains text that will serve as a tooltip description of the function.

       public class CreateTableRule : ICreateTableRule
	   {
        [CreateTableRuleAttribute("Audit Table New", "Create columns for an audit replication")]
    
	    public bool  MyCustomAuditTable (ColumnClass aTargetFields)
	    {
		ColumnClass colClass;
		colClass = new ColumnClass();
		colClass.Name = "REC_ID";
		colClass.AllowNull = false;
		colClass.TypeName = "integer";
		colClass.PrimaryKeyPos = 1;
		aTargetFields.Insert(0, colClass); 

		colClass = new ColumnClass();
		colClass.Name = "TID";
		colClass.AllowNull = true;
		colClass.TypeName = "varchar";
		colClass.Size = 50;
		aTargetFields.Add(colClass);
 
		colClass = new ColumnClass();
		colClass.Name = "TTS";
		colClass.AllowNull = true;
		colClass.TypeName = "bigint";     
		aTargetFields.Add(colClass);
 
		colClass = new ColumnClass();
		colClass.Name = "UserID";
		colClass.AllowNull = true;
		colClass.TypeName = "varchar";
		colClass.Size = 20;
		aTargetFields.Add(colClass);
		return true;
	    }
      }

This function takes a list of ColumnClass objects as input. This is the ColumnClass definition:

		public class ColumnClass
		{
			public string Name;
			public int CCSID;
			public string TypeName;
			public int Size;
			public int Precision;
			public int Scale;
			public int PrimaryKeyPos;
			public bool AllowNull;
			public string Default;
		}

Whenever a target table is to be created, the function will pass columns in the format of a list of ColumnClass objects. Within the function, new columns can be appended at specified positions in relation to existing columns, or columns can be removed or column definitions changed.

In this example, a REC_ID integer column is added at position 0 (notice the Insert at index 0), specifying the PrimaryKeyPos = 1. PrimaryKeyPos is a value which, if other than 0, indicates that the field will be set as part of the primary key definition, at the specified position if not taken by other existing fields, or at the first available position.

Three more columns are added at the end of the columns list, TID, TTD and User_ID. For each new column, the datatype, size, precision and scale as requested by the database are specified. This means that every time a specific Create Table Rule is written, it is critical to consider the target database to which the rule is applied.

In the Global Script Editor, edit the MappingRule template to add a custom mapping rule that will be used to map columns of the above ‘audit’ tables:

   public class MappingRule : IMappingRule
   {
   [MappingRuleAttribute("Map For Audit Table", 
     "Match names considering the additional fields on audit tables")]
    public bool AuditTableMapping (bool bIsForth, string sSourceName, int iSourceOrdinal, 
			 string sSourceType, bool bIsSourcePrimaryKey, bool bIsSourceNullable, 
			 int iSourceSize, short sSourcePrecision, short sSourceScale, string sTargetName, 
                        int iTargetOrdinal, string sTargetType, bool bIsTargetPrimaryKey, bool bIsTargetNullable,
			 int iTargetSize, short sTargetPrecision, short sTargetScale, 
                        ref System.Text.StringBuilder sExpression)
   {
	if (String.Compare(sTargetName, "TTS", true) == 0)
       {
	  sExpression.Append("[TransactionTS]");
	  return true;
       }
       else if (String.Compare(sTargetName, "TID", true) == 0)
	{
	  sExpression.Append("[TransactionID]");
	  return true;
	}
	else if (String.Compare(sTargetName, "UserID", true) == 0)
	{
	   sExpression.Append("[UserID]");
	   return true;
	}
	else
          return String.Compare(sSourceName, sTargetName, true) == 0
    }
}

Save and compile the script.
In the Metadata Explorer, select the target connection for which you have defined the rules.
From the right mouse button menu, choose Connection Properties.
In the Target Connection Properties dialog, scroll to the CREATE TABLE Custom Rule property.
The default value of for the property is ‘Automatic’ which means create tables as they are on the source.
From the drop-down menu, select the ‘Audit Table New’ rule you created above.
In the Default Mapping Rule field, select ‘Map for Audit Table’ created above.
Click OK to apply the changes.

The custom functions are applied either when using the Create Target Table Wizard or when creating multiple replications in the Multiple Replications wizard.

Create Target Table Wizard

In the Create Target Table wizard, the Define columns screen displays a Create Table Rule drop-down menu, with the Audit Table New rule. The list of columns is modified as specified in the rule. You can change the value of Create Table Rule by selecting a different item from the menu.

In the Create New Replication wizard, the Mapping Info step initializes the mappings by adding the custom mapping rule defined in the script function.

You can modify the mapping either manually or selecting a different mapping function from the right most button in the toolbar.

Multiple Replications Wizard

Both the Create Table Rule and the Mapping Rule appear in the Set Replications screen, with the default values for the connection automatically selected.

The created tables and replications have the additional fields and custom mappings:

CreateTableRule with Table Name Change

Below is an example of a custom CreateTableRule that can access and modify a target table name.

public bool MyCustomTableRule(List<ColumnClass> aTargetFields, ref StringBuilder sTargetTableName)
{
ColumnClass colClass;
colClass = new ColumnClass();
colClass.Name = "REC_ID";
colClass.AllowNull = false;
colClass.TypeName = "integer";
colClass.PrimaryKeyPos = 1;
aTargetFields.Insert(0, colClass);
colClass = new ColumnClass();
colClass.Name = "TID";
colClass.AllowNull = true;
colClass.TypeName = "varchar";
colClass.Size = 50;
aTargetFields.Add(colClass);
colClass = new ColumnClass();
colClass.Name = "TTS";
colClass.AllowNull = true;
colClass.TypeName = "bigint";
aTargetFields.Add(colClass);
colClass = new ColumnClass();
colClass.Name = "UserID";
colClass.AllowNull = true;
colClass.TypeName = "varchar";
colClass.Size = 20;
aTargetFields.Add(colClass);
//Check and change target table name
if (sTargetTableName.ToString().ToLower() == "testtable")
   {
    sTargetTableName.Clear();
    sTargetTableName.Append("NewTestTable");
   }
return true;
}