Oracle Teradata Connection

Toad Data Point & Toad Intelligence Central
1

I have Oracle and Teradata ODBC drivers installed, and can connect to both at the same time. But when I try to build a query in cross-connection mode, I get the error *Unable to connect to any of the specified MySQL hosts. * I don’t have MySQL, and have never used MySQL, so there must be some other problem that is causing this.

2

That error usually happens when there is an issue with the local storage, which we use in part with X-Query. Are you able to connect to local storage?

3

This is my first experience with Toad Data Point. I’m not sure what connecting to local storage refers to. I am able to connect to the Teradata data base and then also connect to the Oracle database (while still connected to the Teradata database), and am able to run queries against each independently. It is when I switch the query build mode to cross connection that I get the MySQL error.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Wednesday, September 30, 2015 5:27 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Oracle Teradata Connection

RE: Oracle Teradata Connection

Reply by Ryan Johnson

That error usually happens when there is an issue with the local storage, which we use in part with X-Query. Are you able to connect to local storage?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

4

Lance, here is a reply I recently received from Igor that may address your question

Reply by Igor Manokhin

Sorry for the late response. X-Query is actually run on the local hub which is MySql-based engine. That is why X-Queries should conform with MySql syntax. That is why DATEDIFF with 3 params is producing the error.

Igor.

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"lance.christenson" bounce-lancechristenson@toadworld.com 10/1/2015 9:19 AM >>>

RE: Oracle Teradata Connection

Reply by lance.christenson
This is my first experience with Toad Data Point. I’m not sure what connecting to local storage refers to. I am able to connect to the Teradata data base and then also connect to the Oracle database (while still connected to the Teradata database), and am able to run queries against each independently. It is when I switch the query build mode to cross connection that I get the MySQL error.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

5

Well, it is likely a hub issue. You will need to reset it. You will lose anything you had in the local storage.

First, open TDP, go to options, then Local Storage -> General. You should see this:

LocalStorage.png
LocalStorage.png860×532 35.2 KB

Make note of this folder and shut down TDP. Navigate to this folder and delete or rename it. Restart TDP and let me know if it is working again or not :slight_smile:

6

Here are the few steps that are involved.

  1. Install the ODBC drivers for the teradata in oracle user home.
  2. create a .odbc.ini in oracle user home with the details as database name , IP address and few more details.
  3. Update the listener.ora with a new service. shutdown and restart
  4. Create an entry into the tnsnames.ora . Make sure the host should be pointing to the server where the listener is running. Also make sure you have (HS=...) in the tnsnames entry.
  5. Create a dblink.

I have a document somewhere which i created to do this. Once i find it i will post it here.

Hope this helps.

  1. Database link, it contains the password
    create database link "ABC.COM.AU"
    connect to ABC
    identified by ""
    using 'DEF';

  2. tnsnames.ora
    The IP address is pointed to any server which has listener.ora, .odbc.ini and odbc set up
    DEF =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IP_ADDRESS)(PORT = 1521))
    )
    (
    CONNECT_DATA = (SID = DEF)
    )
    (HS=OK)
    )

  3. listener.ora on the host pointed by the tnsnames.ora of the database server, in this case IP_ADDRESS
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = DEF)
    (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
    (PROGRAM = hsodbc)
    (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/10.1.0/db_1/lib32:/u01/home/oracle/odbc/lib)
    )

  4. .odbc.ini
    [DEF]
    Driver=/u01/home/oracle/odbc/drivers/tdata.so
    Description=Group Data Warehouse V2R4.1
    DBCName=IP_ADDRESS
    Database=db_name
    DefaultDatabase=db_name