Is DataStage Merge stage just a left outer join with multiple other sources?

Question

It appears that the DataStage Merge stage is just a left outer join with the Master being the "left" side and driving input. The other inputs are joined with the master when possible.

Is that all there is to it? What am I missing?

Answer 1

There are differences - you cannot really compare it to a left outer join. These are some characteristics for the MERGE stage:

• it can have a reject link - to be precise it can have one per secondary link as data from the secondary links that did not match get rejected (this is already a huge difference to join)

• data from the master link can be dropped (or ignored)

• data on the master link need to be duplicate free as data (from the secondray links) is consumed (destructive read)

Please refer to the documentation for more details - but I think it is clear that there are important differences.

Answer 2

merge stage have two main properties 1 .drop = means inner join - it will send the unmatched records to reject link and 2. keep = means left outer join it will keep the unmatched records in target

Answer 3

The Merge stage is a processing stage. It can have any number of input links, a single output link, and the same number of reject links as there are update input links. The Merge stage is one of three stages that join tables based on the values of key columns. The other two are:

 Join Stage

 Lookup Stage

The three stages differ mainly in the memory they use, the treatment of rows with unmatched keys, and their requirements for data being input (for example, whether it is sorted). The Merge stage combines a master data set with one or more update data sets. The columns from the records in the master and update data sets are merged so that the output record contains all the columns from the master record plus any additional columns from each update record that are required. A master record and an update record are merged only if both of them have the same values for the merge key column(s) that you specify. Merge key columns are one or more columns that exist in both the master and update records. Unlike Join stages and Lookup stages, the Merge stage allows you to specify several reject links.

Merge Stage