DB2EE stage - write data to 2 different DB instances

shin0066 · Post by **shin0066** » Mon Sep 22, 2008 8:19 am

Hi,

We have 2 ETL applications running on same application server - each uses different DB2 instances. Requirement is both applications needs to write data using DB2EE stage,where are currently one DB2 instance is cataloged and it has needed db server information in db2nodes.cfg file.

Is there any option, we can provide both applications to use DB2EE stage to write data into their own DB environment?

Thanks,

ray.wurlod · Post by **ray.wurlod** » Mon Sep 22, 2008 4:04 pm

Just set the DB2-related environment variables separately for each "application".

shin0066 · Post by **shin0066** » Tue Sep 23, 2008 9:54 am

Hi Ray,

You are suggesting to DB2 environment variables like instance information in dsenv file? if so, how do we flip dsenv db2 environment variables for different project?
appreciate your input.

Thanks,

ray.wurlod · Post by **ray.wurlod** » Tue Sep 23, 2008 4:10 pm

Put the environment variables into the project's environment variables.

bcarlson · Post by **bcarlson** » Tue Sep 23, 2008 5:04 pm

We use job parameters for connecting to the database and provide the following: client instance, server instance, user name, password, database name. There shouldn't be anything preventing you from connecting to either of the databases if you pass all the parameters.

Brad

shin0066 · Post by **shin0066** » Tue Oct 07, 2008 12:49 pm

Hi,

As long as i pass DBname, Instancename, Id and Password ETL job works fine. But when i try to log to Designer and try to import Metadata via ODBC or Plug in metadata options it is throwing SQL1013N error. Any idea what else needs to be done to over come this issue.

Thanks,

MTA · Post by **MTA** » Tue Oct 07, 2008 1:30 pm

Check odbc.ini file in DS engine

shin0066 wrote:Hi,

As long as i pass DBname, Instancename, Id and Password ETL job works fine. But when i try to log to Designer and try to import Metadata via ODBC or Plug in metadata options it is throwing SQL1013N error. Any idea what else needs to be done to over come this issue.

Thanks,

MTA · Post by **MTA** » Tue Oct 07, 2008 1:31 pm

Check odbc.ini file in DS engine

shin0066 wrote:Hi,

As long as i pass DBname, Instancename, Id and Password ETL job works fine. But when i try to log to Designer and try to import Metadata via ODBC or Plug in metadata options it is throwing SQL1013N error. Any idea what else needs to be done to over come this issue.

Thanks,

shin0066 · Post by **shin0066** » Tue Oct 07, 2008 3:35 pm

database entry is already been in .odbc.ini file.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 07, 2008 3:44 pm

Do you have the database environment variables in your Administrator client (that is, in the DSParams file for the project)?

shin0066 · Post by **shin0066** » Tue Oct 07, 2008 9:48 pm

yes,
I did set APT_DB2INSTANCE_HOME to "2nd db2 instance"

1st db2 instance is set in dsenv file. I am overriding 2nd db2 instance in DS Admin for specific to this project only. But some how when i log to this specific project APT_DB2INSTANCE_HOME is not reflecting to 2nd db2 instance.

DB2EE stage is working file when i pass dbinstance, id and password but not the ODBC stage is not working. I provided driver information in .odbc.ini and updated uvodbc.config for this specific project. When i perform ./example program from branded_odbc folder it is working but i have to export DB2INSTANCE='2nd db2 instance'.

Any advise is appreciated.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 08, 2008 12:13 am

Put ALL of the database environment variables into your project (in the Administrator client), and use environment variable job parameters to override the value (as required) on a per-job basis.

shin0066 · Post by **shin0066** » Wed Oct 08, 2008 6:47 am

Hi Ray,

Can't it be done with out use of environment variables per job basis in job parameters?

for example if i want to import ODBC metadata - how does job level parameters works?

Thanks,

ray.wurlod · Post by **ray.wurlod** » Wed Oct 08, 2008 2:56 pm

I don't think so. If you find a way please post your findings.

shin0066 · Post by **shin0066** » Wed Oct 08, 2008 8:40 pm

Thanks for the info Ray,

Now ODBC stage is working after adding a user variable called DB2INSTANCE = '2nd instance' name, I could able to point to DB2 database and extract data but when i try to import meta data using ODBC or Plug-in methods getting SQL1013N error.

Today I updated LIBPATH and PATH for DataStage project on Administrator client to add 2nd instance information - but what ever i provided in LIBPATH and PATH in administrator will get padded at the end of sourced dsenv libpath and path string - so 1st instance is coming before 2nd instance information - i guesss that is why when i try to import meta data process not taking 2nd instance information. But i was doing changing LIBPATH and PATH for this specific DS project today morning - at one point i was able to import Metadata using ODBC and Plug-in option, but after 30 min when i try to do the same thing it was giving SQL1013 - not sure what i did to get it worked - i tried all the option it is not working.

Any idea how to resolve SQL1013N for meta data import.

Thanks,