Search found 30 matches

by Lucky
Tue Oct 03, 2006 8:36 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

1. Agree with you that there is no need to use Sorter Stage as all the records are being collected into an intermediate file which is then being used as a Source.

2. I should process one file at a time and the file will have one Header and one Trailer record.

3. Platform being used is Windows. So I should go for batch scripts to check for the characters 'T', 'H' & 'D' by using find /c /i "T" command but I am not sure if I can rely on the script.
by Lucky
Tue Oct 03, 2006 7:12 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

Ray,

Yes I meant Sort stage. I agree with your suggestion about the performance issues if we use a sort stage, but I am very new to data stage and still getting used it and still exploring the options available in DS. I have worked on Informatica so I am using that knowledge to create jobs in data stage. Having said that each tool is different in its own way, as you know.

Important thing is, the file will have only one Header and one Trailer records. So now I have to make sure when I process a file, it has to contain a header and trailer with detail records in between. Otherwise I should not process the file and should reject the entire file.

Can you please let me know how we can pre-process the file to check the trailer and headers.

Shane: Your pictorial representation is absolutely correct as far as processing the detail records are concerned except that there should be Sorter Stage before SeqFileDetail stage and Yes this gives me the desired result. If there is no match (match is being done on SRCE_FILE) then I write these records to the reject file. I donot need the header information except that I need to check if the file consists of Header and Trailer records. But I would need Trailer information for reconciling purposes as it consists the Summary information of the Detail records. The trailer record in a file will go Table2 and all the valid detail records to Table1.

Purpose of checking Header and Trailer records: To make sure all the detail records are in a single file.
by Lucky
Tue Oct 03, 2006 6:17 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

Hi Ray/Shane,

I have just finished working on this job.

The logic I have followed:

1. Read all the columns in a row as a string into a transformer ( as suggested in the earlier posts by Shane).
2. Use a stage variable to get the Trailer/ Details rec types (the first field in each string record is the an identifier which helps us in determining if the field is a Header/Detail/Trailer)
3. Now used two sorter transforms after transformer and hash file stage. Constraints used are:
a. RecType = 'D' for 1st Sorter
b. RecType = 'T' for 2nd Sorter
c. RecType = 'T' for Hash Stage
4. Join Hash Stage and Detail Sorter transform data by Source File (Passed as parameter to the job) and insert into Table1 (Constraint has been used to make sure that data will be inserted only if there is an entry in hash file).
5. I have used Sorter to accumulate data in the buffer rather than processing rows one at a time. This way I can avoid processing detail records before processing the Trailer record.

I hope I have made my ideas clear.
Please correct me if I have to follow a much better approach.
by Lucky
Tue Oct 03, 2006 5:07 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

Hi All,

There is some more logic to be implemented in this job.

1. Need to check if both Trailer (T) and Header (H) records are present in the file.
2. The Detail (D) records should be processed only if the file consists of both Header and Trailer records.
3. The Trailer will have date, time and ID fields (in addition to the summary fields) which will have to be concatenated as ID_date_time and this value has to be used as a separate field in each Detail (D) row.

Please give me ideas to achieve this solution.
by Lucky
Tue Oct 03, 2006 1:45 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

Hi Shane, Kirti,loveojha2,

Thanks a lot for your quick responses.

I have got the job to perform what I wanted.

Shane: I have simply followed your solution and included my logic and it worked straight away.

Thank you all for your time.
by Lucky
Tue Oct 03, 2006 12:22 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

I think I should have stressed more on the Reading Data from the file than the logic which follows after reading the data.

The issue I am facing now is reading data from the file with rows consisting of different number of fields.

H -> 7 Fields
D -> 4 Fields
T -> 8 Fields

Please let me know if there any suggestions for me to implement the logic.
by Lucky
Mon Oct 02, 2006 11:32 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

Hi,

The issue is when you take 5 columns into the transformer and if one of the records consists of less than 5 or more than 5 columns then data stage issues an error. I am looking for an option where data stage can read rows consisting different number of columns from a file.
by Lucky
Mon Oct 02, 2006 11:12 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: extract data from file different num of fields in each row
Replies: 22
Views: 4578

extract data from file different num of fields in each row

Hi,

I have a situation where I have to extract data from a file with rows in the file contain independent set of fields.

ex:

row1 -> rec_type, id,name,qualification

row2 -> rec_type,country,state,place,location

row3 -> rec_type,num_of_rows,file_date,file_time

Depending the rec_type, I have to load different tables. if rec_type= 'H' (header) then the row should go to Table1, if rec_type = 'D', the row should be inserted into Table2 and if rec_type = 'T' this row has to go to Table3.

H -> Header
D -> Detail
T -> Trailer

Please let me know what options should be used in Data Stage
by Lucky
Thu Sep 28, 2006 4:30 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

Thanks Craig and ArndW,

I m sure that there is no existing files in the directory, also i have corss checked and there is no file with that name to ask for replacing the existing file.


Regards,
by Lucky
Thu Sep 28, 2006 3:40 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

It is not prompting anything when i run from command line and working fine

Regards,
by Lucky
Thu Sep 28, 2006 2:37 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

Hi ArndW ,

I have searched c:\ and d:\ ,files are not extracted any where else,
And I m able to do copying file form one location to another location using ExecDOS from Before job subroutine
and also using the command "d:\progra~1\winzip\winzip32.exe -min -e c:\File_Name.zip c:\ |dir" from command line i m able to Unzipping the files, but if i m using the same command from the DS Job before job subroutine, DS job is just hanging and if kill Winzip32.exe process from task bar then i m getting the error message

Test11..BeforeJob (ExecDOS): Error when executing command: d:\progra~1\winzip\winzip32.exe -min -e
*** No output from command ***


Appriciate for u r resonse.

Regards
by Lucky
Wed Sep 27, 2006 10:27 pm
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

Hi ArndW, Ray , DSGuru2B
Thanks for u r responses,
I think i m giving complete path in the command, will u pl tell me where i have to give paths other than d:\progra~1\winzip\ , c:\ in "d:\progra~1\winzip\winzip32.exe -min -e c:\File_Name.zip c:\ |dir


Regards
by Lucky
Wed Sep 27, 2006 1:35 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

Hi,

I have the compatible command line support installed on the machine and as I have already mentioned, the command works when executed from the command prompt.

But I am not successfull in doing the same from DS.
by Lucky
Wed Sep 27, 2006 12:44 am
Forum: IBM<sup>®</sup> Infosphere DataStage Server Edition
Topic: Unziping file from Windows environment
Replies: 25
Views: 8944

Hello All,

Any idea about this problem???

We are using
DataStage version 7.1,
O/S: Windows 2000,
WinZIP version: 9.0 with command line support.

If anyone has ever tried this one, please let me know how to go about this problem.

I have tried all the options

1. Calling ExecDOS in before Job Routine by passing the command as the input value
2. Using the Execute command stage in a sequence job
3. Passing the command as parameter to the Filter Command in Sequential File Stage after checking the option "Stage uses filter commands"

But none of them worked for me...
by Lucky
Tue Sep 26, 2006 11:27 pm
Forum: IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
Topic: How to remove additional zeros in a record using Transformer
Replies: 12
Views: 10296

Hi,

As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type.

Please correct me if I am wrong.