Many projects require different db clients on 1 DS engine

dohertys · Post by **dohertys** » Wed Jul 13, 2011 8:21 am

Hi,
I've got several different application areas who require separate projects on datastage and would all use different database clients, but I want to run them on the same datastage engine.

Normally when I configure datastage to use a particular database client I would add specific lines into the dsenv file in order to get is working - but that would enable each client at the engine level.

Is it possible to do this at a project level instead? ( and would that be a better way of acheiving this ?)

Is it just a case of setting project level variables instead of setting them in dsenv?

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Wed Jul 13, 2011 10:50 am

Is there any particular reason you don't want to have all databases accessable system-wide? I always enable all databases in the dsenv file so that if project requirements change and they need access to a different database it doesn't require re-configuration.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 13, 2011 5:10 pm

For ODBC you can have project-based uvodbc.config files. For other database connectivity use project-based environment variables (for example DB2INSTANCE or ORACLE_HOME).

dohertys · Post by **dohertys** » Thu Jul 14, 2011 8:21 am

Thanks for your replies.

One reason for not setting up the database access system wide was simply to not let people have access to something they didn't need. I suppose that really giving them access to the client shouldn't matter so much - as long as we don't give them a logon then it can't do any harm.

Another reason would be that if we make changes at a dsenv level, then we have to get all the different projects owners to agree to an outage so that I can restart Datastage... but if I do it just using project level variables then I can make the changes to one project without having to inconvenience the users of the other projects.

Is there any other advantages/disadvantes to either method that anyone can think of? I think someone mentioned that if its set up at project level then some things don't work correctly - e.g. 'right click-- view data' on a database stage.. Have you experienced that? (must admit, i've not tested that myself yet)

PaulVL · Post by **PaulVL** » Fri Jul 15, 2011 1:57 pm

From my experience, I would vote for open access to the various DBMS tools. Oracle, Teradata, Informix, etc...

Your security is on the Database side, not the ETL tool side in terms of protecting the access to the data.

Our setup:

3 environment: DEV, QA, PROD
65 projects in DEV, 34 in PROD.
10000+ jobs per day in PROD.
Linux GRID setup.

I have no issues of updating client side code for Informix or Oracle and taking the environment down. You don't want one client side code install path per project on the same server. Your DBAs would recommend one client code install per server. Not one per project.

chulett · Post by **chulett** » Sat Jul 16, 2011 6:41 am

dohertys wrote:Another reason would be that if we make changes at a dsenv level, then we have to get all the different projects owners to agree to an outage so that I can restart Datastage... but if I do it just using project level variables then I can make the changes to one project without having to inconvenience the users of the other projects.

... I've found that there are very very few dsenv changes that actually require DataStage be restarted to take effect. Any time a job runs it sources it so changes are immediate. Heck, I don't even remember the last one that did.

And I agree with Paul, your security is on the database side.