Loading the same data from many source databases

[Luk]

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

[Sainath.Srinivasan]

Look for DRS.

You can always use parameters in ODBC DSN.

The only affecting factor may be the naming conventions allowed and the delimiter char.s in the underlying dbms.

[ArndW]

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?

[Luk]

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?

you have right yhat will be working:)

but second option is more tempting for me because I don't want to add parameters to every job which I have done.

You can always use parameters in ODBC DSN.

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??

[ArndW]

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!

[Luk]

table are identical in 100% because database is a part of special financial system and must be the same every time!!

I know that I can manualy change ODBC sources, but Sainath said sth. about parameters and I don't know what he had in his mind.

anyway thanks for help!

[chulett]

All he meant was that you could set up multiple ODBC sources in the configuration files and then specify the correct one for each run via a job parameter used in the DSN field.

[Sainath.Srinivasan]

But then, why are the tables identical and present in different rdbms? Is it some kind of extract produced from various different sources?

[Luk]

All he meant was that you could set up multiple ODBC sources in the configuration files and then specify the correct one for each run via a job parameter used in the DSN field.

so, it is the same solution as Arnd gave !!

[chulett]

Yup.

[Luk]

But then, why are the tables identical and present in different rdbms? Is it some kind of extract produced from various different sources?

I will give example:

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]

[Sainath.Srinivasan]

In that case, you are leading yourself into a situation of changing the configuration files.