Overview
When replicating from a relational database to a flat file, Syniti Replicate allows you to replicate data in two different ways, or modes:
Refresh
A one-time complete replication from source table to CSV format flat file, according to replication settings and scripts. You can control the timing of the replication, identify the columns to be replicated and add scripts to transform data during replication.One-way mirroring
A continuous update of a replicated table to a CSV flat file based on changes to the source database that have been recorded in the database server log. You can define the replication settings to check the transaction log on the source database at regular intervals. Any changes found in the log would be applied to the target file.
Create a Target Connection for CSV Output
In the Metadata Explorer, choose Targets, then Add New Connection from the right mouse button.
In the Add Target Connection wizard, type a name for the connection and choose Files - CSV in the Database field.
In the Set Connection String screen Output Folder field, enter the path to a directory to contain the output files and schema.
Set properties to configure the format of the CSV output files as needed.
Set a value for the Add Transactional Info field. Choose Yes to include transactional information in the output for each record obtained from the database. This can be useful if replicating using mirroring from the source database.
Optionally set a value for the Use One File Per Group field. False generates a single output file for each replicated table. True generates one file for all replicated tables in a group.
You can leave the ExtendedProperties field blank.
Click Next to view the Select Tables screen.
If this is the first time you have created a File connection using the output folder defined above, the table display will be empty. You can add a representation of target tables after completing the wizard.Click Next to display the Actions screen,
Optionally choose to continue with creating replications once the wizard is complete.
Click Next to display the summary, then click Finish to create the connection.
The next step is to add target output representation to the Metadata Explorer. This will be represented as relational tables.
Now you can set up replications from whichever source connection you have defined to the target file.
Connection Properties
Required Information
Property Name | Description |
Output Folder | Path for the schema and output files. |
File Name Format | File name format for output files. Indicates if the output file will be named after the table or the replication name, and if it will contain a timestamp value and/or a session type description: ref – Refresh mir - Mirroring |
Use Column Names | Determinates if the output CSV files generated during replication will contain the list of columns names in the first row. |
Use Session Type File Extension | Generates files using the former extension defined by the session type. |
Add Transactional Info | Determinates if the output CSV files generated during replication will contain transactional info columns (operation type, transaction id, transaction timestamp, transaction user, etc.) and if these information will appear at the beginning of the record. |
Transactional Columns Prefix | Prefix to add to transactional fields (if selected). This option will allow using a custom format for the transactional fields to distinguish them from the data fields. |
Column Separator | Indicates the character used as column separator in the CSV files. |
Column Escape | Indicate the escape character used for the column separator. |
String Separator | Indicates the character used as string separator in the CSV files. |
String Escape | Indicates the escape character used for the string separator. |
Optional Information
Property Name | Description |
Use One File Per Group | Indicates if each replication group generates a common CSV file or one file each replication in the group. |
Extended Properties | Additional properties not listed in the grid. Enter the properties as a list of “property = value“ string separated by a comma (,). |
Limitations
Maximum character limit for a field is 255 characters.
Only CSV format is supported as a target.
Flat File Structure
Files used as a replication target have the following structure. The file has a .mir extension when used for mirroring and a .ref extension when used for snapshot or refresh replications.
Column | Value |
1 | Sequence number |
2 | Timestamp of transaction |
3 | User, if provided by the database |
4 | Table name |
5 | Operation code – one of I (Insert), U (Update) or D (Delete) |
6 | Previous primary key value for the database column. This is used for update operations to identify the correct record. |
7 | Next primary key value. This is used for update operations to identify the correct record in cases where the primary key is changed during the update operation. |
8 | Database column value |
Example File
Example Filename: corp_20150115T161123155064_1.mir
Data:
10,2015-01-22 15:32:39.000000,,"glb","I",,2,"test insert" 11,2015-01-22 15:32:40.000000,," glb ","D",10,, 12,2015-01-22 15:32:42.000000,," glb ","U",104,104,"my updated" 13,2015-01-22 15:32:44.000000,," glb ","U",1,12,"sd"