ODBC.ini File Help

antonyraj.deva · Post by **antonyraj.deva** » Sat Sep 12, 2009 4:12 am

Hi All,
My requriement is to connect to a DB2/400 (iSeries) & SQL Server sources with the Datastage server in AIX 5.3. and the target is DB2 UDB which is installed in the server itself. I'm a bit confused about the odbc.ini file settings. I herewith attach the sample odbc.ini file I'd modified based on my understanding of the documentations available. Kindly requesting to correct me if I'm wrong in any of the settings.

[DB2 Wire Protocol]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (Remove for DB2 UDB)
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=xxx.x.xxx.xx
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (Remove for DB2 UDB)
LogonID=xyz123
Password=xxxxxx
Package= xxxxxx
PackageOwner= idb2dev1
TcpPort=8471
WithHold=1

[SQLServer]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=recovery
LogonID=xxxxxxxx
Password=xxxxxxx
Address=sql2k5pmlo1,1433
QuotedId=No
AnsiNPW=No

Thanks in Advance...

hailun · Post by **hailun** » Sat Sep 12, 2009 6:24 am

Is there any error encountered,if so can you post the error information here, usually we just need add coresponding DSN entry in the .odbc.ini file under $DSHOME....

antonyraj.deva wrote:Hi All,
My requriement is to connect to a DB2/400 (iSeries) & SQL Server sources with the Datastage server in AIX 5.3. and the target is DB2 UDB which is installed in the server itself. I'm a bit confused about the odbc.ini file settings. I herewith attach the sample odbc.ini file I'd modified based on my understanding of the documentations available. Kindly requesting to correct me if I'm wrong in any of the settings.

[DB2 Wire Protocol]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (Remove for DB2 UDB)
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=xxx.x.xxx.xx
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (Remove for DB2 UDB)
LogonID=xyz123
Password=xxxxxx
Package= xxxxxx
PackageOwner= idb2dev1
TcpPort=8471
WithHold=1

[SQLServer]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=recovery
LogonID=xxxxxxxx
Password=xxxxxxx
Address=sql2k5pmlo1,1433
QuotedId=No
AnsiNPW=No

Thanks in Advance...

antonyraj.deva · Post by **antonyraj.deva** » Sat Sep 12, 2009 6:34 am

There's no error so far since I haven't tried to run any job using ODBC till now. Do I need to create the ODBC profiles also on the local system where the DS client is installed? and $DSHOME points to /datastage/dsadm/Ascential/DataStage/DSEngine> is this the default location for DSHOME?

hailun · Post by **hailun** » Sat Sep 12, 2009 6:44 am

Yes /datastage/dsadm/Ascential/DataStage/DSEngine is the home path for Datastage, the .odbc.ini file you are modifying is under this path,right...

Now you need add approciate DSN entry in that file.

[a]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=OS/390 and AS/400 (Remove for DB2 UDB)
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=xxx.x.xxx.xx
IsolationLevel=CURSOR_STABILITY
Location=OS/390 and AS/400 (Remove for DB2 UDB)
LogonID=xyz123
Password=xxxxxx
Package= xxxxxx
PackageOwner= idb2dev1
TcpPort=8471
WithHold=1

[c]
Driver=/datastage/dsadm/Ascential/DataStage/branded_odbc/lib/VMmsss20.so
Description=DataDirect SQL Server Wire Protocol
Database=recovery
LogonID=xxxxxxxx
Password=xxxxxxx
Address=sql2k5pmlo1,1433
QuotedId=No
AnsiNPW=No

After that you need add entry a and c to another file named uvodbc.config under the same path also the uvodbc.config file under project path if not newly created after modifying $DSHOME level uvodbc.config file....

Then you can add odbc stage in your job before uisng the DSN a or c....

antonyraj.deva wrote:There's no error so far since I haven't tried to run any job using ODBC till now. Do I need to create the ODBC profiles also on the local system where the DS client is installed? and $DSHOME points to /datastage/dsadm/Ascential/DataStage/DSEngine> is this the default location for DSHOME?

rajngt · Post by **rajngt** » Sat Sep 12, 2009 6:59 am

this needs to be update if you are trying to connect using ODBC stage otherwise use dsenv file for DB2 connection

chulett · Post by **chulett** » Sat Sep 12, 2009 7:13 am

antonyraj.deva wrote:Do I need to create the ODBC profiles also on the local system where the DS client is installed?

No, all of this happens only on the DataStage server, nothing needs to be done on the client.

antonyraj.deva · Post by **antonyraj.deva** » Mon Sep 14, 2009 10:50 am

Thanks a lot Craig & Hailun for the solutions provided.

Rajngt, thanks to you too. Could you kindly tell how a DB2 connection can be made from dsenv file in AIX systems.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 14, 2009 5:26 pm

The dsenv file only sets environment variables. It does not create connections.

antonyraj.deva · Post by **antonyraj.deva** » Thu Sep 17, 2009 5:21 am

Thanks a lot to everybody, I now have an working ODBC connection.
I'm marking the topic as resolved.

DSXchange

ODBC.ini File Help

ODBC.ini File Help

Re: ODBC.ini File Help