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



Joined: 21 May 2007
Posts: 97
Location: Australia
Points: 992

Post Posted: Sat Aug 03, 2019 7:09 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Additional info: Service job to load any flat file to DB table
Hi experts,

Looking for a good idea on how to load any flat file to SQLServer DB.
We have plenty of flat files and some of them can be pretty large. The owners of these files would like test loading them into DB on an ad-hoc basis for exploratory purposes. The database would give them better performances and sharing capabilities.
Could there be a job wrapped into a service that would need a few parameter values provided by the end-users:

1. File name
2. Target table name
3. Table action - append, truncate, create
4. Delimiter character

The job would require:

1. Source file in the pre-determined directory and delimited with a character specified in the parameter above

Is there a way for a job to read this file without record schema? Possibly with column export using delimiter character specified in the parameters, that would then build the character schema to be used by sequential file stage?
Or similar?
Just trying to simplify for the business users that know some SQL but none of DataStage.

Regards,

Novak
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 43044
Location: Denver, CO
Points: 222242

Post Posted: Sat Aug 03, 2019 9:29 am Reply with quote    Back to top    

Okay... lots of thoughts flying around in my head right now. Let's see if we can shepherd them out in some kind of a coherent fashion.

High level, I'd be shocked if anyone could put together a single job that could load "any flat file to any table". Sure, you could consume any file as one long string and then restructured it using a Column Import stage but you would need to "supply an import table definition to specify the target columns and their types". So not sure how that attempt would play out. The other thing is something that may have changed as my experience is from many years ago but ISD jobs working with flat files are problematic. Meaning, from what I recall if you open on if is only closed when the service stops. We ended up using them in our services only for static reference data.

Now, please clarify the scope for us. How many target tables are there? What I would consider is a job for each target table, consuming an appropriately formatted flat file. Based on the name of the target table in your ISD request, the service could launch the appropriate job to load that target table. I would also stick with a single required delimiter (perhaps something like a pipe) rather than let them pick one willy-nilly. Hmmm... just noticed that your table options including create, so it would seem the expectation is that they would also be able to create any table they wanted - is that true? If that's the case and that is really what is "needed" here, then the suggestion above assuming an agreed on list of target tables goes right out the window. [sigh]

And that leaves me with a suggestion to forget about using DataStage for this. Give your users who "know some SQL" access to a tool like Toad and their own schemas (or whatever SQLServer calls them) somewhere safe where they have the grants to manage their own tables. Toad can walk a user through reading a flat file, building the appropriate control file to parse it and then loading it into a target table - even creating it in the process. All without needing to know any SQL. Bam! Then you could get back to whatever else you were working on before this came up. Wink

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2878
Location: USA
Points: 21847

Post Posted: Sun Aug 04, 2019 6:50 am Reply with quote    Back to top    

The first thing that popped into my mind was to use a Document Database, maybe something like MongoDB. I have not used them myself yet but have read about them enough to think that's what they're designed for... just cram miscellaneous stuff in there.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
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: 43044
Location: Denver, CO
Points: 222242

Post Posted: Mon Aug 05, 2019 7:45 am Reply with quote    Back to top    

Trending!

#JustCramMiscellaneousStuffInThere

Wink

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
Novak
Participant



Joined: 21 May 2007
Posts: 97
Location: Australia
Points: 992

Post Posted: Tue Aug 06, 2019 8:11 am Reply with quote    Back to top    

Thank you both.

Craig, you've reminded me of how simple this can be. We can't have Toad but I have checked quickly with SSMS and can do similar (need to resolve character set defaults).

The scope, roughly, is that about 10 users would load about 2 files per month, and doing so without involving us (IT). It would be pretty good if we gave them this self-service, with some rules in place (e.g. comma delimited files, etc.)
Still wanting to explore using DataStage for this...
How about taking a long string through a loop in transformer stage to be taken apart into columns?
It does not have to be ISD. We can just give them access to Operations Console...
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: 43044
Location: Denver, CO
Points: 222242

Post Posted: Wed Aug 07, 2019 7:42 am Reply with quote    Back to top    

Glad I could help. Somewhat. Wink

Be careful with using comma delimited fields if there is any possibility of there being commas in your data. Not sure how much I can help going forward other than to say you certainly could use a transformer loop to chop a string up by its delimiters, the problem is going to be... then what?

In the meantime, if you do end up trying something, let us know how it goes! Specific problems always get more specific answers. Very Happy

_________________
-craig

"May the bridges I burn light my way forward"
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: 54546
Location: Sydney, Australia
Points: 295766

Post Posted: Sun Aug 11, 2019 4:12 am Reply with quote    Back to top    

If you're using Connector stage types, of course, the field delimiter property can be the value of a job parameter.

We've just been making use of that bulk loading data into Snowflake.

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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