Record Logging

As you extract records from a data source and load them into a data target, a variety of record logging options are available. For example, you can log records that are rejected as duplicates, or records that are rejected due to format errors or when a field is truncated. The rejected records are written to a log file which can be used as input for another process. As this technique demonstrates, you can also write reports against these log files.

This technique is divided into two parts. In the first part, we create the log file; in the second, we view the records in the log file.

In the following example, we retrieve records from DMSALE, which contains sales representative information. The data source contains duplicate records, and these are rejected and written to a log file.

The location for the rejected records file is specified in the Record Logging tab of the target object, and we use the default, which will be dmxduplg_dupl. This allows us to easily display the records in the log file, which we do in the second part of the technique.

Refer to the sample data flow DMDUPLG for the complete example.


Top of page

Example: Creating a Log File and View Records in the Log File

In the DMC:

  1. Right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the workspace, with the SQL object displayed.
  2. Drag the data source object DMSALE from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  3. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

  4. Select SALESREP and click the arrow to add it to the Selected Columns list. Then add PLANT to the Selected Columns list.
  5. Click the Distinct check box, and click OK.

    This will create one row for each unique combination of SALESREP and PLANT.

  6. Save the data flow as DMXDUPLG.

    Note: When you save, you will get a message warning you that the flow is incomplete since you have not defined a target. Save the flow anyway.

  7. Right-click the workspace to the right of the SQL object and select Add Target, then New.
  8. In the Properties window, select the adapter you used for your sample procedures, the application directory you are using for your samples, and enter DMSREP as the synonym.
  9. Under Target Load Options, select Reject the record from the If the record exists drop-down menu.
  10. On the Flow tab, in the Tools group, click Properties. The Properties page opens.
  11. Under Record Logging, select the Duplicate Rejections check box, as shown in the following image.

    Note that the rejected duplicates will be written to dmxduplg_dupl.ftm in the application directory you select for the target.

  12. Click the X to close the Properties page.
  13. Right-click the DMSREP target object and select Target Transformations.

    The Transformations window opens.

  14. Check the Key column for SALESREP in the Target Columns list to designate a key column in the data target. Click OK.
  15. Save the data flow.
  16. On the Flow tab, in the Run group, click Run and select Submit from the drop-down menu.
  17. Right-click DMXDUPLG in the navigation pane and select Logs, then Last Log.

    The log of the data flow shows that 148 rows were processed, 126 rows were accepted and inserted into the data target, and 22 rows were rejected because of duplicates. The following image shows the log.

    Log file


Top of page

Example: Viewing Records in the Log File

After the DMXDUPS data flow created in the last example has run:

  1. Right-click the application directory that you specified in the Record Logging tab during the previous step, and select Refresh.
  2. Right-click the dmxduplg_dupl object from that application directory in the navigation pane and select Sample Data.

    The records that are displayed in the following image are the 22 duplicate records that were rejected and placed in the dmxduplg_dupl log file.

    Records Log

    Note: Your order may differ.


iWay Software