IBM Support

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

Question & Answer


Need help to set up ODBC using MS SQL database


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/ Or 23 depends on what driver that you have
Description=DataDirect 5.2 SQL Server Wire Protocol
Address=SQLServer host,SQLServer server port

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=,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


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 -

[{"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