What is a Sparse Lookup in DataStage EE?

sami · Post by **sami** » Thu Feb 22, 2007 1:10 pm

What is a Sparse Lookup? Why and when can we use it?

Thanks

sudharma · Post by **sudharma** » Thu Feb 22, 2007 1:29 pm

Hi Sami,

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.

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 1:41 pm

As for the why and when, refer here.

novneet · Post by **novneet** » Fri Feb 23, 2007 7:02 am

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.

DSguru2B · Post by **DSguru2B** » Fri Feb 23, 2007 7:46 am

The post that I embeded has comments from our very best, Ray and Vincent. Adhere to their warning on when to use it, else there will be bottlenecks

pavan007 · Post by **pavan007** » Mon Feb 26, 2007 3:19 pm

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.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:06 pm

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.

dsdoubt · Post by **dsdoubt** » Mon Feb 26, 2007 5:07 pm

Due to some reason, I could not locate the option to change the Normal lookup into Sparse.

Could you pls help me on locating it.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 26, 2007 8:07 pm

Where (in which stage type) did you look?

vick · Post by **vick** » Mon Feb 26, 2007 8:09 pm

if you open the Oracle_Enterprise stage you can find it in "Look Up Type" drop-down.

The "Look Up Type" dro-down has 2 options "NORMAL" & "SPARSE"

HTH

vick

ray.wurlod · Post by **ray.wurlod** » Mon Feb 26, 2007 8:13 pm

I doubt that the OP is using an Oracle Enterprise stage, as DB2 was specifically mentioned.

kumar_s · Post by **kumar_s** » Tue Feb 27, 2007 12:39 am

Even the DB2 has the same option to convert to 'SPARSE'.

novneet · Post by **novneet** » Tue Feb 27, 2007 11:02 pm

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).

ray.wurlod · Post by **ray.wurlod** » Wed Feb 28, 2007 12:10 am

What if someone other than u wants to perform a sparse lookup?

novneet · Post by **novneet** » Wed Feb 28, 2007 12:58 am

I didn't got the exact meaning? Can you be more precise and descriptive!

DSXchange

What is a Sparse Lookup in DataStage EE?

What is a Sparse Lookup in DataStage EE?

More to explain

Sparse Lookup in DataStage EE?

way it is possible