IBM Infosphere Datastage Merge Stage is same as Oracle Merge SQL statement

 

If we know how Oracle merge statement works, its easy to work with Datastage merge as both do similar things though not exactly same.

If we want to update/insert records to table A based on selection criteria from multiple tables its better to write ORACLE MERGE SQL instead of individual UPDATE/INSERT statements. But this may have performance impact if we are writing ORACLE Merge.

On other hand, DataStage MERGE stage will only do the update.

MERGE works with the concept that we have 1 master table and we want to update master table based on the selection criteria from update tables using key fields.

Below table compares ORACLE MERGE and DataStage MERGE assuming DataStage merge is processing tables (DataStage Merge could also process files, datasets)

 

	ORACLE MERGE	DATASTAGE MERGE
Master Table	Can have only one master table	Can have only one master table
Update Table	Can have many update tables	Can have many update tables
Output record count	ORACLE MERGE can do INSERT, UPDATE and DELETE.. Therefore record count could increate, decrease or same after merge operation	will remain same if unmatched master records are kept otherwise will be less if unmatched master records are dropped
Output column count	Oracle MERGE can not add columns to master table therefore column count will remain unchanged after merge operation. Note: both master table and target tables are same in ORACLE Merge	master and target tables are different therefore new columns from update tables can be added
Can we capture unmatched update tables records	No	Yes, using the reject links
What happens to records unmatched in master tables	will remain unchanged and delivered to target	can be kept in master or dropped from master table
When matched	we can perform insert, update or delete	only update
when unmatched	we can perform insert, update or delete	we can only drop or keep records from master table
deterministic?	cannot update the same row of the target table multiple times in the same MERGE statement	Same row can be updated multiple time is master record is matched with different update table records
Duplicates?	No, if duplicate records in master or update tables present for the match fields (key fields) then oracle thinks its updating same record again and again so it will fail	No, duplicates must be removed from master and update tables before processing merge