Dynamic Datastores

dataservices job design
1

Hi,

I am trying to read the contents of a csv file(same structure different content). The csv file contains a list of databases that I need to connect. Once I connect to the databases, I need to go to a table(table name is same in all the databases) and grab the data.

Product: Data Services 4.0
Database: MySQL 5.1

Please let me know how should I go about it.

Thanks

techie2011 :us: (BOB member since 2010-03-02)

2

I don’t think you can specify dynamic datastore yet, in any version of DI/DS.
I wished we had an option to do this atleast in script, in that sql function.

Your other option is to create a Nested-if-else structure, which includes all DataStore name and load appropriate table.

Some thing along the line…

if Data_store = ‘PROD’
SQL(‘PROD’,‘Select * from table’);
else…

data_guy :us: (BOB member since 2006-08-19)

3

Thanks for your reply.

I am not sure if I can do a if-then-else statement as the datastore names from the csv file can change often. All I need to do is to take a one record from the csv file, connect to the database, extract the data and then move to then move to the next database.

Is there way to do this?

Thanks

techie2011 :us: (BOB member since 2010-03-02)

4

I can’t think of any other EASY way to do this, then creating a CUSTOM function in DI, and including all possible DS names and constructing nested If-then-else SQL statements for extracting the data from those tables.

And on other hand, you do have to declare all DS name first in DS/DI before you can make use of them, so if your CSV contains new DS info, you need to create them first in DI/DS, other wise it won’t work!

Other’s…

Your thought’s

data_guy :us: (BOB member since 2006-08-19)

5

I don’t get one thing…

Are you talking about traversing different connections of a same source system like DEV/TEST/QA/UAT/… or from all together different sources?

ganeshxp :us: (BOB member since 2008-07-17)

6

OK. let me explain with an example

System 1 has db1, system 2 has db2…system n as dbn
The table are same across all the systems. The system names and database names will be provided in CSV format.

The list of systems is dynamic and hence I cannot create datastores manually. They all belong to the same environment eg QA

I need to go to each of these systems, login, access the table, extract the data, insert it into a target table and then move to the next system.

Thanks for your help. Please let me know if you need any additional information

techie2011 :us: (BOB member since 2010-03-02)

7

But will the list be a subset of a Universal Subset or how is that?
I mean say today you have 50 databases as universal set. Day 1 you get 30 requests which is present in this universal set, then day 2 - 15 requests which is from this universal set? Or the universal set keep changing?

If the universal set keep chaging then I don’t have a solution or else I have an ugly solution!!!

EDIT:
One other question is, are they from same Database system, I mean like everything from Oracle/or everything from SQL Server or this one also will change?

ganeshxp :us: (BOB member since 2008-07-17)

8

It is not a part of a universal subset. I need to connect to all the systems/databases listed in the csv file.

I will be reading all the databases in the file. Day 1 100 i read 100…say 2 50…i read 50.

All these databases are of the same type i.e. MySQL

please let me know what your ugly solution is

thanks

techie2011 :us: (BOB member since 2010-03-02)

9

Ganesh,

we are eagerly waiting for your ugly solution!

:stuck_out_tongue: :stuck_out_tongue:

data_guy :us: (BOB member since 2006-08-19)

10

Okay but it won’t fit if the everyday’s set is not a subset of the Universal Set

Say you have 20 source systems. And you have same Datastore with multiple configuration (Things will go impossible if the datastores are different)

Now you do export execution command for all possible source system in the Management Console for the same job.

Then have a single master job which would read the CSV File in some fashion, and call the corresponding .bat files using the exec command

Is that something making sense.?

By the way, I can’t imagine of a business case in which 100’s of source system which would always keep changing. :?:

I have another stuff. Why don’t you collect all data all the day and create a VIEW on the database by making the Filter in the IN Clause…
Will that one help in some way?

ganeshxp :us: (BOB member since 2008-07-17)

11

Without knowing more about the requirements the above approach is exactly how I would do it too.

So does this mean that you could end up running a job for every row in the CSV file? That sounds dangerous. The master job would need to be able to do some throttling or it would only be able to run one child job at a time.

eganjp :us: (BOB member since 2007-09-12)

12

There was a solution in this forum for dynamically changing source or target tables, I am thinking about adapting the same technique here in your case:
(Thank GOD all the databases use the same DBMS)

Here you go…

Lets say you have an excel file like this:


[b]Db_name, server_name[/b]
Db10      , svr10

At the start of the job, you will have a sql script to drop and create view, this view would be created based on the values from the excel sheet for database and server namesÂ…I guess this is possible. You would need one view for each table load. This view has to sit somewhere from which you can access all the databases on all the servers.
This is the main crux of the solution and is open for discussion, mainly about the permissions needed to achieve this, besides other things.

In the next step of job, you would be using this view as a source table to load your target. To make this reusable, you could use the script in a workflow and pass the db and server names as parameters and use them in the sql script. This way you don’t have to maintain multiple system configuarations, you will only be dealing with one datastore in the job in which the view is present.


WF1 : Fetch records from the Excel file 
WF2 : Would contain the script  with create and drop view SWL, Db10 and svr10 values are passed as input parameters that are fetched from the Excel file.
WF3 : Would contain the dataflow to load the data from this view on to the target
Job:	WF1 >> WF2>>WF3

If you need to load data from a table on more than one database, you could use WF2 and WF3 in a while loop until you process all the records or from the excel file. –beginning of a different story.

I’d wait to hear from the others before I start.

Thanks,
Bindaas

Bindaas BODS :uk: (BOB member since 2010-03-25)

13

Hi Guys,

Back with a fresh mind after holidays!! 8)

Please can somebody explain the mentioned solution with more elaborative example.

Cheers,
Shazin

Shazin :india: (BOB member since 2011-07-19)

14

Bindaas

Looks like a solution which I had implemented ages back :expressionless:

But yes you are correct. Getting a DB Link connection to a common DB would be a challenging task at least for me.

We have to go through a process of security layers to get this done. Also DB Links for 50 DB’s??? Or some number like that. I can’t imagine.

But yes I had used this View concept to switch between 2 databases or schemas. Perfect working method.

ganeshxp :us: (BOB member since 2008-07-17)

15

Ganesh,

I imagined it wouldn’t be easy. But it kind of strikes me as a workable solution as jobserver service account should be able to access all the databases anyway, so the Techi2011 can manage to include the db link as part of set up process along with job server access grants each time when a new database is added…Everything would fall in place.

I agree he would be having hard time convincing the dba s. Any ways if data is spread across 20 - 50 databases, that is what you get in to…

Thanks,
Bindaas

Bindaas BODS :uk: (BOB member since 2010-03-25)