Loading the same data from many source databases
Moderators: chulett, rschirm, roy
Loading the same data from many source databases
Hi!
I'm using ODBC stage to get data from MSSQL server. On this server there are many databases with the same strtucture (i.e. for many different companies).
Is there a simple way to use the same jobs to different data ODBC sources??
Is there some possibilty to change data sources in many jobs in one time??
I can of course change path to concrete database in ODBC source (leaving name of data source) but is this the best way, I doubt.
regards
I'm using ODBC stage to get data from MSSQL server. On this server there are many databases with the same strtucture (i.e. for many different companies).
Is there a simple way to use the same jobs to different data ODBC sources??
Is there some possibilty to change data sources in many jobs in one time??
I can of course change path to concrete database in ODBC source (leaving name of data source) but is this the best way, I doubt.
regards
LUK
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
you have right yhat will be working:)Can you not pass the Database instance, userid, and password into your job as parameters so that it will connect to the appropriate ODBC database at runtime?
but second option is more tempting for me
![Smile :)](./images/smilies/icon_smile.gif)
could you be more specific, is it possible to have 1 ODBC source (on Unix, in .odbc.ini file) that points to many databases?? and how can I achieve this??You can always use parameters in ODBC DSN.
LUK
If you do not wish to add parameters to your job(s) then you are limited to using your one ODBC connection; this would entail you editing & changing your DataStage "odbc.ini" and perhaps "UVODBC.INI" files to shift the actual connection location around.
Although ODBC and standard SQL is supposed to be both global and portable across platforms and databases in practice there are idiosyncracies and each database vendor has added a couple of enhanced features which, when used, cancel out any true portability. What I mean is that although you might think that the tables are identical across the Databases most often they are not 100% the same. But perhaps you are in luck!
Although ODBC and standard SQL is supposed to be both global and portable across platforms and databases in practice there are idiosyncracies and each database vendor has added a couple of enhanced features which, when used, cancel out any true portability. What I mean is that although you might think that the tables are identical across the Databases most often they are not 100% the same. But perhaps you are in luck!
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I will give example:But then, why are the tables identical and present in different rdbms? Is it some kind of extract produced from various different sources?
MSSQL database1:
tableA(col1,col2...)
tableB(col1,col2...)
MSSQL database2:
tableA(col1,col2...)
tableB(col1,col2...)
databases have the same structure, tables in databases have the same structure!!
I am doing 1 job in which I'm extracting data from tableA and B from database1, transform and put data into target DB. Then I am using the same job (with different source database - this issue was the main subject of this post) to extract data from tablaA and B from database2 and put it into another target DB.
so, the purpose is: to have one job, and us it many times on different source and target DB!!
sory! I see that I wrote very long reply!
:D [/quote]
LUK
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom