How to handle multiple Input as well as output @runtime
Moderators: chulett, rschirm, roy
How to handle multiple Input as well as output @runtime
Hi,
How can we create a job in which at run time we pass the input(flat-file)
as well as output(oracle 9i tables) ? We are having more than 200 flat-file & 200 oracle 9 i table. Can any body help?[/b]
How can we create a job in which at run time we pass the input(flat-file)
as well as output(oracle 9i tables) ? We are having more than 200 flat-file & 200 oracle 9 i table. Can any body help?[/b]
Hi,
Welcome
.
I doubt you have 200 files/tables with the same table definition,
so parameters will get you to dynamically set file/table name/s, but not the table definition.
what are you trying to acheive?
are you trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition?
Welcome
![Smile :)](./images/smilies/icon_smile.gif)
I doubt you have 200 files/tables with the same table definition,
so parameters will get you to dynamically set file/table name/s, but not the table definition.
what are you trying to acheive?
are you trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition?
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
What about the metadata for the 200 flat files and tables? Are they similar in all but name? That's the only way you're going to create one job that takes Job Parameters and updates 200 tables from 200 files. Unless the Oracle tables have a common set of fields and those fields are the only fields that will be updated. ![Confused :?](./images/smilies/icon_confused.gif)
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
roy wrote:Hi,
Welcome.
I doubt you have 200 files/tables with the same table definition,
so parameters will get you to dynamically set file/table name/s, but not the table definition.
what are you trying to acheive?
are you trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition?
I am trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition. The files & respective tables are 1 to 1.
chulett wrote:What about the metadata for the 200 flat files and tables? Are they similar in all but name? That's the only way you're going to create one job that takes Job Parameters and updates 200 tables from 200 files. Unless the Oracle tables have a common set of fields and those fields are the only fields that will be updated.
I am trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition. The files & respective tables are 1 to 1. But two Oracle tables doesn't have common set of field & I am going to truncate the tables before loading ,so there is
no chance for updations.
Can't be done - at least not in any kind of a 'normal' DataStage job. Perhaps if you used the Oracle 'catalog' tables as a source, passed in a table name and then somehow dynamically spun out a control file for SQL Loader...Agnivesh wrote:I am trying to create a generic job that loads the data without the need to build 200 jobs, each having it's own table definition. The files & respective tables are 1 to 1. But two Oracle tables doesn't have common set of field & I am going to truncate the tables before loading ,so there is no chance for updations.
![Confused :?](./images/smilies/icon_confused.gif)
Now, there are ways to automatically create 200 jobs from the table metadata. A colleague of mine just did something like this to create 478 jobs to extract CDC data to matching flat files. The end result was an export file in XML format that you then imported to create the jobs. Not something for The Faint of Heart or that could be explained in a post here. You may, however, be able to contract with him to create something like that for you.
![Wink :wink:](./images/smilies/icon_wink.gif)
Other than that, if you are simply doing truncate-and-load without any odd trasformations of the data, why not just fall back on SQL Loader?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Agnivesh,chulett wrote:Now, there are ways to automatically create 200 jobs from the table metadata. A colleague of mine just did something like this to create 478 jobs to extract CDC data to matching flat files.
I responded via private message, but...
Send me an email with details if you're still interested in a process to generate 1-N number of DS jobs automatically.
Thanks,
Craig
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ask yourself this question. "Could I automate the generation of 200 sqlldr control scripts?" If the answer is yes, then the DataStage answer is also yes. If the answer is no, then the DataStage answer could still be yes, but not without adding some specialist knowledge about how table and column definitions are stored in job designs.
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.
crouse wrote:Agnivesh,chulett wrote:Now, there are ways to automatically create 200 jobs from the table metadata. A colleague of mine just did something like this to create 478 jobs to extract CDC data to matching flat files.
I responded via private message, but...
Send me an email with details if you're still interested in a process to generate 1-N number of DS jobs automatically.
Thanks,
Craig
Sorry for being late. I will mail u soon with details
Thanks
Agnivesh