Teradata DB connection using ODBC Thru IBM Information Server DataStage

Connectivity
N/A

Teradata DB connection using ODBC Thru IBM Information Server DataStage

Hi,
I am having issues to connect to TearaData using ODBC drivers provided by Teradata TTU's. If i try to connect using Information Server provided ODBC drivers, it is not able to source the libraries - could be due to 32 bit drivers of TeraGSS. Any one ran into similar issue?
Appreciate any information. Also i am looking for an example how to connect from Linux command line to Teradata using ODBC drviers?

Here is my environment info -

Operating system = RedHat Linux 5.5
Information Server = 8.5 version
TTU's on Linux box = 13.0 version with few patches for TPT

Appreciate,

~ST
20 REPLIES
Teradata Employee

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

how to connect from Linux command line to Teradata using ODBC drviers:

- Install Teradata ICU, TeraGSS and ODBC pkgs. in sequence.
- set ODBCINI environment variable to point to your odbc.ini, for example,

export ODBCINI=/opt/teradata/client/ODBC_64/odbc.ini

- Update DBCName entry under [testdsn] in $ODBCINI file, to point to your Teradata database. Supply database name or IP-address. For example, your [testdsn] section in odbc.ini looks like

[testdsn]
Driver=/opt/teradata/client/ODBC_64/lib/tdata.so
Description=Teradata running Teradata V1R5.2
DBCName=teradatadev<\b>
LastUser=
Username=
Password=
Database=
DefaultDatabase=

- export NLSPATH environment variable,

export NLSPATH=/opt/teradata/client/13.10/odbc_64/msg/%N.cat

- run the sample application installed by ODBC Driver pkg,

/opt/teradata/client/13.10/odbc_64/samples/C

- When it prompt, supply the DSN name(for example testdsn), database user name and password. The sample application connects to database, and can take SQL request input, execute and display the results.

Enthusiast

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

Hey Vhari, I know it's been a few years since you posted this but I'm still trying to get my redhat installation completed.  I had to install the unixODBC driver to get the installation completed.  I don't have root but was able to install the packages into a customer directory.  I have the LD_LIBRARY_PATH set for both the path to the unixODBC lib and the Teradata libs.  I have ODBCINI set to the ~/teradata/client/ODBC_64/odbc.ini path.  I have the TERADSN entries in the odbc.ini file.  I have edited the odbcinst.ini file in the same path with the following:

[ODBC DRIVERS]

Teradata=Installed

[TERADSN]

Driver=/home/mechid/teradata/client/ODBC_64/lib/tdata.so

APILevel=CORE

ConnectFunctions=YYY

DriverODBCVer=3.51

SQLLevel=1

My odbcinst -j looks like this.  Do I need to change those entries to the teradata paths from the unixODBC path?  If so do you know how?

=> odbcinst -j

unixODBC 2.3.2

DRIVERS............: /home/mechid/etc/unixODBC/odbcinst.ini

SYSTEM DATA SOURCES: /home/mechid/etc/unixODBC/odbc.ini

FILE DATA SOURCES..: /home/mechid/etc/unixODBC/ODBCDataSources

USER DATA SOURCES..: /home/mechid/teradata/client/ODBC_64/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

When I run the isql command to connect I"m getting:

  [ISQL]ERROR: Could not SQLConnect

I also have a perl script trying to use DBD::ODBC and I get this error:

  DBI 1.622-ithread default trace level set to 0x0/1 (pid 2011 pi 11136010) at DBI.pm line 276 via SendRumReports

.pl line 4

non-Unicode login6_sv

dbd_db_login6

    SQLDriverConnect 'TERADSN', 'jf3579', 'xxxx'

    SQLConnect 'TERADSN', 'jf3579'

         DESTROY for DBI::db=HASH(0x1115b120) ignored - handle not initialised

I think I'm close but I could be totally wrong.  

Teradata Employee

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

islanderman:

1)

You wrote : "I had to install the unixODBC driver to get the installation completed."

I do not quite follow this, why you had to install unixODBC ?

2)

I also noticed you updated the odbcinst.ini to add "[TERADSN]", I don't know what it is meant for.  You do not have update the odbcinst.ini that comes with Teradata ODBC driver package.  Please note, odbc.ini is not the same as odbcinst.ini.

Please follow the instructions in my earlier post in this thread, and let me know if you encounter any error at any of the step.  If you have the basic environment ready and connectivity test is successful, then you can work on configuring PERL.

Enthusiast

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

Sorry about the confusion.  First I tried to install DBD::ODBC from cpan but it complained that I needed the unixODBC driver, so I installed it.  I then installed DBD::ODBC and then I installed the following from teradata.  All of the installs were to a custom directory since i don't have root.

tdicu__linux_indep.14.10.00.00-1.tar.gz

tdodbc__linux_indep.14.10.00.00-1.tar.gz

TeraGSS_linux_x64__linux_indep.14.10.00.06-1.tar.gz

I am sorry I confused you with the odbcinst.ini file setup.  The instructions that I could find for editing the odbc.ini and the odbcinst.ini files are confusing.  If I follow your instructions from above , this is what I have in my odbc.ini file.  

[TERADSN]

Driver=/home/mechid/teradata/client/14.10/odbc_64/lib/tdata.so

Description=ATTSL06/EDWP

DBCName=cnewp.sbc.com

LastUser=

Username=

Password=

Database=

DefaultDatabase=

SessionMode=Teradata

UseNativeLOBSupport=Yes

TraceFile = /home/mechid/logs/tracedsn.log

Trace=Yes

[ODBC Data Sources]

TERADSN=tdata.so

My LD_LIBRARY_PATH has been updated to include paths to the unixODBC lib and the teradata libs.  My ODBCINI is set to /home/mechid/etc/odbc.ini and that is what is reflected above.  

The cat file is ~/teradata/client/14.10/odbc_64/msg/tdodbc.cat  and I have set the NLSPATH.  However I'm not sure what to kickoff from samples/C

=> ls -l

total 136

-rwxr-xr-x 1 d1car1m6 d1car1m6 31169 Jan 16  2013 adhoc

-rwxr-xr-x 1 d1car1m6 d1car1m6 81205 Jan 16  2013 adhoc.c

-rwxr-xr-x 1 d1car1m6 d1car1m6  3826 Mar 25 16:24 common.includes

-rwxr-xr-x 1 d1car1m6 d1car1m6 10099 Jan 16  2013 error.c

-rwxr-xr-x 1 d1car1m6 d1car1m6  1411 Jan 16  2013 Makefile

Finally some commands show

 => odbcinst  -s -q

[TERADSN]

And when I attempt to use isql I get (i left off the password)

=> isql -v TERADSN jf3579 xxxxxx

[ISQL]ERROR: Could not SQLConnect

Please let me know anything you'd like me to post.  One thing is I can't seem to get any trace file information from the isql command?

Teradata Employee

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

Teradata ODBC driver is supported with DataDirect ODBC driver manager (shipped with Teradata ODBC driver package), not the unixODBC.   DBC::ODBC module by default configured to work unixODBC, so you need to re-build DBD::ODBC module to work with DataDirect ODBC driver manager.

Instead of tweaking in what you have installed, I suggest you un-install all you have previously installed and follow instructions in my post on "18 Mar 2011" in this thread to setup Teradata ODBC driver.  Verify the connectivity using the tool located in

".../Teradata/client/14.10/odbc_64/bin/tdxodbc".

After verifying the connectivity, you can start configuring and building DBC::ODBC module to work with DataDirect ODBC driver manager, below link describes the steps.

http://www.datadirect.com/resources/resource-library/odbc-developer-center/odbc-tutorials/using-data...

Enthusiast

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

Thanks again for the help.  I will uninstall this like you said but I just want to verify this before starting.  I downloaded the odbc linux driver, version 14.10.00.00.  The order of installing these should be 

tdicu__linux_indep.14.10.00.00-1.tar.gz

TeraGSS_linux_x64__linux_indep.14.10.00.06-1.tar.gz

tdodbc__linux_indep.14.10.00.00-1.tar.gz

The DataDirect ODBC driver manager will be included here?  I just found out that the teradata odbc will be the only connection I will need to setup, so do I then still need to install DBD::ODBC or will the teradata installation be all that I need?

Is there any documentation on the tdxodbc tool?

Thanks again.

Teradata Employee

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

No, if you do not need to use PERL you do not need to re-configure DBD::ODBC module.

The installation order is correct, tdicu, teragss, tdodbc in order.  Yes, tdodbc package will also install DataDirect ODBC Driver Manager too.  No specific action is needed.

tdxodbc is a simple connectivity, SQL query execution test tool.  When invoked it prompts from ODBC DSN name, databaser user-name and password.  Once connected, it prompts from SQL request to execute or type 'quit' to close.

tdxodbc -h option can show help about command-line arguments.

Enthusiast

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

I have 1 more question if you don't mind.  After I install the teradata packages, how do I know which is the correct path for the odbc.ini file?  These were all installed the first time.

/home/mechid/teradata/client/ODBC_32/odbc.ini

/home/mechid/teradata/client/14.10/odbc_64/odbc.ini

/home/mechid/teradata/client/14.10/odbc_32/odbc.ini

/home/mechid/teradata/client/ODBC_64/odbc.ini

Teradata Employee

Re: Teradata DB connection using ODBC Thru IBM Information Server DataStage

/home/mechid/teradata/client/ODBC_32/odbc.ini

/home/mechid/teradata/client/ODBC_64/odbc.ini

Both are correct, but please use the above ones.  These are links to the ones with version(14.10) number.  If you use these paths(without version), you would not need to update your odbc.ini when you upgrade to later version in future.