All,
I am attempting to load a flat file into an Oracle table where there are no delimiters for the data. THe job is being ported over from Server edition, which was easy as the transformer had the substring function, which allowed me to break the one long string into columns based on position. However, it seems to be SLOW when moved over. I'd like to try it using a parallel job, just to see if performance is any better (yes, it is a sequential file, but still wondering if the overall performance of Parallel edition plus the Oracle Enterprise stage might be better).
However, the tranformer in Enterprise edition no longer has the substring function, and none of the other statges seems to be able to accomplish what I want. How would you go about doing this in Parallel edition?
Thanks!
Best Way to Load flat file with no delimiters
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
the transformer in PX has the same set of string functions as Server. But if you have fixed width columns with no separators it would make more sense to declare the columns appropriately in the sequential file stage and save reconverting them in a transform stage.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
That's the thing, I tried to look, but the transformer in Server seems to have a substring function, which allows you to give the column name, then the start position and the length. When I tried that same syntax in the Parallel edition transformer, it gave me an error.ArndW wrote:the transformer in PX has the same set of string functions as Server. But if you have fixed width columns with no separators it would make more sense to declare the columns appropriately in the sequential file stage and save reconverting them in a transform stage.
I would declared the coluns, but some of the transofrms overlap. so for example, position 1-10 might b job number, but 1-5 might be client number. I'd have to break it up in the read, then run the transforms again, and I still can't do a substring (there's a "Left" and "Right' functions), so I'd be stiuck with the same issue again.
-
- Participant
- Posts: 57
- Joined: Sat Jun 09, 2007 1:14 am
- Location: chicago
Hi,
Using the left and right string functions in combination,we can reach the exact string position/extract that bit of required string
and I have a question here,
declare the columns appropriately in the sequential file stage and save reconverting them in a transform stage.
where in seq file is possible to do???
please let me know?
Using the left and right string functions in combination,we can reach the exact string position/extract that bit of required string
and I have a question here,
declare the columns appropriately in the sequential file stage and save reconverting them in a transform stage.
where in seq file is possible to do???
please let me know?
Hi I have experience in parallel extender datastage I am ready to give/take help from other
hope we all help each other hand in hand
hope we all help each other hand in hand
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
Define the columns as you would a COBOL flat file. Use CHAR(10) which represents the full length of the string (ie INPUTVAR[20,10]
Make sure you define the sequential input by it's full block length. This will be processed sequentially, but the decode of the flat file will be done in parallel if you use the Promote subrecord stage within Datastage.
Promote subrecord - promotes input subrecord columns to top-level columns
It should actually be a much faster process than in server.
Regards,
Ray D
Make sure you define the sequential input by it's full block length. This will be processed sequentially, but the decode of the flat file will be done in parallel if you use the Promote subrecord stage within Datastage.
Promote subrecord - promotes input subrecord columns to top-level columns
It should actually be a much faster process than in server.
Regards,
Ray D
-
- Participant
- Posts: 21
- Joined: Mon Mar 01, 2010 4:55 pm
Hope this helps you.
Once I faced the same situation, Have to load the data from sequential file(not a delimited file) to Oracle database -Server Job.
I used routines, in which I defined the total length of the record and defining all the column and its length.Then used the routine in the transformer.
It worked.
Regards,
Lobo
Once I faced the same situation, Have to load the data from sequential file(not a delimited file) to Oracle database -Server Job.
I used routines, in which I defined the total length of the record and defining all the column and its length.Then used the routine in the transformer.
It worked.
Regards,
Lobo