how to configure an odbc connection on unix

kjaouhari · Post by **kjaouhari** » Wed Nov 29, 2006 8:20 am

Hi all ,

I'm trying to configure to configure an odbc connection (for sql server) on unix.
This is what I know about the database his ip, port, DSN, login and Pwd.

On windows is very simple but in unix It seems to be more complicated ...

what is the file I had to configure on DSEngine ?

thanks in advance

Ultramundane · Post by **Ultramundane** » Wed Nov 29, 2006 9:02 am

On windows is very simple but in unix It seems to be more complicated ...

I think it is ludicrous speed years simpler on Unix.

To add an odbc connection vi the $DSHOME/.odbc.ini file and add the connection. That is all you have to do to configure the odbc connection on unix. Vi one file and add an entry for the DSN.

To make the configured DSN accessible by a project you must add the DSN to projects uvodbc.config file.

kjaouhari · Post by **kjaouhari** » Wed Nov 29, 2006 9:23 am

Thanks but could you please be more explicit ...

where is the $DSHOME/.odbc.ini and what does it mean add the connection ?

Could you give an example for : To make the configured DSN accessible by a project you must add the DSN to projects uvodbc.config file

thanks lot Ultramundane

kjaouhari · Post by **kjaouhari** » Wed Nov 29, 2006 10:31 am

ok thanks all !

i've just open odbc.ini with vi and I modified the file like this :

[SQLServer]
Driver=/export/DATASTAGE/Engine/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=MyDatabase
LogonID=MyDatabase
Password=Pxd_MyDatabase
Address=100.100.100.18,1479
QuotedId=No
AnsiNPW=No

then I had to modify the uvodbc.config file , I let you know if it works ...

chulett · Post by **chulett** » Wed Nov 29, 2006 10:48 am

I would suggest you not modify any of those example entries, but instead copy them and create new ones. That and name them approproately because the name in square brackets is what you will use to refer to the connection in the ODBC stage.

kjaouhari · Post by **kjaouhari** » Wed Nov 29, 2006 11:01 am

Thank you chulett for your suggestion if I understood :
I have to rename [SQLServer] by [MyDatabase] for example and let a sample for other connection isn't ?

Moreover, I have just modify the uvodbc.config by adding :
<SQLserver>
DBMSTYPE = ODBC

So now I think I have just to restart the DS server...

chulett · Post by **chulett** » Wed Nov 29, 2006 11:33 am

No, that <name> must match the other [name]... confusing, I know.

And no, there's no reason to restart DataStage.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 29, 2006 1:57 pm

If it's any consolation, on older versions of Windows (we're talking 3.1 and earlier here) you had to edit an ODBC.INI file. That's why the Registry entry is still called ODBC.INI.

kjaouhari · Post by **kjaouhari** » Thu Nov 30, 2006 6:22 am

Ok, I have modify the uvodbc.config by adding :
<MyDatabase>
DBMSTYPE = ODBC

But I think it will be more complicated to configure the dsenv ...

I don't know how to write the needed information in dsenv...

chulett · Post by **chulett** » Thu Nov 30, 2006 8:09 am

You don't need anything in the dsenv file that you don't already have.

kjaouhari · Post by **kjaouhari** » Thu Nov 30, 2006 9:13 am

I have this error message :

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

What do I need to check ?

kjaouhari · Post by **kjaouhari** » Thu Nov 30, 2006 10:05 am

this is what I have when I use DS_CONNECT :
Data Source information from /export/DATASTAGE/Projects/DEV/DEV_Primary_Dmt/uvodbc.config:

Data Source: localuv
DBMS Type: UNIVERSE
Network Type: TCP/IP
Host System: localhost 127.0.0.1
Service Name: uvserver

Data Source: PhoneNmber
DBMS Type: ODBC

Data Source: PhoneNumber
DBMS Type: ODBC

Do you have an idea ?

Ultramundane · Post by **Ultramundane** » Thu Nov 30, 2006 10:13 am

Do <name> and [NAME] match? The DSN name specified in the projects uvodbc.config must be the same as $DSHOME/.odbc.ini.

Does your uvodbc.config look similar to:
<PhoneNumber>
DBMSTYPE = ODBC

Does your .odbc.ini file look similar to:
[PhoneNumber]
Driver=/Ascential/DataStage/branded_odbc/lib/VMmsss21.so
Description=DataDirect SQL Server Wire Protocol
Database=
LogonID=
Password=
Address=I0050SQL01.ius.loc.com,1433
QuotedId=No
QEWSD=38891
AnsiNPW=No

kjaouhari · Post by **kjaouhari** » Thu Nov 30, 2006 10:37 am

Thanks Ultramundane for your help ,

this now the error message :

Data Source: PhoneNumber
DBMS Type: ODBC

>DS_CONNECT PhoneNumber
Enter username for connecting to 'PhoneNumber' DBMS [dsadm]: PhoneNumber
Enter password for PhoneNumber:
SQLConnect error: Status = -1 SQLState = IM002 Natcode = 0
[ODBC] [DataDirect][ODBC lib] Data source name not found and no default driver specified

I precise that I configures these 2 files not directly on the project directory and I'm working on a sun solaris system .

ray.wurlod · Post by **ray.wurlod** » Thu Nov 30, 2006 1:46 pm

uvodbc.config IS in the project directory. .odbc.ini is in the $DSHOME directory.

DSXchange

how to configure an odbc connection on unix

how to configure an odbc connection on unix

Re: how to configure an odbc connection on unix