Data base Look up in server and Parallel , Hash/data set loo
Posted: Wed Dec 03, 2003 5:32 pm
I have been going through some of the postings in this forum on look up in general and I am trying to make some conclusions. Please put in your expert advise.
If I have to do a database look up (eg. against a oracle table) in server job, there is no separate look up stage and the oracle stage can be directly used for look from transformer stage. Here each record is looked up with the database using an SQL statement issued to the database with the Key condition in WHERE clause. Is this correct? (or Is it like Ascential brings the look up table in memory and does the matching?)
Now If I have to do a database look up (eg. against a oracle table) in parallel job, there is a separate look up stage and the oracle stage can be linked to the look up stage for look up. Here there is no key matching issued to the database in the WHERE clause. The SQL which runs on database can return a set of records depending on the SQL and I guess the matching is done by the look up stage in memory of ETL server. Is this correct?
Now you can also download the table data to a data set or hash file and do a look up. What is the pros and cons of these approaches? Which is the best one in terms of performance? If I have a large database table (UDB) to be looked up in my ETL job, which is the best method I should go for?
T.I.A.
If I have to do a database look up (eg. against a oracle table) in server job, there is no separate look up stage and the oracle stage can be directly used for look from transformer stage. Here each record is looked up with the database using an SQL statement issued to the database with the Key condition in WHERE clause. Is this correct? (or Is it like Ascential brings the look up table in memory and does the matching?)
Now If I have to do a database look up (eg. against a oracle table) in parallel job, there is a separate look up stage and the oracle stage can be linked to the look up stage for look up. Here there is no key matching issued to the database in the WHERE clause. The SQL which runs on database can return a set of records depending on the SQL and I guess the matching is done by the look up stage in memory of ETL server. Is this correct?
Now you can also download the table data to a data set or hash file and do a look up. What is the pros and cons of these approaches? Which is the best one in terms of performance? If I have a large database table (UDB) to be looked up in my ETL job, which is the best method I should go for?
T.I.A.