Processing a header and trailer textfile in Datastage

Example case

The input file in our scenario is a stream of records representing invoices. It is a text file in a header-trailer format and has the following structure:
- A header starts with a letter H which is followed by an invoice number, a customer number, a date and invoice currency
- Every item starts with a letter I which is followed by product ID, product age, quantity and net value
- And the trailer starts with a T and contains two values which fullfill the role of a checksum: the total number of invoice lines and total net value.

Header and trailer input textfile in Datastage:

Solution

Datastage job design which solves the problem of loading an extract structured into headers and items:

Detailed illustration of each component of the job

trsfGetKinds transformer - a type of record is extracted from each input line and assigned and written into a kind column

The transformer reads first character of each line to mark records and divide them into kinds:

trsfAssignHeaders transformer assigns invoice headers to each line of the input flow. Invoice header is a 8 character key which is extracted from the dataline using the following formula: L02.data_line[1,8]

A datastage transformer which assigns headers using a stage variable:

trsfReformat transformer splits the data flow into invoice headers and invoice lines. A InvoiceLineID stage variable is used to populate a sequence number for each invoice line. In order to correctly load net value, the ereplace function replaces commas with dots in this numeric field.

The following transformer splits the flow into headers and items and reformats records into a corresponding structure:

Target oracle tables with correctly loaded invoice headers and lines:

Comments

2008-11-05 04:12:34 by Marcelo Ramirez:
I really found it very useful for my research, I would like to thank you for all the efforts you do in order to give your readers knowlegde.

2008-11-12 08:19:52 by Paramesh:
This was really good. I am now very much confident with Data Stage also. Earlier i was working with Informatica.

2009-01-12 09:22:18 by Anupam Das:
How can datastage handle Chinese character?

2009-01-13 00:21:24 by ses:
thats really good info. i really appreciate ur efforts. thanks mate

2009-01-23 20:09:48 by Li:
Very nice tutorial. thank you for sharing your knowledge with other people.

2009-02-05 10:48:14 by Sagar:
The tutorial is really helpful in understanding datastage in a much better way.

2009-02-10 20:41:50 by Pallam Ramu:
The tutorial is very helpful for scratch level learners of Data stage.

2009-02-11 07:02:43 by Hari:
Wonderful datastage tutorial for a beginner. Appreciate your effort in putting this together. Thank you very much.

2009-03-02 23:41:40 by Victor:
the Loading of the invoices lines and headers is an excelente and ilustrative example. thanks

2009-03-05 08:36:33 by Biji Mathew:
This was really for a starter like me. It gave an overall idea about Datastage

2009-03-06 18:46:13 by Khan:
I would like to request keep posting new topics.
Can you please post on Join Stage PX

2009-04-06 17:37:31 by Ram:
It was indeed a very good & efficient refresher course to get started for the ETL developers.

2009-04-07 07:34:37 by RC:
Very nice tutorial. Thank you for sharing your knowledge.
We need more such tutorials... more on diffrent stages like merge, filter, remove duplicates, copy, modify, SCD, etc.
will be great help

2009-04-27 11:22:29 by mohankumar:
Excellent tutorial specially for beginners. I found it very useful when searching for case studies. Please continue the great work.

2009-07-01 08:49:27 by Irfan:
we know how much effort and time you invested in preparing this for beginners like me. I am really thankful to you.
Great tutorial.

2009-07-03 01:33:27 by Sri:
It is a very good tutorial. Just refreshall the basic needs.
Can you put some additional topics on the latest versions advantages, split function , surrogate keys applications and dynamic lookups as a continuit of this tutorial part II

2009-07-06 09:32:17 by Kranthi G:
It's very good site to know about etl tools and DS. As a ETL developer I was thank full to you for giving this type of Info.

2009-09-25 13:05:33 by Adrian Ratnayake:
Thanks you for taking the trouble to put this tutorial together. It has given me a feel for what DS looks like.
Additional tutorials covering stages such as merge, filter, remove duplicates, copy etc as well as general design principles, best practice and performance issues would would be appreciated.

2009-12-13 22:18:09 by EXCELLENT:
Good to had a great effort for providing of all ds stuff in this tutorial.
would be appreciated if it has ds px also.

2010-02-02 07:36:13 by vijendrra:
How can datastage handle Chinese characters?

2010-03-05 12:01:30 by Venkata Kali Akella:
Thanks a lot for this, it cleared loads of doubts and detailed about many terms which were like jargons in other references. Thanks a lot my friend

2010-08-14 00:03:28 by Parikshit Sharma:
Thanks a lot for your example.It really helped me to understand DS jobs from scratch.

2010-11-20 20:21:22 by tablaphy:
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material

2010-11-23 14:46:06 by Drereetetut:
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.

2012-01-20 08:42:09 by sahana patil:
It helped me a lot and I got lot of knowledge regarding datastage and other ETL tools

2012-05-08 05:42:16 by Arun Prakash S:
Really I am thanking you for this wonderful tutorial which gives great confidence on datastage tool. Its awesome.

2012-07-26 05:55:34 by Sheetal Yerte:
Thanks for the wonderful tutorial. I am totaly new to Datastage, I have not seen actual tool yet. Still am able to understand concept as well as how to design the job etc. Thank you very much.

2012-08-10 06:33:27 by Praveen Kumar G:
Thanks a lot for the tutorial with good illustrations. This is really helpful for getting an idea on Datastage.

Add a comment

Name Email (optional) Comment