Configuring ODBC connection on AIX box for DataSta

srinivasb · Post by **srinivasb** » Mon Dec 23, 2002 8:48 am

Hi,

I am trying to configure ODBC for DataStage V 6.0 on AIX box.

Edited $dshome/Ascential/DataStage/branded_odbc/odbc.ini file

Made entry to ./Ascential/DataStage/DSEngine/sample/uvodbc.config

While trying to import ODBC table definitions. It shows me the DSN (jpmdev) in the drop down list but gives me the following errors:

DSR.MetaGeta(GET.TABLES)(SQLConnect('jpmdev','pm_user')):
BCI Error:
SQLSTATE=IM002,CODE=0,[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Data source name not found and no default driver specified

Any help will be gladly appreciated.

Thanks and regards.

Srinivasb

WoMaWil · Post by **WoMaWil** » Mon Dec 23, 2002 1:15 pm

Hi Srinivasb,

I am not in office. But you are right it is not easy to configure ODBC. If you follow all steps in a correct manner as described in the manual you will succeed.

You have to look also into the dsenv-file and several other files.

End of next week I am back in office an then I tell you the details.

Perhaps download also the datadirect ODBC wire protocol manual from the net. It describes installation a bit better than the ascential one.

Wolfgang

srinivasb · Post by **srinivasb** » Sat Jan 04, 2003 2:45 am

Hello Ray Wurlord and Wolfgang:

We still are unable to configure ODBC between the Windows client (NT professional/Win 2000 server/professional) with DataStage Ver 6.0 or 5.2 to DataStage Server Ver 6.0 on IBM AIX 5L.
Though we have come to the point where the DSN shows up in the list of the drop down.

We have re installed DataStage 6.0 on AIX yesterday and we seek guidance from all of you regarding the steps to be taken in this process. The Oracle version is 8.1.7 on AIX.
All properties are properly configured.There are two instances of Oracle running on AIX box.
I am able to use OCI connection on AIX DataStage.We need to get the ODBC up and running.PLEASE HELP

Srinivas.B
India
Mobile :0091-44-2301101
Xtn 35

ray.wurlod · Post by **ray.wurlod** » Sat Jan 04, 2003 3:47 pm

Your DataStage server is an Oracle client. Oracle client software must be correctly installed and configured on the machine where the DataStage server is. This involves environment variables, entries in TNSNAMES.ORA, SQL*Net and so on. Can you access Oracle from that machine using any other client? How far do you get when you use the test programs in branded_odbc? Have you tried to use DSCONNECT from a telnet session in one of your DataStage projects (you will need to execute the commands in the dsenv file first - are commands to set the Oracle environment variables, such as ORACLE_HOME, in dsenv)?

srinivasb · Post by **srinivasb** » Thu Jan 09, 2003 3:21 am

Thanks a million Wolfgang and Wurlord,

By trial and error , we have configured the ODBC .Now my question is which is the most optimal method of setting up of ODBC in such UNIX systems?

1. ODBC -common to all projects
2. ODBC specific to each project

Thanks in advance.

Regards
Srinivas.B

Srinivas.B
India
Mobile :0091-44-2301101
Xtn 35

ray.wurlod · Post by **ray.wurlod** » Thu Jan 09, 2003 5:31 am

Optimal is what's right for you.
If you are likely to need to access different DSNs from different projects (for example development, test and production environments), which is the usual practice in my experience, then per-project is definitely the way to go.
On the other hand, if you use the same DSN for each project, and prefer to qualify table names with schema names and so-on, then you could configure globally. Less configuration effort, but in my opinion more difficult-to-maintain jobs.

WoMaWil · Post by **WoMaWil** » Thu Jan 09, 2003 8:20 am

Ray,

I have made the experience with DS 5.2, that when I have more than one target system and I define it globaly, that I get all the choice when I first enter a certain project. But when I then do something as for example importing metadata via one of my many sources DS does automaticly make a local file to that project, which contains then only the one source. So next time being in that project I have only one choice instead of the many I had the first time. So may be that has changed in 6.x but in 5.x it is better to manage each file localy when you have more than one source.

Wolfgang

ray.wurlod · Post by **ray.wurlod** » Thu Jan 09, 2003 4:33 pm

Good point.
When one imports an ODBC table definition, only the uvodbc.config file in the project is updated with DSNs currently in odbc.ini.
So DataStage itself works project by project; to preserve true "global" access you would need to manually maintain the uvodbc.config file in each project. Alternately you can remove this file from all projects, in which case DataStage will search for it in the Server Engine account (the "UV account") or, if not found there, in /etc.
Removing per-project uvodbc.config files is not recommended practice but it is legal according to the manuals.