ODBC AND JDBC

JDBC: Java Database Connectivity (JDBC) provides Java developers with a
standard API (application Programming Interfaces) that is used to access
databases regardless of the driver and database product. To use JDBC you'll
need at least JDK 1.1 a database and a JDBC driver. There are several
characteristics of JDBC:ODBC : ODBC is the acronym for Open DataBase
Connectivity a Microsoft Universal Data Access standard that started life as
the Windows implementation of the X/Open SQL Call Level Interface specification.

ODBC Driver Manager - an application binds to this generic library which is
responsible for loading the requested ODBC Driver.

ODBC Driver - dynamically loaded by the ODBC Driver manager for making
connection to target Database.

Difference b/w them is

1.ODBC is for Microsoft and JDBC is for java applications.

2.ODBC can't be directly used with Java because it uses a C interface.

3.ODBC makes use of pointers which have been removed totally from java.

4.ODBC mixes simple and advanced features together and has complex options for
simple queries But JDBC is designed to keep things simple while allowing
advanced capabilities when required.

5.ODBC requires manual installation of the ODBC driver manager and driver on all
client machines. JDBC drivers are written in java and JDBC code is automatically
installable secure and portable on all platforms.

6. JDBC API is a natural Java Interface and is built on ODBC. JDBC retains some
of the basic feature of ODBC


You can use Squirrel SQL client available here to connect to databases via JDBC, it works with Teradata as well.

SET VS MULTISET

Table Type Specifications of SET VS MULTISET

There are two different table type philosophies so there are two different type tables. They are SET and MULTISET. It has been said, “A man with one watch knows the time, but a man with two watches is never sure”. When Teradata was originally designed it did not allow duplicate rows in a table. If any row in the same table had the same values in every column Teradata would throw one of the rows out. They believed a second row was a mistake. Why would someone need two watches and why would someone need two rows exactly the same? This is SET theory and a SET table kicks out duplicate rows. The ANSI standard believed in a different philosophy. If two rows are entered into a table that are exact duplicates then this is acceptable. If a person wants to wear two watches then they probably have a good reason. This is a MULTISET table and duplicate rows are allowed. If you do not specify SET or MULTISET, one is used as a default. Here is the issue: the default in Teradata mode is SET and the default in ANSI mode is MULTISET.
Therefore, to eliminate confusion it is important to explicitly define which one is desired. Otherwise, you must know in which mode the CREATE TABLE will execute in so that the correct type is used for each table. The implication of using a SET or MULTISET table is discussed further.

SET and MULTISET Tables

A SET table does not allow duplicate rows so Teradata checks to ensure that no two rows in a table are exactly the same. This can be a burden. One way around the duplicate row check is to have a column in the table defined as UNIQUE. This could be a Unique Primary Index (UPI), Unique Secondary Index (USI) or even a column with a UNIQUE or PRIMARY KEY constraint. Since all must be unique, a duplicate row may never exist. Therefore, the check on either the index or constraint eliminates the need for the row to be examined for uniqueness. As a result, inserting new rows can be much faster by eliminating the duplicate row check.
However, if the table is defined with a NUPI and the table uses SET as the table type, now a duplicate row check must be performed. Since SET tables do not allow duplicate rows a check must be performed every time a NUPI DUP (duplicate of an existing row NUPI value) value is inserted or updated in the table. Do not be fooled! A duplicate row check can be a very expensive operation in terms of processing time. This is because every new row inserted must be checked to see if it is a duplicate of any existing row with the same NUPI Row Hash value. The number of checks increases exponentially as each new row is added to the table.
What is the solution? There are two: either make the table a MULTISET table (only if you want duplicate rows to be possible) or define at least one column or composite columns as UNIQUE. If neither is an option then the SET table with no unique columns will work, but inserts and updates will take more time because of the mandatory duplicate row check.
Below is an example of creating a SET table:
image\img00604.gif
Notice the UNIQUE PRIMARY INDEX on the column emp. Because this is a SET table it is much more efficient to have at least one unique key so the duplicate row check is eliminated.
The following is an example of creating the same table as before, but this time as a MULTISET table:

Notice also that the PI is now a NUPI because it does not use the word UNIQUE. This is important! As mentioned previously, if the UPI is requested, no duplicate rows can be inserted. Therefore, it acts more like a SET table. This MULTISET example allows duplicate rows. Inserts will take longer because of the mandatory duplicate row check.

Teradata Sql Assistant (TSA)


Definition:
Teradata SQL Assistant (TSA), as part of Teradata Tools and Utilities (TTU), is an ODBC-based client utility used to access and manipulate data on ODBC-compliant database servers. It has two editions:
1) Teradata SQL Assistant for Microsoft Windows
2) Teradata SQL Assistant/Web Edition

Teradata SQL Assistant is an information discovery tool designed for Windows XP and Windows 2000. Teradata SQL Assistant retrieves data from any ODBC-compliant database server. The data can then be manipulated and stored on the desktop PC.

Teradata SQL Assistant/Web Edition is a web-based query tool that allows you to compose queries, submit them to the Teradata Database, and then view the results in a web browser.

Overview:
Teradata SQL Assistant for Microsoft Windows, originally called "Queryman" (before V. 6.0)or "QueryMan" (V. 6.0 and up), is also known as "SQLA" among programmers. It supports import / export tasks, but not the serious ones. With its user-friendly GUI, TSA for Windows is generally oriented to business users and casual data consumers. (Refer to BTEQ for comparison.) TSA / Web Edition has fewer functions than TSA for Windows. The key features of each are presented below.

The key features of Teradata SQL Assistant are:
    * Send queries to any ODBC database or the same query to many different databases;
    * Export data from the database to a file on a PC;
    * Create reports from any RDBMS that provides an ODBC interface;
    * Import data from a PC file directly to the database;
    * Use an import file to create many similar reports (query results or Answersets). For example, display the DDL (SQL) that was used to create a list of tables;
    * Create a historical record of the submitted SQL with timings and status information such as success or failure;
    * Use SQL syntax examples to tailor statements;
    * Use the Database Explorer Tree to easily view database objects;
    * Use a procedure builder that provides a list of valid statements for building the logic of a stored procedure;
    * Limit data returned to prevent runaway queries.

The key features of SQL Assistant / Web Edition are:
    * Send queries to any Teradata Database;
    * View and sort the results by column and save them to a file;
    * Save queries to a file so you can run them automatically from the same file in the future;
    * Load queries from a file and make changes before submitting them to the database;
    * View historical records of the queries you have submitted with timings and status information. You can sort the history rows by column, update them with your notes, and save the records to a file;
    * View a hierarchical listing of databases with object names to reduce the time required to create SQL statements;
    * Use the browser’s built-in find features to do a string search on a query, an answer set, or any information in the historical table visible in the browser window;
    * Abort a query in progress if you change your mind about running a query or if a query is taking longer than you expected;
    * Client browser’s connection ends after 30 minutes of inactivity.