DSXchange

Hello my friends..... :D

I have a seq file with fields KEY1 + KEY2. This file has several records.

For each of the records in the file, I need to access a SQL table with the exact value of KEY1 + KEY2.

How can I do this in a server job ?

I don't know how to use fields KEY1 + KEY2 of the file, and insert it into the SQL stage.

Any help, will be appreciated.

Load the sql table into a hashed file and perform a lookup based on those two keys. This way for each key present in the sequential file, you will get its corresponding record in the sql table.

Thanks, but the table may have about 3,500,000 records, and the file about 20,000 records.

The process is a daily process, and I think that this can take an important time to load the table.

Thanks anyway.

It will take time but wont crawl. Load only the columns you need into the hashed file.
OR
get a temp table created in the same schema, and pass a join sql to get the values. That will be much faster.

gpbarsky wrote:Thanks, but the table may have about 3,500,000 records, and the file about 20,000 records.

The process is a daily process, and I think that this can take an important time to load the table.

This is a common requirement and will not be slow - if done properly. For a file with 20,000 records, the most amount of records from the 3.5M record tagble will be just that - 20,000. No need to load everything every run.

Load only the records you need for each run. Load only the fields you actually need. Take a moment to farm the keys from your source file and built a work table with them. Join those keys to your large table when populating the hashed file. You then have only 'what you need' run over run.

Thanks, Craig.

We will try the option of loading a temp table, and then make a join.

Really, I love this forum because you answer me faster than the consultants here in Argentina.

You are so great.....

Have a nice day.