Model flat file, distribution, and request - Connect_CDC - connect_cdc_mimix_share - Latest

Follow these steps when creating a flat file:

1. Create a source host and a source server.

   1. Set up the source host and server. For source servers, you can select any of Connect CDC's supported databases.

   2. Test the connection to the source host and source server.

   3. Install the metabase on the source.

   4. Make the tables available for the source server by choosing Refresh Available Tables from the context menu of the source server.

   For more information on the above steps, see the Getting Started Guide.

2. Name and save the model.

   Note: It is recommended to name and save the model at this time, so that at the time of distribution in the next step, the flat file’s default path is created with the name of the model.

   If the model name or the server name changes, you can change the path in the Target File Properties.

3. Create a target host.

   1. Create the target host the way you set it up normally.

   2. Test the connection to the target host.

   For more information, see the Getting Started Guide.

4. Create a File System target server.

   1. Right-click your host.

   2. Select New Server from the context menu options.

   3. In the Name box, input a name that is unique in the model.

   4. Select a DBMS type of File System.
      Note: Only one File System server is allowed per host.

      

      

      The modeling of the target server looks like the following example:

      

   5. Review the values in the Settings tab and make any necessary updates.

   Parameter	Description
   Use default location	When checked, sets the default location in the kernel directory to <install_location>/targetfiles/<modelname>/<File Server label name>. If unchecked, you must add the path of the file location box. This can be specified as a full path or a relative path (added from the kernel directory).
   File name	This option is only available when editing the properties of a mapped target file.
   Location	Displays the file location. This can be changed.
   Rollover criteria	Models the target file to roll over to a new operating system file based on specified events. The default setting is when a request stops. The other options are: Select a specific point in time, specified as hours:minutes:seconds, using a 24-hour clock (for example, 23:00 for 11:00 pm). You cannot specify a day, only the time during the day. After a specific interval, expressed in HH:MM:SS, where HH is any positive integer, and MM and SS are minutes and seconds. The hours may be greater than 24. After the file reaches a specific size in characters. After a file contains some number of rows. When the request stops, sends an alert notification is sent to the monitoring and control tools, such as Connect CDC MonCon, when the file rolls over, based on the set criteria. When the file rolls over, the format is: Target File name_YYYYMMDDHHmmSS_rev.csv where YYYY stands for the year, MM for the month, DD for the day, HH for hours, mm for minutes, SS for seconds, rev for the revision number.
   Character set	One of the following: Default–This is the operating system character set. UTF-8–Use this if the source table contains Unicode columns.
   Null String	The default is “~”. Connect CDC differentiates between null and empty strings. Connect CDC identifies an empty string as no character.
   Quote character	The default is a double quote ("). The quote character has the same requirements as the delimiter character.
   Field	The default field delimiter character is the comma. For the allowable set of characters see Valid character set for field and record.
   Record	The default record delimiter character is \n for Unix, \r\n for Windows. For the allowable set of characters see Valid character set for field and record.
   Include before values	When selected, Before and After values for all columns will be written to the File Server when an update statement is captured. The Before value entry in the sv_manip_type column will be B.
   Valid Character Set for Field and Record
   	You can specify multiple printable characters as delimiters. The default size value of the record delimiter is 10 and 20 in the case where escaped characters are used. At least one printable character and 2 for escaped character should be entered as a delimiter. Valid Values and Limitations Maximum printable characters allowed:10 Minimum printable characters allowed:1 Any upper and lower case letters Any numeric digits Any special characters from the following set:       `,~!@#$%^&*()-_++{}[]|;:”’<>./? Escaped characters ( backslash character “\”), followed by the characters from the following subset: t, n, \, 0, or r will be counted as 1 character. If you use a space you must enter \b. The binary zero is not allowed in the list of escaped characters. Note: You can mix all of the above characters as delimiters.

5. Create a flat file by distributing the source table to the File System server. Select source tables for distribution and create a mapping from a source database table to a target File System.

   The Distribute Tables dialog displays.

   A message displays that you have successfully distributed the tables.

   Connect CDC Director automatically generates a target file definition when the user distributes a source table to a File System server. The datatypes are identical to those of the source table. By default, all source columns are mapped to their counterparts in the file definition, excluding datatypes not supported for flat file replication. (Long character, long and short binary types are not supported.)

   The mapping is flagged as a journal mapping automatically, without user intervention. Target system variables shown in the table below are generated and mapped to the standard journal methods. For a list and description of all system variables refer to Use system variables.

   The modeling of the target flat file looks like the following example:

   The default path uses the model name and the file server name, although this can be changed in the Target File Properties. During model commit, Connect CDC Director verifies that this directory exists and creates the directory if it does not exist.

6. Optionally, right-click the source table to modify the source table Properties.

   • All expressions are allowed.

   • Gate conditions are allowed.

   • You can unmap any of the automatically mapped target columns whether the column derives from a source table column or a system method. You can also change their default methods.

   • You may add additional mappings from the source table to other database tables and files on other File System servers.

   • If you map two source tables with the same name from different sources to the same File System server, you must manually rename the second table to create a different name.

   Note: Journal mapping is always turned on, and you are not able to turn it off. This is shown on the Sending and Receiving Table Properties page Mapping tab.

7. Optionally, right-click the target file then select Properties to modify the target file Properties.

   This opens the Target File Properties page on the Basic tab where you can set the format of the output File System files:

   The Basic tab contains information on the Host, the file name, its location, the rollover criteria and information about the character set used.

8. Click the Columns tab to display the columns from the sending table and the added system variables.

9. Optionally, click the General tab to display any description you provide for the target file.

10. Create a new Distribution (for more information, see the Getting Started Guide).

11. Define the Request.

    1. Right-click a distribution, then select New Request>Replication in the menu that displays.

       Note: You can only write to a target file using a Replication request. Copy and Synchronization requests are not supported.

       The Replication Request Properties page displays.

    2. Set any path group or scheduling information on the Common tab (see the Getting Started Guide for more information).

    3. Click the Replication tab to display the Replication options.

    4. Set the following:

    Parameter	Description
    Transaction ErrorMode	Indicates the actions to take when an error occurs during application of a transaction to the receiving tables and files.
    File System target files	Enables you to delete or retain older FileSystem files on start of replication. Each time a request starts, a unique timestamp is assigned. Files created in an earlier request are deleted or retained based upon the timestamp. If you select delete, a flat file created in an earlier request is deleted, and a new file is created with the current date and time as part of its name. If you select retain, flat files created in earlier requests are retained, and a new file is created with the current date and time as part of its name.
    Collision Resolution	See Detect collisions  for more information.

12. Validate the model.

13. Commit the model.

    Each File System target file consists of multiple operating system files and has its own directory in which all the operating system files are written. Connect CDC Director verifies that this directory exists during model commit and creates the directory if it does not exist. The default directory is:

    .../kernel/targetfiles/<model>/<server>/<target file name>

    This can be changed in the Location field of the Target File Properties.

14. Start the request in a monitoring and control tool, such as Connect CDC MonCon.