Reading Cobol Flat File with different record types in BODS

dataservices job design
1

Hi All,

I have not processed Cobol Flat File in BODS before. My Cobol File has 3 different record types. Header and 2 Line item types. Both Header and Line items will repeat in a file.

For example in this case there are three record types.
H-Header, C-Channel and L-Language
H will have Name, Phone and State
C will have the Channel he subscribed
L will have the language he opted for

HBOB123NY
CNETFLIX
LENGLISH
HTOM123CA
CAMAZON
LSPANISH

For every person in the file, there will be a minimum of three records. (H,C & L). If the person has subscribed to multiple channels then he will have multiple Cs.

Have to read this file and load into a table as below using BODS.

The table should have the below 5 fields per record.
NAME PHONE STATE CHANNEL LANGUAGE
BOB 123 NY NETFLIX ENGLISH
TOM 456 CA AMAZON SPANISH

I tried using Cobol Copybook format in BODS. But then it is trying to build every possible combination using the three record types. So it is writing the table as below.

NAME PHONE STATE CHANNEL LANGUAGE
BOB 123 NY NETFLIX ENGLISH
BOB 123 NY NETFLIX SPANISH
BOB 123 NY AMAZON ENGLISH
BOB 123 NY AMAZON SPANISH
TOM 456 CA AMAZON ENGLISH
TOM 456 CA NETFLIX ENGLISH
TOM 456 CA AMAZON SPANISH
TOM 456 CA NETFLIX SPANISH
TOM 123 CA NETFLIX SPANISH
TOM 123 NY NETFLIX SPANISH
and the list goes on.

Appreciate any help on this.

Mike.ETLSDS (BOB member since 2018-06-01)

2

Hi.

This is going to be complex logic but doable in DS. I will give you a skeleton

  1. Initialize all records sequentially:
  1. Run the job in such a way that id for H record gets assigned to corresponding L and H records (it could be that you would need to run this in loop)
  1. Now again run the job to flatten this hierarchy.

Few temporary columns could be used to hold complete values and then convert them to proper attributes.

Hope this helps.

Regards. S

Shazin :india: (BOB member since 2011-07-19)