DSXchange

Hi,

I have a EBCDIC file and in that file i have different record indicators.

One column in the record varies based on the record indicator.
This coulmn include set of the columns specific to that record type.

For some record indicator you will have packed decimal filed for some it is not there.


So need a help how to read data and store it.


Example:

File Layout:

Detail records:
First column is record indicator(More than 900 record indicators)
Next three coulmn need to be extracted and populated to the target

Last two columns need to be extracted and populated as a single column in the target.


1,23,45,67,89,90
2,47,65,34,abvc,457.0(Packed decimal)

So on

That would be a job for the Complex Flat File stage. Have you tried that using that stage yet?

To add to what Craig has asked, using EBCDIC files and "packed decimal" fields leads me to believe that you are dealing with COBOL files, and if you can get the copybooks / FD for this file then DataStage and the Complex-Flat-File stage will do almost all of the parsing work for you.

I was making the same assumption, forgot to mention the FD part.

Ya true if the metadata is same then i can go ahead with Complex flat file but here the metadata is not same for all the record indicator.

Copy Book:

REC_IND
CUST_ID
SAL
CUST_NAME
DEP_DATA --> for this field the number of columns Varies for different record indicators.

for record indicator=2 or record type=2 the metadata for DEP_DATA is like

DEP_DATA includes
DEP_ID char(10)
DEP_NAME char(10)

for record indicator=3 or record type=3 the metadata for DEP_DATA is like

DEP_DATA includes
DEP_HIGH_SAL packed decimal
DEP_MANAGER char(10)

If it is less number of record types then we can handle but in my case we have record type from 1 to 999 so how to handle this scenerio.

Need help on this.

As has already been mentioned, the CFF stage allows you to use multiple record types.
To make your life easier and to spare you having to type in the definitions of each record type you should get the original copybooks or perhaps PL/1 definitions and let DataStage take care of automagically using those in your jobs.

If it is less number of
First Concern is :

We have '0' to '999' record types ,so defining this many definitions in the record layout in one job.Is it a right way to design?

Second Concern is :

While populating the data to target table i should concatenate the values extracted from DEP_DATA and load it.

It is difficult to answer those questions without knowing a lot more about the implementation.

If data volumes are high, you need to make sure that you only read your source one time, so there is no way around the big if-then-else or CASE or CFF Datatypes.

Since we don't know how DEP_DATA is stored or used it isn't possible to recommend any particular approach.

This is a serious design problem, and based on my experience you might not want to aim for a single solution. One possibility is to group record types logically, and have a separate job for each group. CFF gives you an output link for each record type. You could have dozens of links to manage in one job.

Yes, that means multiple reads of your single source, but you have trade-offs that you can't avoid.

You have 1000 different record layouts but is your file a Fixed block file or a variable block file?

Sorry arun. I was out of station...

Its a fixed width file

Okay. More questions.

1. What is your record length? From your examples it seems to be small (Unless you skipped some columns).

2. What can be the maximum volume of your file?

If these are smaller records and the volume is not very high, I will suggest you to go for a Server job, use a sequential file stage to read the file with all the static columns defined and converted to ASCII using the column level NLS mapping (From what I see you don't have a packed field outside of your DEP_DATA). Leave your DEP_DATA as a chunk, do not convert it.

Create a BASIC routine and pass your source records to it. Which should identify your record type and extract the neccessary columns from your DEP_DATA chunk and convert it as needed and concate and create your target record.

Yes! You would have to write a 1000 if-then-else.

Or maybe you can also split up the records if you can and make the job as multi instance and try running them parallely.