Note on Map_Operation: It allows you to change the opcodes on your data. Before discussing this we should know about opcodes precisely
In DI we have 5 opcodes, Normal, Insert, Update, Delete and Discard(you’ll see this option in Map_Operaion only)
Normal: Indeed, it creates a new row in the target. The data which is coming from the source, is usually flagged as normal opcode
Insert: It does the same thing, 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’, those rows will be deleted from the target
Discard: If you select this option, those 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. Here i have taken normal opcode mainly because, query transform always takes normal rows as input and produces normal rows as output. However, will share the remaining opcodes in Table comparison and History Preserving transforms.
- 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 Normal as 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 have selected normal as delete and discarded rest all
- In the qry transform i have filtered out few records, where i want to delete those from the target.
- In the target data set, the above records will be deleted. Check the target data
- Now here in the 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.
- Now in the last and final flow, i have discarded all the opcodes. I do not want to load the data in to the target.