Linux (or Windows or UNIX) Tools that work with Teradata
Part 1
Overview and discussion –
This paper presents some tool(s) that allow one to work in a Linux environment, almost any Linux distribution, working with a Teradata DBMS. Many Teradata tools have been ported to several Linux and UNIX distributions; however there are a number of Linux distributions that casual users are more likely to use such as Ubuntu, more of an Everyperson’s Linux, perhaps less enterprise ready, but also not as exacting in hardware and software requirements. The Teradata tools in the SUSE or Redhat Linux distributions as yet have not been ported to work with Ubuntu, Slax or other Slackware variant, etc.
Not all Teradata tools present in a Linux (SUSE for example) environment are represented in this paper and the tools that are discussed do not have exactly the same capabilities as the equivalent Teradata tool. But the tools discussed do allow for basic Teradata functionality such as visual querying, command line SQL submission, simple Fastloading, and simple TPumping.
The tool(s) have been used and/or tested on 1) a Xubuntu 8.04 'Hardy Heron' VMWARE appliance, and 2) Slax 6.0.9 bootable from a USB or CDRom, and 3) Windows XP SP3. The Ubuntu Linux distribution seems to be a more complete distribution having a fuller repertoire of Linux type tools in the release, but seems to be only self contained, i.e., you cannot get to your Window’s NTFS drives without some work. The Slax version is not supplied as a VMware appliance; however there is either a CDRom or USB drive bootable version, either version having the capability to read/write to your Windows NTFS disk drive(s). If you are VMware literate it is probably possible, maybe easy, to build a VMware image of one of these Slax OSs.
While the Slax distribution starts out smaller and less Linux capable than the Ubuntu distribution, additional modules are extremely easy to add - even before downloading. You can pick and choose modules to add before downloading. Slax offers a big advantage in that one can boot into Linux on almost any PC without worrying about having VMware software installed, essentially the ability to carry a PC on a memory stick, and then having Slax be able to use (R/W) the PCs hard drives. One caution - if booting into Slax from a WIFI portable PC it may have difficulties in finding the WIFI card (no problem if wire connected) but there is a network utility in the menu that makes finding and setting up the WIFI easier.
You can view and download the Linux distributions (or others) and VMware player at
http://www.vmware.com/appliances/ - Xubuntu
http://www.slax.org/ - Slax
http://www.vmware.com/download/player/ - VMware player
To either basic distribution you will need to add the Sun Java JRE, Slax can add a JRE before downloading, with Ubuntu the JRE is relatively easy, simply start a shell, say java, and follow the directions given to install. BTW, both distributions include at least one internet browser so you can use the browser to gain access to company portals, etc, and both distributions include simple POP-3 Email clients to allow you at least some access to the outside internet world. I also recommend adding the newest version of Python available since future discussions can make use of it.
The remainder of this paper will address only the visual querying capability; follow up paper(s) will deliver Java code which allows for Teradata Fastload and Tpump capabilities.
ExecuteQuery – the first tool in the toolkit
The first tool in the Everyperson’s Linux (or Windows or Unix or SUSE or …) toolkit is the visual query tool, an open called ExecuteQuery, and it can be found on the web at http://executequery.org/index.jsp The tool is written in Java and “is available completely free of charge and will remain so under the GNU Public License” ExecuteQuery uses JDBC as its primary interface to the database world. When running the tool on Windows either the Teradata JDBC or the Teradata ODBC works (via the Sun JDBC-ODBC bridge). The Teradata JDBC and ODBC for various platforms are located at http://www.teradata.com/. Pick a version corresponding to your Teradata version although v13 JDBC works very well with Teradata v12.
While the tool does not match functionally to SQL Assistant one-to-one, ExecuteQuery does include a few things that SQL Assistant lacks, namely ERD Generation and the ability to easily import/export XML and CSV data formats. There is a set of screen snapshots at http://executequery.org/screens.jsp that demonstrate various capabilities. It may be that some of the capabilities in the snapshots may be of limited use when using with Teradata, specifically those that graphically help you build SQL such as the CREATE TABLE. But possibly you can make use of those capabilities.
Unless you are knowledgeable about how to connect to Teradata via JDBC you may have difficulty in setting up a connection. Here are a few snapshots showing how you could set up a JDBC Teradata connection. There are 2 “panels” that must be filled in, a Drivers panel and a Connections panel. In the Drivers panel you must indicate where the tool can find the Teradata JDBC jar files and give it the ClassName as thus:
In the Connections panel you specify the Port (always 1025 for Teradata, different for other database technologies), the UID and PWD and then the JDBC URL to find the Teradata instance.
In the setup panels there is precedence on which field on which panel overrides other fields on other panels; in this case the Connections JDBC URL overrides the JDBC URL in the Drivers panel. Caution - do not give up easily when doing the JDBC setup, it is much touchier to set up than an ODBC Windows entry. The easiest way to connect to Teradata is to use the JDBC-ODBC bridge and on the Connections panel, the JDBC URL entry just specify something like jdbc:odbc:wincop1 where wincop1 is the ODBC entry name which you have previously set up. Of course when running on Everyperson’s Linux this is probably not possible, there is probably not an ODBC entry that can be used.
That is it, now you have a query tool that operates in almost any environment including Everyperson’s Linux against almost any database (those than have a JDBC or ODBC). Notice that you can have multiple connections to different databases all active at the same time. The above setup (see the left subpanel on the Connections panel) has a JDBC connection to Teradata, JDBC to SQL Server, and then ODBC connections (assuming running on Windows) to each database.
Happy querying.
Next – Java Fastload and Tpump on Everyperson’s Linux.