Differences between Join, Merge and Lookup Stages.

Abhijeet1980 · Post by **Abhijeet1980** » Tue Dec 26, 2006 7:29 am

Dear All,

Can anyone outline the differences between Join, Merge and Lookup Stages ?

I've read a few forum messages regarding Join, Merge and Lookup, but have found no satisfactory answers.

Senior members: Pls do respond....

chulett · Post by **chulett** » Tue Dec 26, 2006 7:42 am

Have you tried... oh, I don't know... reading the Parallel Job Developer's Guide pdf? Each stage has its own lovely little chapter in there.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 26, 2006 3:17 pm

If you attend the IBM DataStage Essentials class there is one page in the student guide that does attempt a summary of the differences between these three stage types.

How about you make your own summary, based on reading the manual, and we can comment on that? You see, different things are important to different people - you might be interested in functionality, while someone else might be interested in resource consumption. Someone else might just need others to supply an answer to an interview question, which is something we try to avoid doing here.

kumar_s · Post by **kumar_s** » Tue Dec 26, 2006 5:38 pm

Code: Select all

                        Join            Lookup                   Merge

Description            RDBMS-style         Source and lookup     Master table and one
                       Relational tables   table in RAM          or more update tables

Number and names       2 or more inputs    1 source and N lookup 1 master tables and N
Of inputs                                  tables                update tables

Memory usage          Light                Heavy                 Light

Handling of           Ok, produces a       OK                    Warning given
duplicates in         Cross-product                              Duplicates will be an
primary input                                                    unmatched primary.

Handling of           Ok, produces a       Warning given. The    Ok only when N =1
duplicates in         Cross-product        second lookup table
secondary input                            entry is ignored.

Options on
Unmatched             None                 None                  Capture in reject sets.
secondary

On match, secondary   reusable             reusable             reusable
entriesare

Number of outputs     1                    1 output and         1 output and 1 reject for
                                           optionaly 1 reject   each update table


Captured in reject    Does not apply       Unmatched primary    Unmatched Secondary entries
Set                                        entries

DSguru2B · Post by **DSguru2B** » Tue Dec 26, 2006 5:42 pm

Good going Kumar. Great post. Everything in a nice table like format. We like tables, we are dw guys after all

kumar_s · Post by **kumar_s** » Tue Dec 26, 2006 5:47 pm

True, aligning to this format took a long. Atleast once.

narasimha · Post by **narasimha** » Tue Dec 26, 2006 5:50 pm

Nice effort Kumar!
Is this from the IBM DataStage Essentials class or did you compile it yourself? Thanks for sharing it.

kumar_s · Post by **kumar_s** » Tue Dec 26, 2006 5:57 pm

I have posted it already, so I guess I might have taken it from Orchestrate manuals.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 26, 2006 6:00 pm

It's quite like the table in the DataStage Essentials class, though missing the row that indicates whether sorted inputs are required.

kumar_s · Post by **kumar_s** » Tue Dec 26, 2006 6:04 pm

Ray you shall add you valuable inputs to this thread, which will be useful for those who have not access to DataStage Essentials class.

narasimha · Post by **narasimha** » Tue Dec 26, 2006 6:13 pm

kumar_s wrote:I have posted it already, so I guess I might have taken it from Orchestrate manuals.

No sweat, Just appreciations for your efforts

ray.wurlod · Post by **ray.wurlod** » Tue Dec 26, 2006 6:21 pm

kumar_s wrote:Ray you shall add you valuable inputs to this thread, which will be useful for those who have not access to DataStage Essentials class.

Everyone has access to IBM training, in exchange for money.

(The original post still smells like an interview question to me.)

Abhijeet1980 · Post by **Abhijeet1980** » Tue Jan 02, 2007 6:50 am

Thanx very much for sharing wonderful information with me.

The comparision was really wonderful.

Regards
Abhijit