IBM Support

DataStage - How to setup ODBC using with MS SQL database on Unix

Question & Answer


Question

Need help to set up ODBC using MS SQL database

Answer

1) CD to $DSHOME
Make a copy of the file .odbc.ini

Edit .odbc.ini - you will see the template for MSSQL.

[SQLServer Wire Protocol]
Driver=#BRANDED_DRIVER_DIR#/lib/VMmsss22.so Or 23 depends on what driver that you have
Description=DataDirect 5.2 SQL Server Wire Protocol
Address=SQLServer host,SQLServer server port
AnsiNPW=Yes
Database=db
LogonID=UID
password=PWD
QuotedId=No

Make a copy old the template
Enter the name for your DataSource name - for example [MSSQL_TEST]

You need to fill out the Address parameter
for example - ADDRESS=192.160.173.23,1433 , You can use hostname or IP address and 1433 is the port# - You need to check with DBA to see what port# is used
DATABASE= Yourdatabase name

2) Go to the project , there is a file call uvodbc.config.
Make a copy of the file then edit
Add the entry

<MSSQL_TEST>
DBMSTYPE = ODBC

3) Test odbc using DS_CONNECT defined for DataStage as follows:
A. If you haven’t previously done so, cd to $DSHOME and set up the DataStage environment by running dsenv: . ./dsenv
B. Invoke the DataStage engine shell: ./bin/uvsh
C. Log to the project: LOGTO project_name (Where project_name is case sensitive)
D. Get a list of available DSNs by typing: DS_CONNECT
E. Test the required connection by typing: DS_CONNECT DSN
Where DSN specifies the connection you want to test. Enter username and password as requested, you should then be connected to the specified database.
F. Enter .Q - to quit the connection

NOTE: For newer Information Server releases (8.5 forward) - please make sure you are using the NEW SQL Server Drivers - http://www-01.ibm.com/support/docview.wss?uid=swg21621427

[{"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF027","label":"Solaris"},{"code":"PF016","label":"Linux"},{"code":"PF010","label":"HP-UX"},{"code":"PF002","label":"AIX"}],"Version":"9.1;8.5;8.1;8.0.1;7.5.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21419753