| Product: | Syniti Data Replication, DBMoto |
| Version: | All |
| ID: | 1647 |
| Summary: | How to use Syniti DR/DBMoto rules to create a log of changes to a database |
This article describes how to use Syniti DR/DBMoto to maintain a log table of changes to a database. The source table is the table that you want to audit, and the target table is the log of all changes that occurred. Replication is occurring between source and target, but with the sole purpose of keeping an external log of changes to the source table.
The target (log) table contains all the fields of the source table, plus additional fields for a timestamp and a value that represents the type of operation that occurred (Insert, Update or Delete). The desired outcome is:
- If a record is added in the source table, insert a record in the target (log) table and set fields in the table with a time stamp and the type of action performed (Insert)
- If a record is updated in the source table, insert a record in the target (log) table and set fields in the target table with a time stamp and the type of action performed (update)
- If a record is deleted from the source table, insert a record in the target (log) table and set fields in the target table with a time stamp and the type of action performed (delete)
The example below assumes that your source table is defined in IBM Db2 for i, and the target (log) table is in Microsoft SQL Server. The replication type is mirroring and the tables are as follows.
|
IBM DB2 for i Source Table CUSTOMERS |
Microsoft SQL Server Audit Target Table REC_ID integer identity, |
Note that the target, or audit, table has 5 additional fields, an auto-incremental ID field, a transaction ID field, a timestamp field, a UserID field and an Operation field:
The steps to implement the audit table are:
- Write a rule to automatically add audit columns to the tables using the CreateTableRule. In the rule definition, include the new audit columns for the table.
- Write a function that contains the CreateTableAttribute attribute to instruct DBMoto to use the function as a new Create Table Rule.
- Create a Custom Mapping Rule that maps the new columns in these ‘audit’ tables:
- Open the Target Connection Properties for the target database to contain the audit tables.
- Set the values of the CREATE TABLE Custom Rule and Default Mapping Rule fields to the rules you have just created.
After these steps have been completed, when you use the Create Target Table Wizard or Multiple Replications Wizard with the target connection that you modified, the new rules will be set for use in the wizards and dialogs.
See below for detailed steps on the process.
1. Create a rule to automatically add audit columns
The Global Script module contains a new class definition:
public class CreateTableRule : ICreateTableRule.
Define a new Create Table Rule as in the example below.
public class CreateTableRule : ICreateTableRule {
[CreateTableRuleAttribute("Audit Table New", "Create columns for an audit replication")]
public bool MyCustomAuditTable (List<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);
colClass = new ColumnClass();
colClass.Name = "Operation";
colClass.AllowNull = true;
colClass.TypeName = "varchar";
colClass.Size = 5;
aTargetFields.Add(colClass);
return true;
}
}
2. Create a function to point to new Create Table Rule
Use the attribute CreateTableAttribute to instruct Syniti DR/DBMoto to use the new Create Table Rule. Here is its definition:
/// <summary>
/// Attribute defined for create table rules
/// Custom mapping rules will be defined in the following formats:
/// Simple format (list of ColumnClass objects):
/// [VB]
/// <CreateTableRuleAttribute("Create Custom", "Create table considering specific custom fields")> _
/// Public Function MyCustomCreateTable (ByRef aTargetFields As List<ColumnClass>) As Boolean
/// ...
/// End Function
/// </summary>
The function takes a list of ColumnClass objects as input. This is the ColumnClass definition:
/// <summary>
/// ColumnClass class to be used as a parameter in the CreateTableRule
/// </summary>
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 (if defined and selected in the specific dialog) is invoked, passing a list of columns in the format of a list of ColumnClass objects. Within the function, the user can append new columns at specified positions in relation to the existing columns; or even remove columns or change their definition.
In the example here, 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 should 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, specify the datatype, size, precision and scale as requested by the database. This means that every time a specific Create Table Rule is added, the target database requirements should be considered.
3. Create a Custom Mapping Rule to map the columns of the ‘audit’ tables
public class MappingRule : IMappingRule
{
[MappingRuleAttribute("Map For Audit Table", "Match names considering the additional fields on audit tables", false)]
public bool AuditTableMapping (bool bIsForth, ColumnClass[] sourceColumns, ColumnClass[] targetColumns)
{
foreach (ColumnClass trgCol in targetColumns)
{
if (String.Compare(trgCol.Name, "TTS", true) == 0)
trgCol.Mapping.Expression = "((DateTime)[!TransactionTS]).Ticks";
else if (String.Compare(trgCol.Name, "TID", true) == 0)
trgCol.Mapping.Expression = "[!TransactionID]";
else if (String.Compare(trgCol.Name, "UserID", true) == 0)
trgCol.Mapping.Expression = "[!UserID]";
else if (String.Compare(trgCol.Name, "Operation", true) == 0)
trgCol.Mapping.UseUnmapped = true;
else
{
// Look for source column matching the name
foreach (ColumnClass srcCol in sourceColumns)
if (String.Compare(srcCol.Name, trgCol.Name, true) == 0)
{
trgCol.Mapping.MappedTo = srcCol.Name;
break;
}
}
}
}
return true;
}
}
4. Set the Target Connection Properties for the Create Table Rule
- In the Metadata Explorer, right click on the Target Connection and choose Properties.
- Set the properties below:
- CREATE TABLE Custom Rule: Select the create table rule you just created (in this example, ‘Audit Table New’. This property allows you to select the create custom rule defined in the global script. The default value of this table is ‘Automatic’ which means create tables as they are on the source.
- Default Mapping Rule: Select the mapping rule you just created. This sets the default mapping rule that will be used when creating a replication using this connection as target.
- Click OK to save the properties and close the dialog.
5. Create Replications using the new rules
Once the configuration is complete, the custom functions can be used either when creating a single replication with the Create Target Table/Replication Wizard or when creating multiple replications with the Multiple Replications Wizard.
Create Target Table Wizard
Source Table:
The ‘Define columns’ step has a new Create Table Rule drop-down list, with the new rule (Audit Table New) selected by default. Note that the list of columns shown in the wizard screen displays the additional columns requested for auditing. You can edit the table structure by choosing a different selection from the list.
Create Replication Wizard
The Mapping Info step initializes the mappings by adding the custom mapping rules defined in the script function. You can modify the mapping either manually or selecting a different mapping function from the rightmost button in the toolbar.
Multiple Replication Wizard
Both the Create Table Rule and the Mapping Rule appear in the Set Replications step, with the default values for the connection automatically selected. The created tables and replications at the end include the additional fields and custom mappings.
