Before we can connect to the Teradata database using ODBC stage in DataStage, we need to define the Data Source on the DataStage server.
In Windows environment, we can defined the Data Source by going to Control Panel -> Administrative Tools -> Data Sources (ODBC) . Add a system DSN.
Note: DataStage cannot work on user DSN.
In Unix environment,
three files need to be edited for ODBC configuration.
1. dsenv file - contains the environment variables to connect to different databases
2. .odbc.ini file - contains the names and the data sources available to the Unix users.
3. uvodbc.config file - contains the DSNs name that connect through ODBC
Example of dsenv file#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<o:p></o:p></span></p>
Example of an .odbc.ini file[ODBC]
InstallDir=/usr/odbc
Trace=0
TraceDll=/usr/odbc/lib/odbctrac.so
TraceFile=/usr/odbcusr/joe/trace.log
TraceAutoStop=1
[ODBC Data Sources]
financial=tdata.so
[financial]
Driver=/usr/odbc/drivers/tdata.so
Description=NCR 3600 running Teradata V2R5.1
DBCName=123.45.67.10
DBCName2=123.45.67.11
DBCName3=123.45.67.12
Username=odbcadm
Password=
Database=
DefaultDatabase=sales
Example of uvodbc.config[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1
<TEST_DSN>
DBMSTYPE = ODBC
Once we are done with the configuration, we should do a test to the ODBC driver connectivity.
Step 1. Change to $DSHOME directory. Then type in . ./dsenv
Step 2. Run the DataStage server shell by type in ./bin/dssh
Step 3. Log on to the project using LOGTO your_project_name
Step 4. Use DS_CONNECT to get the list of DSNs available.
Step 5. Type DS_CONNECT dsn_name to test the connectivity.
Referenceshttp://publib.boulder.ibm.com/infocenter/iisinfsv/v8r0/index.jsp?topic=/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/tasks/wsisinst_Third_Party_ODBC_Drivers.html
