Using the Apache Derby ij tool with the Teradata JDBC Driver

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Oracle (Sun) JDK 6.0 and JDK 7.0 include the Apache Derby database, which is implemented in 100% Java. 

This article is not about the Apache Derby database; instead, this is about a command-line interactive SQL tool that is included with Apache Derby. 

The Apache Derby "ij" interactive SQL tool is quite useful. It is a command-line SQL tool similar to BTEQ, and it works with any JDBC Driver and any database, not just the Apache Derby database. 

The following instructions show how to use the ij tool with the Teradata JDBC Driver. 

First, you need to have a JDK installed (not just a JRE) that includes the Apache Derby jar files. The Apache Derby jar files are typically located in the db/lib directory under the main JDK install directory. (Note that some builds of JDK 6.0 have problems with installing the Apache Derby files.) 

Second, you need to have the Teradata JDBC Driver jar files terajdbc4.jar and tdgssconfig.jar available. 

On Windows

Assuming that your JDK 6.0 or 7.0 is installed in directory c:\jdk and that your Teradata JDBC Driver jar files are located in c:\terajdbc 

c:\jdk\bin\java -cp "c:/terajdbc/terajdbc4.jar;c:/terajdbc/tdgssconfig.jar;c:/jdk/db/lib/derbytools.jar" org.apache.derby.tools.ij

On UNIX or Linux

Assuming that your JDK 6.0 or 7.0 is installed in directory /usr/jdk and that your Teradata JDBC Driver jar files are located in /usr/terajdbc

/usr/jdk/bin/java -cp "/usr/terajdbc/terajdbc4.jar:/usr/terajdbc/tdgssconfig.jar:/usr/jdk/db/lib/derbytools.jar" org.apache.derby.tools.ij

After ij starts, it prints a command prompt "ij>". The interactive commands are the same regardless of which platform you are running on.

Commands in ij must be terminated with a semicolon, just like with BTEQ. You can obtain interactive help with the "help;" command.

ij version 10.2

ij> driver 'com.teradata.jdbc.TeraDriver';

ij> connect 'jdbc:teradata://mydbhost/TMODE=ANSI' user 'joe' password 'please';

ij> select current_timestamp;

Current TimeStamp(6)

--------------------------------

2008-01-16 16:03:46.4

1 row selected

ij> disconnect;

ij> exit;

1 Comment

Intellipaat provides Hadoop online Training.

Hi,

We will start a new Hadoop Developer batch from  18th May’13. Course Content Link: http://intellipaat.com/courses/big-data/#HadoopDevelopers-1

Interested candidates please drop an email for registration at sales@intellipaat.com or give us a call.

Regards,                         

Sales Intellipaat Team

Mob: 91-9019368913  

Visit us at www.intellipaat.com.