DataStage Map

[akamdar]

I am trying to create a map where I compare a sequential file of about 10 million records with a ledger table in DB2 environment with a possible 4 million rows. The matching or comparing is done with about 12 keys(columns) from both sources. What is the best way to do this? I am new to Ascential.

I created a map where the file is directed to a hash file and to a transformer and the ledger table results are directed to the transformer where lookups are done on the 12 keys. I am not sure if this is efficient. The ledger table is a DB2 table and the sequential file is on the Unix server.

I had created a simplified test with a sequential file of about 3 records and the ledger table with 2 million rows to see how long it might take. It was running for a few hours and then I lost the server connection so there was a SQL fetch error.

Any ideas to this problem would be appreciated.

[ray.wurlod]

Welcome aboard.

Start with a specification of what you are planning to achieve.

Why was the connection lost? Is this likely to be repeated? If so you are going to need to design some restart logic. Or get "them" to put in a more reliable network.

You used the term "map". Are you using server job, or DataStage TX? (I ask because that term is more commonly found in TX, but you being new might not realize that. It's OK to use it in the server job context, but we need to be certain what edition you are using so as to give correct answers - or at least to avoid totally wrong ones).