Map Operation in BODS

See more in our ebook

Map Operation in Data Services Designer allows conversions between data manipulation operations. Using Map_Operation transform you can change operation codes on data sets to produce the desired output.

For example, if a row in the input data set has been updated in some previous operation in the data flow, you can use this transform to map the UPDATE operation to an INSERT. The result will be to convert UPDATE rows to INSERT rows to preserve the existing row in the target.

Data Services can push Map_Operation transforms to the source database.

Note: Map_Operation allows you to change the opcodes on your data. Before discussing this we should know about opcodes.

In Data Integrator, we have 5 opcodes, Normal, Insert, Update, Delete and Discard(you’ll see this option in Map_Operaion only)

Normal: It creates a new row in the target. The data which is coming from the source, is flagged as normal opcode by default. There is no flag for Normal mode data.

Insert: It does the same thing as Normal, it creates a new row in the target and the rows will be flagged as ‘I’ – Insert

Update: If the rows are flagged as ‘U’ , it overwrites an existing row in the target.

Delete: If the rows are flagged as ‘D’, these rows will be deleted from the target

Discard: If you select this option, these rows will not be loaded into the target.

Understanding Opcodes: Here is an example, in the below figure I’m using (normal to normal, normal to insert, normal to update, normal to delete) opcodes.

First, I have taken normal opcode mainly because, query transform always takes normal rows as input and produces normal rows as output.

In the first flow, i.e., MO_Normal-> i have selected Normal as Normal and discarded rest all opcodes.

This flow inserts all records in to the target which are coming from the source
In the second flow, MO_Insert-> I have selected normal as insert and discarded rest all opcodes-

It does the same thing, inserts all records in to target.
Have a glance on both the data sets before loading in to the target. You will see no opcode for Normal as Normal rows(1st flow), but you can see Insert opcode indicated as ‘I’ for Insert (2nd flow).

In the third flow, I want to update few records in the target-

Let’s say I want to update all the records whose DeptNo = 20.

Now, I have selected normal as update in the map_operation and discarded rest all-

Check the data, you can see the updated rows flagged as ‘U’

In the fourth flow, I want to delete some records from the target.
- Let’s say I want to delete rows whose DeptNo = 30. In the map_operation transform, I will select normal as delete and discarded rest all
In the query transform I have filtered out records with DeptNo 30, this means that no new data will be loaded with DeptNo 30 and opcode ‘DELETE’ will ensure that data which is already loaded with DeptNo 30 will be deleted.
you can see the data after the map_operation dataset along with delete opcode ‘D’-

In the target data set, the above records will be deleted. Check the target data-

Now in another sub-flow, I have inserted these deleted records in to another table. For this I have added one more Map_Operation and selected row type delete as insert-