DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
simc



Group memberships:
Premium Members

Joined: 22 Jul 2015
Posts: 5

Points: 91

Post Posted: Fri Oct 20, 2017 10:34 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
So I have a requirement to load a full file into the database.

I've tried a couple different thoughts, first was to cat the file in a command stage, and use the result as a variable to insert into the database, but it seems like there's size limitation to that variable.

Second thing I tried, was to read the file with a seq file stage, but I can't get it to read the whole file into one rec, one field... it reads each line into a record, so I tried with that updating the one row and appending each line of the file... it works, but takes like 4 minutes a file.

So my question, is does anyone have an elegant solution to read in a full file and insert it into one record.

Thanks from a relative newbie!
Charles
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42215
Location: Denver, CO
Points: 216749

Post Posted: Fri Oct 20, 2017 10:53 am Reply with quote    Back to top    

Curious what kind of field we're talking about here, a CLOB?

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1636
Location: Colleyville, Texas
Points: 22245

Post Posted: Fri Oct 20, 2017 10:56 am Reply with quote    Back to top    

Could you add the following please?

Database type and the definition of the "field" that is being used to store the data.

Maximum expected file size (if you have it) - is it megabytes, terabytes, etc.

Format for the original file - does it contain fixed length records, variable length, Unix or DOS format?

More detail is better!

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
Not yet rated
simc



Group memberships:
Premium Members

Joined: 22 Jul 2015
Posts: 5

Points: 91

Post Posted: Fri Oct 20, 2017 11:38 am Reply with quote    Back to top    

It's an oracle database, 12c, the field type is CLOB.

The file format is simple text, and the samples I have are mostly under 200kb with a couple in the 1.5mb range. can't see them being more than 3-4mb.

The format is fixed length records, DOS format I believe.

Heres a quick sample, with the formatting a little off:

Code:
220160101CLO CLO 1ADR 1 1       24700        3705        3705           0           0           0           0       45280
220160101CLO CLO 1ADR 1 2        2800         420         420           0           0           0           0        1915
220160101CLO CLO 1ADR 1 3        2500         375         375           0           0           0           0        1915
220160101CLO CLO 1ADR 1 5       21900        5212        5212           0           0           0           0        4175
220160101CLO CLO 1ADR 1 6       13800        3558        3558           0           0           0           0        7695
220160101CLO CLO 1ADR 1 7           0           0           0           0           0           0           0           0
220160101CLO CLO 1ADR 1 8           0           0           0           0           0           0           0           0
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42215
Location: Denver, CO
Points: 216749

Post Posted: Fri Oct 20, 2017 12:49 pm Reply with quote    Back to top    

The forum software removes all 'extra' whitespace, hence the formatting issue. Wrap anything you want to preserve in [code] tags, something I did for your post.

FYI, some quick searches out in the wild turn up several "Ask Tom" questions similar to this, all of which were directed to treat the file as an external table and use PL/SQL to load it into the CLOB. Of course. Wink Not quite sure how you would turn that into a DataStage job off the top of my head.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 267

Points: 2686

Post Posted: Thu Oct 26, 2017 12:12 pm Reply with quote    Back to top    

this is not as easy as I thought it would be.

Can you pipe the output of cat (unix) or type (windows) into it?
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1153

Points: 7639

Post Posted: Thu Oct 26, 2017 12:47 pm Reply with quote    Back to top    

This may be a silly question, but your file seems very much like structured data to me... why do you want to load the FILE into the database when the DATA into a TABLE would be best?

You could add a few columns for filename and timestamps if needed... but I think a regular table with fields 13 (+?) fields would suit you best.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54034
Location: Sydney, Australia
Points: 293130

Post Posted: Sun Oct 29, 2017 12:50 am Reply with quote    Back to top    

You could try setting the line terminator to None (or to something that will never occur in the data) in the Sequential File stage.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
eostic

Premium Poster



Group memberships:
Premium Members

Joined: 17 Oct 2005
Posts: 3715

Points: 29665

Post Posted: Mon Oct 30, 2017 5:21 am Reply with quote    Back to top    

Or use a Server Job. The Folder Stage will easily pull the entire contents into a single column "as is". For files of a few hundred meg or less, it works perfect every time.

Ernie

_________________
Ernie Ostic

blogit!
Open IGC is Here!
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54034
Location: Sydney, Australia
Points: 293130

Post Posted: Mon Oct 30, 2017 2:37 pm Reply with quote    Back to top    

Good catch, Ernie.
One can encapsulate the Folder stage in a Server Shared Container and use that in a parallel job, if that's what you need.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 267

Points: 2686

Post Posted: Wed Nov 01, 2017 9:12 am Reply with quote    Back to top    

PaulVL wrote:
This may be a silly question, but your file seems very much like structured data to me... why do you want to load the FILE into the database when the DATA into a TABLE would be best?

You could add a few columns for filename and timestamps if needed... but I think a regular table with fields 13 (+?) fields would suit you best.


Version/source control for code is nothing but a database of files. There are times when that sort of thing can be useful. We archive a few generated files to DB for various reasons. If nothing else it beats having a massive archive folder and makes pulling by date/time easier and deletion of outdated 'files' easier and backup is taken care of, etc. I guess all that can be summed up by "convenience".
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours