You can use DataMigrator to build a flow that extracts data using a single pass of the data and loads multiple data targets. This can be useful if, for example, you need to create separate databases for each department or region in your enterprise and you can split the extracted data based on the value of a department code or region code. The term for splitting the data based on the value of a column in the extracted data is horizontal partitioning. Horizontal partitioning of the data is just one useful way to take advantage of the ability to load multiple data targets in DataMigrator.
Implement horizontal partitioning in DataMigrator by building a data flow using multiple data targets together with VALIDATE to extract data from a data source, and split the output to two different data targets based on the value of an input column. This can be accomplished in one pass of the data.
To do this, create a data flow with one or more data sources and multiple data targets. Each data target has its own VALIDATE statement based on the value of a particular column. When the data target is loaded, only records with the appropriate values are accepted; all other records are rejected.
In the following example, DMINV, an inventory table, is the data source, and PRODTYPE is the input column used to split the output into two data targets. Records containing a value of Analog for PRODTYPE are loaded into a data target named DMINVA. Records containing a value of Digital for PRODTYPE are loaded into a data target named DMINVD.
Refer to the sample data flow DMSPLIT for the complete example.
Note: DMSPLIT in the ibisamp directory writes to FOCUS/FDS data targets. If you installed the samples for a different adapter, you will need to open and resave DMSPLIT before running it.
In the DMC:
The Column Selection window opens.
Tip: To select all columns, click any cell and press Ctrl + A.
The Properties dialog box opens.
The Transformations window opens.
The Transformation Calculator opens with the Relational Expression tab selected.
When you have completed the validation, the Transformations window should appear as shown in the following image:
The Properties dialog box opens.
The Transformations window opens with the Relational Expression tab selected.
The Transformation Calculator opens.
When you have completed these steps, your data flow should appear as shown in the following image:
Two data targets are loaded, DMINVA with records containing a PRODTYPE of Analog and DMINVD with records containing a PRODTYPE of Digital.
To view the log, right-click DMXSPLIT in the navigation pane, select Logs, and then click Last Log. The log of the data flow, shown in the following image, indicates that 17 rows were processed, 7 rows loaded into DMINVA, and 10 rows loaded into DMINVD.
iWay Software |