Hi All,
I need best practice suggestion for the following design
I designed the job in following way
(Staging Database) ODBC stage----->Transform------>(Target Database )ODBC stage
I was told that connecting to two databases from one job is not a good practice....
is this a good practice? what will be the problems if I continue with this design?
Thanks
multiple ODBC stages on one layout
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
ETL. Extraction, Transformation, Load. Any source, any target.
Find whoever told you "connecting to two databases from one job is not a good practice", demand to know why, and post the reply here so we can all have a good laugh.
Find whoever told you "connecting to two databases from one job is not a good practice", demand to know why, and post the reply here so we can all have a good laugh.
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: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Where I work it is not a good idea on some systems because of the very tight window for extracting data. By splitting the jobs, in my opinion, you reduce the chances of a failure during the extract. The extract job will likely run much faster and this helps to get the data out in the tight window. Especially when working with ODBC for loading data. In addition, if you have a failure during the load, you hopefully have the extracts still available. Thus, you don't need to go back and get the data again.
This is not a do-all, but a practice emloyed during modeling and job creation for those systems where we need such measures.
What will be the problems if I continue with this design?
I suppose that if the company you work for has established standards and you choose to not follow them that may you risk termination.
This is not a do-all, but a practice emloyed during modeling and job creation for those systems where we need such measures.
What will be the problems if I continue with this design?
I suppose that if the company you work for has established standards and you choose to not follow them that may you risk termination.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ultramundane makes a good point. But "it's not good practice" is not the reason for splitting extraction from the other phases; there is a good reason (limited time window) in that case. I still believe that it's the right thing to do to challenge these vague assertions.
Be like a young, wide-eyed child, and don't be afraid to ask "why?". But don't accept "Because" as the answer.
Be like a young, wide-eyed child, and don't be afraid to ask "why?". But don't accept "Because" as the answer.
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.