Using SQuirrel SQL Client with Teradata

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Teradata Employee

Using SQuirrel SQL Client with Teradata

Teradata offers many ways to connect and query our database with our Tools and Utilities(TTU) package - BTEQ, BTEQWin, SQLA and the TD Eclipse plug-in.

These are great tools and we're here to promote them and help you get the most from them. But we're techies too and we understand that there are times when you want to use your own tool of choice. We get it and we're here to help you with this option as well.

Let's take SQuirrel as an example. This is a great, popular SQL client tool and yes, it plays well with Teradata. Here's a quick start-up guide to get you running your Teradata queries with it in no time.

What do we need?

Installing Teradata's JDBC driver.

The Teradata JDBC package can be downloaded as either a ZIP file or a TAR file. Basically it's 2 jar files that you need to put into your classpath:

  • terajdbc4.jar
  • tdgssconfig.jar

In this example, I created a new folder, "c:\Program Files\Java\myjars", extracted the files to this folder and finally updated my classpath environment to include both jar files explicitly.

Be sure to update your classpath before starting up SQuirrel to ensure that the new path environment is being used.

Configuring SQuirrel for Teradata

Start up SQuirrel and click on the Drivers tab along the left edge.

This lists all the database connectivity options currently configured and available. What we want to do is add our Teradata JDBC driver, so go ahead and click the green PLUS icon.

Scrolling to the bottom of the list of drivers should display our Teradata jar files in our Java classpath. I've added some default values here as well that can be used for a local TD Express installation.

Click OK and you should see a green success message at the bottom of the window.

Now click on the Aliases tab on the left edge. Creating an Alias is where we'll add your username/password parameters in order to connect to the database. Once again, click the green PLUS icon.

I've named this Alias "Teradata local", used the same JDBC url as my connection string and also the TD Express default user, tduser (and tduser as the password - shhh!).

Connect

You should now be able to click the Connect icon to establish a session with Teradata and start browsing your database objects. Here's a screen shot with the TD Express sample objects:

Here's the output from everyone's favorite query:

SELECT Name, Salary 
FROM retail.Employee
WHERE Salary > 100000
ORDER BY Salary DESC

That's it. SQuirrel is now configured to connect to Teradata and we have a nice tool for browsing the database objects and running queries.

Have fun and go nuts!

14 REPLIES
Fan

Re: Using SQuirrel SQL Client with Teradata

Brilliant.
Fan

Re: Using SQuirrel SQL Client with Teradata

how to create a stored procedure here

Re: Using SQuirrel SQL Client with Teradata

This is great! Now i can use code completion to easily type in table/column names :-)

thanks!
Enthusiast

Re: Using SQuirrel SQL Client with Teradata

I downloaded the jdbc drivers and installed them but can't connect. The url I entered is jdbc:teradata:
I also tried the IP address. Do I need a port number or something?
I've been running Squirrell using their JBCB to ODBC Bridge and it works ok. Does the jdbc driver give me any advantages?
Enthusiast

Re: Using SQuirrel SQL Client with Teradata

I figured out my mistake, the proper URL is:
jdbc:teradata://hostname
Replace hostname with the name of the network host. You need the two slashes. I also downloaded the sample code and found there are some options, for example:
jdbc:teradata://hostname/TMODE=ANSI,CHARSET=UTF8

Re: Using SQuirrel SQL Client with Teradata

Great instructions Mike!

Red - Thanks your example helped me figure out LDAP usage:
jdbc:teradata:/hostname/LOGMECH=LDAP

I don't see Macros listed in the object hierarchy ... is there a Teradata plugin for Squirrel that adds that?
Teradata Employee

Re: Using SQuirrel SQL Client with Teradata

We don't have any specific plug-ins for Squirrel. If you need support for Teradata specific features, try either our SQL Assistant tools (Windows and Java editions) or our Eclipse plug-in. These can be found on our download page:
http://downloads.teradata.com/download

Re: Using SQuirrel SQL Client with Teradata

Do you know any tool that can be used as a Teradata client that doesnt require installation ?

Re: Using SQuirrel SQL Client with Teradata

Hi.. I installed Teradata JDBC driver in squirrel and i am trying connect DB.

I am getting below error when i connect to user tables.

"Error: [NCR] [Teradata JDBC Driver] [TeraJDBC 03.01.00.103] Unrecognized data type: 601"

There is no error reported while querying System tables.

Please help.

Thanks,

Venkat