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/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
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=188.8.131.52,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
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
Was this topic helpful?
16 June 2018