Establishing a new ODBC Connection

1) How do you establish a new Oracle/ ODBC database connection in Datastage?

-- I was not sure of the answer to this question. I just said that we need to define the appropriate environment variables for the DSN, User name and Password.

After googling later, I found the following information

'You need to edit three files to set up the required ODBC connections. These are:
dsenv
.odbc.ini
uvodbc.config
All three are located in the $DSHOME directory. Copies of uvodbc.config are also placed in the project directories.

dsenv fileThe 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.

.odbc.ini fileThe .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 fileUse the uvodbc.config file to specify the DSNs for the databases that you are connecting to through ODBC.

Check this link for more information
http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r0/index.jsp?topic=/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/tasks/wsisinst_Configuring_ODBC_Connections.html

This information is basically from datastage Installation guide.

I think the below points from the Parallel Job Developer's Guide will also be relevant


You need to be running Oracle 8 or better, Enterprise Edition in order
to use the Oracle Enterprise Stage.
You must also do the following:

1) Create the user defined environment variable ORACLE_HOME and
set this to the $ORACLE_HOME path (e.g., /disk3/oracle9i).

2) Create the user defined environment variable ORACLE_SID and
set this to the correct service name (e.g., ODBCSOL).

3) Add ORACLE_HOME/bin to your PATH and ORACLE_HOME/lib to
your LIBPATH, LD_LIBRARY_PATH, or SHLIB_PATH.