What is the Nr. of ROWS Limit for Dynamic Lookup

[roopalidoshi]

Hi,

I am using Power Center 6.1. I have mapping to load a FACT table.

1) In the mapping, I am using Dynamic Lookup (Lookup Data cache size = 2000000 and Lookup Index cache size = 1000000 (defaults)) to check if the record is already existing in the Target table.

2) If the record exists then I route to update the target row else if not exists in the target then I route to insert into target table.

3) The FACT table would contain 10 Million rows in the first load and it would grow every week by 1 Million rows.

4) The source oracle table can never give duplicate records while reading records in a particular load.

What is the guidelines for choosing type of lookup? I read that Dynamic look up should not be used when the Target table is too big. What is start limit for too big table? Should I use UNCACHED Lookup OR STATIC Lookup?

I am having locking problem while loading to target table (using TERADATA and TPUMP loader).

Please let me know if you have any information or experience about Dynamic lookup

Thanks a lot in advance.

-Roopali

________________________
Roopali Doshi
Hi,

I have mapping to load a FACT table.

1) In the mapping, I am using Dynamic Lookup (Lookup Data cache size = 2000000 and Lookup Index cache size = 1000000 (defaults)) to check if the record is already existing in the Target table.

2) If the record exists then I route to update the target row else if not exists in the target then I route to insert into target table.

3) The FACT table would contain 10 Million rows in the first load and it would grow every week by 1 Million rows.

4) The source oracle table can never give duplicate records while reading records in a particular load.

What is the guidelines for choosing type of lookup? I read that Dynamic look up should not be used when the Target table is too big. What is start limit for too big table? Should I use UNCACHED Lookup OR STATIC Lookup?

I am having locking problem while loading to target table (using TERADATA and TPUMP loader).

Please let me know if you have any information or experience about Dynamic lookup

Thanks a lot in advance.

-Roopali

________________________
Roopali Doshi
Menlo Worldwide Technologies- Vector Supply Chain Mgt.

 

[hubud]

Not sure about 6 but in 5 you can simply choose insert else update in the server manager session.

If this doesn't work in 6 then I would choose no cache which performs a normal search of the lkp table each time rather than cache the entire table.

Dynamic lookup caching sucks the whole table into memory and is updated as the cached table is updated during the load so that it is always up to date. For a large table it will eat up your memory.

cheers

simmo