Maximum Record Limit For lookup stage

[Vignesh Shanmugam]

Hi all,

I have been told not to use Lookup stage if the Record counts Exceed by 1 Million by my senior datastage developers.

Can any body give an idea about the approximate count that could be handled in LookUp stage with out getting crashed.

Please provide your Input on this.

Currently i am using JOIN stage for 5 million records.But i should handle reject and Lookup failure conditions as well .Which is very easy in Lookup Stage.

Thanks In Advance

[DSShishya]

Lookup stage can handle any number of records. There is no limit to that.

A general threshold of 1 million records is set just to be on the safer side for performance reasons.

It mainly depends on the resource allocated to the server machine, memory in particular.

Talk to the admin and find out what is the memory availability for the server machine.

[Sreenivasulu]

Instead of join stage you can use Merge stage. It has reject links.

Regards
Sreeni

[kumar_s]

The limit is upto the Physical memory that is allocated to your server. It upto the amount of reference data that can be easily stored in the memory without overrun.

[ArndW]

The upper bound of the memory usable by the lookup stage is also dependant upon the platform implementation, see the threads on AIX (i.e. this thread). Using a number of "1 million records" is a bit misleading -> are the records 5 bytes wide or 500?

[nvdlrao]

Instead of join stage you can use Merge stage. It has reject links.

Regards
Sreeni

The Join Stage in DataStage will have the reject link from 8.2 on wards. Its the IBM Software Labs Tester mistake for Join Stage not having the reject link.

[ray.wurlod]

I disagree.

What does a "reject" mean in the context of a join?

When I join tables using SQL there are no rejects!

[nvdlrao]

I disagree.

What does a "reject" mean in the context of a join?

When I join tables using SQL there are no rejects! ...

The "reject" link in JOIN Stage means, the records which don't have the common key will go to reject link. Its the mistake of IBM Software Labs' Tester, for join not having the reject link. From 8.2 onwards DataStage will have the reject link in Join Stage.

[asorrell]

Actually, no - it wasn't a mistake. It was designed to provide the same exact functionality as SQL joins - none of which support a "reject" capability.

And at this point there is no such thing as 8.2, my understanding is that the next major release (currently in beta) may be called 8.5 when it ships

[nvdlrao]

Actually, no - it wasn't a mistake. It was designed to provide the same exact functionality as SQL joins - none of which support a "reject" capability.

And at this point there is no such thing as 8.2, my understanding is that the next major release (currently in beta) may be called 8.5 when it ships

I am not sure which is the next version of 8.1. But it is going to have the reject link for JOIN Stage from the next version onwards. Well what about the Merge Stage in DataStage? Did it designed to provide the same exact functionality as SQL Merge?. Does it not support reject link?

[ray.wurlod]

Merge is not SQL functionality - it implements a "master and update" mechanism.

[priyadarshikunal]

Merge statement does exists in Oracle and to capture rejects it uses when not matched then clause. I think it was introduced in Oracle 9i version. However i don't know whether they took the idea from ETL tools ot ETL tools tried to mimic the Merge statement of oracle.

Well, unless you know the exact timeline of first use of it in SQL or ETL tool, it seems like the question of Egg and Chicken.