IBM InfoSphere Information Server DataStage 11.5 ODBC Connection Configuration for Microsoft SQL

First, make sure to the latest ODBC drivers are installed from IBM Fix Central

Note: The .odbc.ini file template defaults to an old driver. Make sure to use the latest driver. As of the time I updated this document, the latest was VMsqls00.so

Also, expand ODBC Conenction Configuration on the left to see the working connection configuration in use.

Edit the .odbc.ini file and add the connection needed

sudo - dsadm
cd /IBM/InformationServer/Server/DSEngine
vi .odbc.ini

[MSSQLDB]
Driver=/IBM/InformationServer/Server/branded_odbc/lib/VMsqls00.so
Description=DataDirect SQL Server Wire Protocol driver to STARREZDB
Database=snMSSQLDB
LogonID=MSSQL_AD_user
Password=<PASSWORD>
Address=mssqldb.mytechdocs.test,1433
QuotedId=No
AnsiNPW=No
AuthenticationMethod=9
Domain=AD

image2016-10-7-14-33-0

Edit the odbc.config file for each project needing the connection

cd /IBM/InformationServer/Server/Projects/MSSQLDSProj
vi uvodbc.config

<MSSQLDB>
DBMSTYPE = ODBC

image2016-10-7-14-33-21

Test the connection with DataStage Shell

cd /IBM/InformationServer/Server/DSEngine
. ./dsenv
./bin/uvsh
LOGTO MSSQLDSProj
DS_CONNECT MSSQLDB
Enter username for connecting to 'MSSQLDB' DBMS [dsadm]:   MSSQL_AD_user
Enter password for MSSQL_AD_user:
SQLConnect info-only:   Status = 1   SQLState = 00000   Natcode = 32648
MSSQLDB>

The prompt should change to the following after successfully logging in:

MSSQLDB>

image2016-10-7-14-34-52

MSSQL and SSL

If you have a connection that requires SSL, add the following lines to the end of the connection section in the .odbc.ini file

EncryptionMethod=1
ValidateServerCertificate=0

Useful Sites I found to document this

http://www-01.ibm.com/support/docview.wss?uid=swg21419753

http://www.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.conn.common.usage.doc/topics/r_codbccon_cn_Database_drivers.html

 

Download the lastest SQL drivers from IBM Fix Central for our infosphere version

https://www-945.ibm.com/support/fixcentral/swg/doSelectFixes?options.selectedFixes=is_ddodbc_7.1.6_server_linux64&continue=1

Please follow and like us:

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.