load multiformat seq file
Moderators: chulett, rschirm, roy
load multiformat seq file
Hi all,
iam getting a sourefile with header,detail and trailer.its a fixed widthcolumn file.
Input format
FHDR 453 ssb ---------header recid(4) fileid(4) filename(3)
3452671 boston invest--Detail cusip(7)place(10)type(15)
FTLR 6028 0.989899---Trailer trrecid(4)totalfile(5)totalamt(10)
inheader always recid is FHDR
in trailer always recid is FTLR
output :oracle in one table
table format recid,fileid,filename,cusip,place,type,trrecid,totalfile,totalamt
Thanks
sri
iam getting a sourefile with header,detail and trailer.its a fixed widthcolumn file.
Input format
FHDR 453 ssb ---------header recid(4) fileid(4) filename(3)
3452671 boston invest--Detail cusip(7)place(10)type(15)
FTLR 6028 0.989899---Trailer trrecid(4)totalfile(5)totalamt(10)
inheader always recid is FHDR
in trailer always recid is FTLR
output :oracle in one table
table format recid,fileid,filename,cusip,place,type,trrecid,totalfile,totalamt
Thanks
sri
Sri,
One way of solving the problem is to replace the spaces using pipe delimiter '|' this can be achived using the replace() function and then having to manipulate using the functions such as ltrim() and rtrim() with the pipe delimiters specified to actually achieve the values into the different columns.
Probably the job that needs to be designed is
OCI Stage -->Transformer --> Sequential File --> Sequential File --> OCI Stage
Good Luck
Aditya.
One way of solving the problem is to replace the spaces using pipe delimiter '|' this can be achived using the replace() function and then having to manipulate using the functions such as ltrim() and rtrim() with the pipe delimiters specified to actually achieve the values into the different columns.
Probably the job that needs to be designed is
OCI Stage -->Transformer --> Sequential File --> Sequential File --> OCI Stage
Good Luck
Aditya.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's a server job solution that's not really relevant in the parallel job environment unless you want to use a UNIX shell script to modify the text file in advance.
In the parallel job the easiest way is to read the file with a Sequential File stage whose schema matches the detail record. The header and trailer will be transmitted as a raw record onto the rejects link, which can feed into other stage types to recover the information from those records. Check out the "datatype_from_raw" possibilities in the Modify stage, then maybe a Switch stage to isolate header from footer, and so on.
In the parallel job the easiest way is to read the file with a Sequential File stage whose schema matches the detail record. The header and trailer will be transmitted as a raw record onto the rejects link, which can feed into other stage types to recover the information from those records. Check out the "datatype_from_raw" possibilities in the Modify stage, then maybe a Switch stage to isolate header from footer, and so on.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You don't need a shell script - that was pointing out that Aditya's solution was not appropriate in a parallel job.
If you follow the remainder of my advice, you read the file using a Sequential File stage. In parallel jobs a Sequential File stage can have two output links, the one containing valid lines from the file (that match the schema), the other containing raw records that don't match the schema. In your case, the first link contains the detail records, the rejects link gets the header and trailer.
The rejects link ("rejects" is just a name) can nonethelessbe directed into other stage types for further processing. You have the header and trailer; you can process them however you like, simply by using the appropriate stage types.
However, the record is a raw record, so you will need to parse it into its constituent fields. There are quite a few techniques that you could use for this. My earlier post suggested a couple. Research the Parallel Job Developer's Guide and this forum for others.
If you follow the remainder of my advice, you read the file using a Sequential File stage. In parallel jobs a Sequential File stage can have two output links, the one containing valid lines from the file (that match the schema), the other containing raw records that don't match the schema. In your case, the first link contains the detail records, the rejects link gets the header and trailer.
The rejects link ("rejects" is just a name) can nonethelessbe directed into other stage types for further processing. You have the header and trailer; you can process them however you like, simply by using the appropriate stage types.
However, the record is a raw record, so you will need to parse it into its constituent fields. There are quite a few techniques that you could use for this. My earlier post suggested a couple. Research the Parallel Job Developer's Guide and this forum for others.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi
I did the same as ray said, apply the schema and i got the header and trailer inthe reject liknk, here the data is storting in the raw format(varbinary). now iam trying to modify the datatype from raw to string using the modify stage, so i can seperate the header and trailer.
There are no delimiters, its a fixed format header is having one format and trailer is having its own format .
the input format is
fhdr1.1 fhdr1.2
det1.1a det1.2a det1.3a
det1.1b.det1.2b det1.3b
trlr1.1 trlr1.2
fhdr2.1 fhdr2.2
det2.1a det2.2a det2.3a
det2.1b det2.2b det2.3b
trlr2.1 trlr2.2
the output is
fhdr1.1 fhdr1.2 det1.1a det1.2a det1.3a trlr1.1 trlr1.2
fhdr1.1 fhdr1.2 det1.1b.det1.2b det1.3b trlr1.1 trlr1.2
fhdr2.1 fhdr2.2 det2.1a det2.2a det2.3a trlr2.1 trlr2.2
fhdr2.1 fhdr2.2 det2.1b det2.2b det2.3b trlr2.1 trlr2.2
can u guys please help in this
thanks
Sri
I did the same as ray said, apply the schema and i got the header and trailer inthe reject liknk, here the data is storting in the raw format(varbinary). now iam trying to modify the datatype from raw to string using the modify stage, so i can seperate the header and trailer.
There are no delimiters, its a fixed format header is having one format and trailer is having its own format .
the input format is
fhdr1.1 fhdr1.2
det1.1a det1.2a det1.3a
det1.1b.det1.2b det1.3b
trlr1.1 trlr1.2
fhdr2.1 fhdr2.2
det2.1a det2.2a det2.3a
det2.1b det2.2b det2.3b
trlr2.1 trlr2.2
the output is
fhdr1.1 fhdr1.2 det1.1a det1.2a det1.3a trlr1.1 trlr1.2
fhdr1.1 fhdr1.2 det1.1b.det1.2b det1.3b trlr1.1 trlr1.2
fhdr2.1 fhdr2.2 det2.1a det2.2a det2.3a trlr2.1 trlr2.2
fhdr2.1 fhdr2.2 det2.1b det2.2b det2.3b trlr2.1 trlr2.2
can u guys please help in this
thanks
Sri
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This suggests you're not getting detail lines on the other output link. This, in turn, suggests that your schema for the other output link is not correct for the detail lines.
Get that right first.
Then use a Modify stage on the rejects link to convert the raw row to a string, and decompose that string appropriately using substring techniques, perhaps in a second Modify stage.
Get that right first.
Then use a Modify stage on the rejects link to convert the raw row to a string, and decompose that string appropriately using substring techniques, perhaps in a second Modify stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
i have a similar job. i took data from sequential reject link and passed it through a "Column Import Stage" with a Schema file that define the output columns all as varchar.
Works great. No Modify stage required.
btw: i got this idea from somewhere else on this forum...dont remember who.....you know who you are...Thx.
Works great. No Modify stage required.
btw: i got this idea from somewhere else on this forum...dont remember who.....you know who you are...Thx.
Lance Short
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."