Just wanted to throw this out there to see what is the best path to take in mapping a large flat file for the next few months before using proper ETL and DB2 table.

I got this 20million flat file which looks like this:

MONTH BAL_TYPE ACCT_TERM GL_NO AMOUNT
201203 FTP A123245 988765 100.00
201202 FTP A123245 988700 100.00
201203 ATP B345665 456234 200.00
201202 PTP B345665 456000 200.00


Business provided the requirement for a mapping document to group by "Product"

Mapping Table:
PRODUCT BAL_TYPE ACCT_TERM GL_NO
FIXED_MTG_5YRS FTP A12345 988765
FIXED_MTG_5YRS FTP A12345 988700
VARIAB_MTG_5YRS ATP B345665 456234
VARIAB_MTG_5YRS ATP B345665 456000

The mapping table got 250 different Products


Solution #1
Create Unique Key in the large flat file and mapping table called:

UNIQ_PROD/A50 = BAL_TYPE ||ACCT_TERM ||GL_NO;

Index the Unique Key in the Mapping table and Join to the Flat File.

Solution #2
Since the data is on mvs can we use SYSIN SORT CARD to merge mapping table to the flat file?

something like this:
Sort fields=(21,4,CH,A,29,2,PD,A)

I'm not an expert in using SORT CARD but wonder if anyone here have experience with it?

Just looking for the most optimal solution.

Any suggestion is welcome.

Thanks,

Lurker

This message has been edited. Last edited by: Kerry,

I would use #1, but with soft catenation (| rather than ||) in the Define for the compound key.

The compound field must be stored in the reference file, and indexed. You need not store it in the "large" file -- just define it, and issue a define-based Join to the reference file.