Skip to content

IBM InfoSphere: Configure ODBC Access to MS SQL 2008

This post explains how to configure the bundle DataDirect IQ ODBC driver to connect to Microsoft SQL Server 2008 data sources.

Environment

  • IBM InfoSphere DataStage 11.5
  • IBM IAX 7.1
  • MS SQL 2008

How to Configure

  1. Go to the directory $DSHOME.
  2. Optional: Make a backup if the .odbc.ini
  3. Edit the .odbc.ini file as follow:

    3.1 On the section [ODBC Data Sources] add:

    MyMSSQLDb=DataDirect SQL Server Wire Protocol Driver
    

    3.2 Create a new Data Source entry as follow:

    [MyMSSQLDb]
    Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss00.so
    Description=DataDirect SQL Server Wire Protocol driver
    Database=MyMSSQLDb
    Address=myServer,1433
    QuotedId=No
    AnsiNPW=No
    
  4. Add the new DS to teh uvodbc.config file on $DSHOME (and/or in every project directory).
    DBMSTYPE = ODBC
    

How to Test

See IBM InfoSphere DataStage: Test OBDC Connectivity

Advertisements

IBM InfoSphere DataStage: Test OBDC Connectivity

This post explains how to test ODBC connectivity to data sources.

How to Test

  1. Go to the directory $DSHOME.
  2. Source dsenv
      . ./dsenv
    
  3. Start the IBM InfoSphere Information Server engine shell.
    ./bin/dssh
    
  4. Log into the dstage1 project.
    DataStage Command Language 11.5 Licensed Materials - Property of IBM
    (c) Copyright IBM Corp. 1997, 2015 All Rights Reserved.
    DSEngine logged on: Tuesday, January 05, 2016 11:18
    
    >LOGTO dstage1
    
  5. List the available data sources.
    >Ds_CONNECT
    
    Data Source information from /opt/IBM/InformationServer/Server/Projects/dstage1/uvodbc.config:
    
    Data Source:       localuv
      DBMS Type:         UNIVERSE
      Network Type:      TCP/IP
      Host System:       localhost  127.0.0.1
      Service Name:      uvserver
    
    Data Source:       MySAPIqDb
      DBMS Type:         ODBC
    
    >
    
  6. Test the connection.
    >DS_CONNECT prodDwIqDb
    Enter username for connecting to 'prodDwIqDb' DBMS [root]: myDBuser
    Enter password for myDBuser:
    prodDwIqDb> select count(*) from sysobjects;
       count()
    ----------
          2504
    
    1 row selected
    prodDwIqDb> .Q
    Disconnecting from 'prodDwIqDb'
    >Q
    

References

IBM Knowledge Center. Testing ODBC driver connectivity. Retrieved from https://www-01.ibm.com/support/knowledgecenter/SSZJPZ_11.3.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_Testing_ODBC_Driver_Connectivity.html

IBM InfoSphere DataStage: Configure ODBC Access to SAP IQ 16.0

This post explains how to configure the bundle DataDirect IQ ODBC driver to connect to SAP IQ 16 data sources.

Environment

  • IBM InfoSphere DataStage 11.5
  • IBM IAX 7.1
  • SAP IQ 16.0 SP08

How to Configure

  1. Go to the directory $DSHOME.
  2. Optional: Make a backup if the .odbc.ini
  3. Edit the .odbc.ini file as follow:

    3.1 On the section [ODBC Data Sources] add:

    MySAPIqDb=DataDirect Sybase IQ Wire Protocol
    

    3.2 Create a new Data Source entry as follow:

    [MySAPIqDb]
    Driver=/datastage/IBM/InformationServer/Server/branded_odbc/lib/VMsyiq00.so
    Description=DataDirect Sybase IQ Wire Protocol Driver
    ApplicationUsingThreads=1
    ArraySize=50
    CursorCacheSize=1
    Database= MySAPIQDb   # replace by the name of your database
    DefaultLongDataBuffLen=1024
    EnableDescribeParam=0
    EnableQuotedIdentifiers=0
    MaxPoolSize=100
    MinPoolSize=0
    NetworkAddress=MySQOIQServer,2638  # replace by your server,port
    OptimizePrepare=1
    PacketSize=0
    RaiseErrorPositionBehavior=0
    
  4. Add the new DS to teh uvodbc.config file on $DSHOME (and/or in every project directory).
    DBMSTYPE = ODBC
    

How to Test

See IBM InfoSphere DataStage: Test OBDC Connectivity

References

IBM Knowledge Center. Specifying data source names in the uvodbc.config file. Retrieved from https://www-01.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_uvodbc_config_File.html