TPT unable to find ODBC DSN name, however Tdxodbc can

Tools
Enthusiast

TPT unable to find ODBC DSN name, however Tdxodbc can

I'm using tbuild to try and move data from an odbc source to teradata. However, I'm receiving the following error

$ODBC: TPT17122: Error: unable to connect to data source
$ODBC: TPT17101: Fatal error received from ODBC driver:
              STATE=IM002, CODE=0,
              MSG='[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded' 

Looking at the detailed logs using tdlog & ODBCTraceLevel=Oper  I see that it is attempting to connect to the correct dsn name

CONVERTATOW leaving
CONNECTODBC: dumping the content dsn:lax-hive
PC_HEXDUMP: entering
PC_HEXDUMP: max bytes, reclen: 1024, 32
  Hexadecimal formatted display from address F9200170 for 32 bytes.
   0000  6C 61 78 2D.68 69 76 65.00 00 00 00.00 00 00 F0  *lax-hive.......?*
   0010  01 00 00 00.00 00 00 F0.00 00 00 00.00 00 00 00  *.......?........*
PC_HEXDUMP: leaving
CONNECTODBC: henv address: 0x'f9200180'
CONNECTODBC: hdbc address: 0x'f901b900'
CONNECTODBC: &hdbc address: 0x'ff28aa0'
CONNECTODBC: SQLConnect result: -1
$ODBC: TPT17122: Error: unable to connect to data source
**** 13:23:07 TPT17122: Error: unable to connect to data source
OUTPUTODBCERROR: entering
OUTPUTODBCERROR: henv, hdbc, hstmt: f9200180, f901b900, 0
OUTPUTODBCERROR: SQLError() return code: 0
CONVERTWTOA entering
CONVERTWTOA entering
$ODBC: TPT17101: Fatal error received from ODBC driver:
              STATE=IM002, CODE=0,
              MSG='[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded' 
**** 13:23:07 TPT17101: Fatal error received from ODBC driver:
              STATE=IM002, CODE=0,
              MSG='[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded' 
OUTPUTODBCERROR: SQLError() return code: 100

However I can connect to this dsn using isql, iodbctest, & tdxodbc... Is tbuild attempting to read my odbc.ini files from a non-default location?

 

 

7 REPLIES
Teradata Employee

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

Platform?

TPT Version?

Script?

odbc.ini file?

-- SteveF
Enthusiast

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

platform: osx
tpt version 16.10
script: PTS00012A  (under samples)
job variables file excerpt

/*******************************************************/
/* TPT ODBC operator attributes                         */
/********************************************************/
,ODBCDSNname       = 'lax-hive'
/*,ODBCUserName      = '<UserName'
,ODBCUserPassword  = '< ODBC User Password>'*/
,ODBCTraceLevel    = 'oper' /*None,oper*/

odbc.ini

[ODBC Data Sources]
lax-hive = [Cloudera ODBC Driver for Apache Hive] [lax-hive] Description=Cloudera ODBC Driver for Apache Hive DSN Driver=/opt/cloudera/hiveodbc/lib/universal/libclouderahiveodbc.dylib Host=host-name Port=10000 Schema=default ServiceDiscoveryMode=0 HiveServerType=2 AuthMech=1 ThriftTransport=1 KrbHostFQDN=host-name2 KrbServiceName=hive KrbRealm=realm-name SSL=0
Teradata Employee

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

The ODBC operator should simply call the driver manager, and the driver manager determines the search locations.

Does your ODBCINI environment variable point to the correct odbc.ini file?

Maybe it's just a copy/paste error, but your example is missing the [ODBC Data Sources] section header before the list of DSNs.

Enthusiast

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

the header was a copy paste error; I've edited above to reflect the correct situation.
I don't have a ODBCINI Environment variable set although I've tried setting it via `export ODBCINI=/Library/ODBC/odbc.ini` but receive the same results as before
Example test

2017-08-03 11:07:07 user at user-imac in ~/Desktop/sample_tdload
$ tdxodbc 

Enter Data Source Name: lax-hive
Enter UserID: 
Enter Password: 

Connecting with SQLConnect(DSN=lax-hive,UID=,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-
DBMS name           = -Hive-
DBMS version        = -1.1.0-cdh5.11.1-
Driver name         = -Cloudera ODBC Driver for Apache Hive-
Driver version      = -2.5.22.1014-
Driver ODBC version = -03.80-


I've tried the 4 combinations
with unixodbc, with/without environment variable
with iodbc, with/without environment variable

but am always able to connect via tdxodbc and unable to connect from tpt receiving the error 

$ODBC: TPT17101: Fatal error received from ODBC driver:
              STATE=IM002, CODE=0,
              MSG='[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded' 

Is there a debug config option to have tpt list the DSN Names that it can see / where it is looking?

Teradata Employee

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

TPT doesn't "look" anywhere, it just passes the DSN and options to the driver manager (iODBC on OS X).

 

Maybe the ODBC Operator instance finds the file but can't read it. What are the file permissions for your odbc.ini?

Enthusiast

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

Hey fred both of the files are set as 644.

stat -f "%OLp" odbcinst.ini 
644

stat -f "%OLp" odbc.ini 
644


at this point i've triend uninstalling TTU, reinstalling TTU, restarting, uninstall iodbc & reinstalling, restarting, environment variables pointing to the actual .ini files , soft links in all the other possible locations... not sure where to go from here...

The recommend steps here don't see easy for an end-user like myself to check

Is there any significance to the errors below? (I don't have a username or password for my odbc connection)

**** 19:04:36 Connecting to RDBMS:    'lax-hive'
**** 19:04:36 Connecting with UserId: <not supplied by user>
$ODBC: connecting sessions
**** 19:04:36 Error -795158777 converting user name to UTF8 
**** 19:04:36 Error -795158777 converting user password to UTF8
PC_HEXDUMP: entering
PC_HEXDUMP: max bytes, reclen: 1024, 32
  Hexadecimal formatted display from address 54D01C00 for 32 bytes.

   0000  6C 61 78 2D.68 69 76 65.00 00 00 00.00 00 00 50  *lax-hive.......P*

   0010  02 00 00 00.00 00 00 50.00 00 00 00.00 00 00 00  *.......P........*

PC_HEXDUMP: leaving
$ODBC: TPT17122: Error: unable to connect to data source
**** 19:04:36 TPT17122: Error: unable to connect to data source
Teradata Employee

Re: TPT unable to find ODBC DSN name, however Tdxodbc can

Well, we need to determine whether the connection is failing due to the driver library file not being found, or whether it is because you did not supply a username and password.

 

On the "bad news" side, you are actually trying to use TPT in an unsupported manner.

We do not support using ODBC to connect to Hadoop.

 

We only support the use of the bundled drivers we provide and the databases to which they connect (and against which we are certified).

 

Those databases are:

Oracle

DB2

SQLServer

MySQL

Postgres

 

Anything else is "use at your own risk".

Should the connection work? Yes.

As to how much time we (the TPT team) can devote to helping resolve this, I cannot say.

(Fred is not part of the TPT team but is very knowledgeable about TPT in general and is assisting on his own.)

 

TPT does have nother means to connecting to Hadoop.

We can pull data from files using HDFS, and we can also retrieve data through the TDCH application.

 

-- SteveF