Configuring access to ODBC data sources (Linux®, UNIX®)

After you install IBM Information Server, you need to configure the WebSphere DataStage ODBC connections.

Log in through putty on server using Administrator User name and Password. 1 thing to remember that log in user should have SUDO rights.
My suggestion here is that you use WINSCP to find or edit these three files instead of Putty.
About this task
You need to edit three files to set up the required ODBC connections. These are:

• dsenv
• .odbc.ini ( Note that this file is .odbc.ini not only odbc.ini
• uvodbc.config

All three are located in the $DSHOME directory. Copies of uvodbc.config are also placed in the project directories.

Simplest path to find these three files is

/opt/IBM/InformationServer/Server/DSEngine

My suggestion here is that you use WINSCP to find or edit these three files instead of Putty.

What are wired and non-wired Protocol drivers?

Non-Wire protocol drivers VS Wire protocol drivers

Basically there are two type of ODBC drivers being used in DataStage:

non-wire protocol drivers - require  database client software to be installed on the DataStage server (the drivers use the API supplied by the database client)
wire protocol drivers - do not require database client software (they communicate with the database directly)

Non-wire drivers require different setup information than wire drivers. Non-wire drivers require information about the location of the database client software. Wire drivers require information about the database itself.
For information about configuring the ODBC environment for your specific database, see the Data Direct Drivers Reference manual odbcref.pdf file located in the$DSHOME/Server/branded_odbc/books/odbc directory. You should also check the ODBCREAD.ME file in the branded_odbc directory. There is also an html file located in branded_odbc/odbchelp.

• dsenv file
  The WebSphere DataStage server has a centralized file for storing environment variables called dsenv in $DSHOME. $DSHOME identifies theWebSphere DataStage installation directory. The default directory is /opt/IBM/InformationServer/Server/DSEngine.
• .odbc.ini file
  The .odbc.ini files gives information about connecting to the database (wire protocol drivers) or the database client (non-wire protocol drivers). If your system uses a mix of drivers, your .odbc.ini file will contain a mix of entry types.
• uvodbc.config file
  Use the uvodbc.config file to specify the DSNs for the databases that you are connecting to through ODBC.
• Creating and binding a DB2 package to a DSN
  Before you connect to a DB2 DSN you need to bind DB2 packages to the DSN. You must bind a package to every DSN you are going to use to connect to a DB2 database.
• Configuring third party ODBC drivers
  You can use ODBC drivers from third parties for connecting WebSphere DataStage to different data sources. You might require to connect to a database not supported by the Data Direct driver pack, for example, Redbrick. You can use only one ODBC driver manager at a time. As a result, you cannot use third party drivers at the same time that you use the WebSphere DataStage drivers, and you must perform some reconfiguration to switch between the two types.

dsenv file

The WebSphere DataStage server has a centralized file for storing environment variables called dsenv in $DSHOME. $DSHOME identifies the WebSphere DataStage installation directory. The default directory is /opt/IBM/InformationServer/Server/DSEngine.
You might need to add new environment variables as you configure WebSphere DataStage to connect to different databases using plug-ins or ODBC drivers. Any environment variables you need for interactive use of ODBC drivers to make a connection to an ODBC data source must be added to the dsenv file. This lets the WebSphere DataStage server inherit the proper environment for ODBC connections.
For a connection using a wire protocol driver, there are no changes required to dsenv.
For a connection using a non-wire protocol driver, you generally need to specify the following information in the dsenv file:

• environment variables required by the database client software
• database home location
• database library directory
• the PATH environment variable

The following table shows the library path environment variables for each operating system.

Table 1. Library path environment variables for WebSphere® DataStage®
Operating system	Environment variables
AIX®	LIBPATH
Linux®	LD_LIBRARY_PATH
Solaris	LD_LIBRARY_PATH

The following example shows some typical entries for commonly used databases.

#Sybase 11 setup

LANG=

Export LANG

SYBASE=/export/home/sybase/sybase;export SYBASE

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$SYBASE/lib:/usr/lib:/lib;export LD_LIBRARY_PATH

# Oracle 9i

ORACLE_HOME=/space/oracle9i

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib;export 

LD_LIBRARY_PATH

ORACLE_SID=WSMK5

export ORACLE_HOME ORACLE_SID

#Informix XPS 9.3

INFORMIXDIR=/space/informix; export INFORMIXDIR

INFORMIXSERVER=solnew2.1;export INFORMIXSERVER

ONCONFIG=onconfig.solnew2; export ONCONFIG

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/lib/

esql:$INFORMIXDIR/lib/cli;export LD_LIBRARY_PATH

LANG=C

export LANG

#DB2 Version 9

DB2DIR=/opt/IBM/InformationServer/DB2;export DB2DIR

DB2INSTANCE=DB2inst1; export DB2INSTANCE

INSTHOME=/export/home/DB2inst1;export INSTHOME

PATH=$PATH:$INSTHOME/sqllib/bin:$INSTHOME/sqllib/adm:$INSTHOME/sqllib/misc

export PATH

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INSTHOME/sqllib/lib;export LD_LIBRARY_PATH

THREADS_FLAG=native;export THREADS_FLAG

Stop and restart the WebSphere DataStage server whenever you edit dsenv.

uvodbc.config file

Use the uvodbc.config file to specify the DSNs for the databases that you are connecting to through ODBC.
Entries in uvodbc.config have the following form:

<name>

DBMSTYPE = ODBC

name is the ODBC data source name (DSN), this should be the same name as specified in the .odbc.ini file. The spaces on either side of the equal sign are required.
The following example shows a typical uvodbc.config file:

[ODBC DATA SOURCES]

<localuv>

DBMSTYPE = UNIVERSE

network = TCP/IP

service = uvserver

host = 127.0.0.1

<Sybase1>

DBMSTYPE = ODBC

<Sybase2>

DBMSTYPE = ODBC

<Oracle8>

DBMSTYPE = ODBC

<Informix>

DBMSTYPE = ODBC

<DB2>

DBMSTYPE = ODBC

A copy of the uvodbc.config file is also placed in each WebSphere DataStage project directory. For example, the default path for projects on Linux^® is/opt/IBM/WDIS/Server/Projects/. This is useful where you configure a data source that is known to some projects but not others. By default, WebSphere DataStage searches the current project directory for a uvodbc.config file and, if it finds one, uses this in preference to the file in $DSHOME. If you alteruvodbc.config after creating projects you should copy the edited file to the project directories.

 

.odbc.ini file

The .odbc.ini files gives information about connecting to the database (wire protocol drivers) or the database client (non-wire protocol drivers). If your system uses a mix of drivers, your .odbc.ini file will contain a mix of entry types.
Configuration examples for the various platforms are provided in $DSHome/branded_odbc/IBM_Tools in the odbc.ini file.

• aix_4.2.odbc.ini for AIX^® systems.
• sun_4.2.odbc.ini for Solaris systems.
• linux_4.2.odbc.ini for Linux^® systems.

These files give the minimum parameter settings required to create a working odbc.ini file. They each give configurations for the following databases:

• DB2^® on Linux, UNIX^®, Windows^®, and AS400
• Oracle on Linux and UNIX
• Informix^® on Linux, UNIX, and Windows
• Microsoft^® SQL Server 2000 connection
• Sybase on Windows

1)  .ODBC.ini

Sample Entry for .ODBC.ini file( Log in through WINSCP instead of Putty for better results)

               Path=/opt/IBM/InformationServer/Server/DSEngine

Note: LOCALDB will be the name of your Database

[LOCALDB Wire Protocol]Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb224.so

Description=DataDirect DB2 Wire Protocol Driver

AddStringToCreateTable=

AlternateID=

Collection=OS/390 and AS/400 (Remove for DB2 UDB)

Database=LOCALDB

DynamicSections=100

GrantAuthid=PUBLIC

GrantExecute=1

IpAddress=localhost

IsolationLevel=CURSOR_STABILITY

Location=OS/390 and AS/400 (Remove for DB2 UDB)

LogonID=db2inst1

Password=sample

Package=DB2 package name

PackageOwner=

TcpPort=50001

WithHold=1

2)  UVODBC.config

Sample Entry for UVODBC.config file (Log in through WINSCP instead of Putty for better results)

Note: LOCALDB will be the name of your Database

<LOCALDB>

DBMSTYPE = UNIVERSE

network = TCP/IP

service = uvserver

host = localhost

3)  Dsenv

Note: This is the library file so please don’t make any changes in the existing file just enter the below entry with the path of all three files which are in second line at the end of the file and save it.

#DB2 Version 9

DB2DIR=/opt/IBM/InformationServer;export DB2DIR

DB2INSTANCE=db2inst1; export DB2INSTANCE

INSTHOME=/home/db2inst1;export INSTHOME

PATH=$PATH:$INSTHOME/sqllib/bin:$INSTHOME/sqllib/adm:$INSTHOME/sqllib/misc

export PATH

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INSTHOME/sqllib/lib;export LD_LIBRARY_PATH

THREADS_FLAG=native;export THREADS_FLAG