this is what i have found in help. hope this can hint you
Lookup Type. Where the DB2 stage is connected to a Lookup stage via a reference link, this property specifies whether the DB2 stage will provide data for an in-memory look up (Lookup Type = Normal) or whether the lookup will access the database directly (Lookup Type = Sparse). If the Lookup Type is Normal, the Lookup stage can have multiple reference links. If the Lookup Type is Sparse, the Lookup stage can only have one reference link.
Sparse lookup is like firing one-by-one query on the DB table to fetcht the result.
The select fields include all the fields that we are fetching from the lookup table and where field and value include the metching field and the value for the metching input fields.
I hope this clerifies the issue.
When you have large amount of data comming from source its better to use normal lookup and if you are having incremental comming better go for sparse so that the performance will be good and when you use sparse look up use ORCHESTRATE.Key_field_name= Key_field_name. in where condition.
As recommended in document, go for sparse in 1:100 ratio. That is if you have input records 100 times less that the lookup data. In other way, very large data to be loaded into physical memory where as fewer times of hits are required to the lookup table.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
The only way a the sparse lookup can be performed is that you connect the DB2 stage dirctly to the lookup(without any other stage in between DB2 and lookup).
Last edited by novneet on Wed Feb 28, 2007 2:16 am, edited 1 time in total.