Teradata

 

---

Overview

To log in using a data source, provide the following case-sensitive parameters:

Parameter	Description
DSName	The name/location of which Teradata server ( instance ) you wish to connect with.
user	Username
password	Password
databaseName (optional)	Database name. Overrides default databasename

Within WebSphere, these settings are input into the custom properties page of a Data Source. Here are some sample screens:

1. JDBC Provider
2. Data Source
3. Data Source - Custom Properties

If you get an error similar to...

DSRA8200W: DataSource Configuration: DSRA8020E: Warning: The property 'property' does not exist on the DataSource class com.ncr.teradata.TeraConnectionPoolDataSource.

...that means one of your database properties is spelled incorrectly or does not exist in the class.

Teradata DBAs establish roles and profiles for all user id's, including a default database name for selecting objects such as tables, views, macros, etc.

Teradata has no higher definition in its schema above database, and is synonymous with "username", most of the time.

Generally, user id's have a default database setting inside the dictionary of "username". This means, that when logging onto Teradata with username, the default databasename is username.

Provided the roles and access rights are properly established for your user id and assigned role, Teradata RDBMS will either allow, or dis-allow access to the requested objects ( databasenames, views, tables, macros, etc. ).

 

Example 1:

Inside the JDBC connection string is where one could declare the default database name, in this case DBName.

For instance,

//jdbc:teradata//GatewayServerName:portnumber/DBServerName/parm1,parm2,...

Parm1 could be DATABASE=DBName

This would cause the default database "scope" to point to DBName. This means, that whenever a SQL is presented to Teradata for processing, that all requested objects ( tables views macros triggers, etc. ) are located in the current database specified. In the example above, by setting DATABASE=DBName, any SQL such as:

SELECT * FROM V01TableName;

would assume that the location in the dictionary for this object is DBName.

 

Example 2:

Another option is to fully qualify all objects in the SQL text such as:

SELECT * FROM DBName.V01TableName;

 

Development --> Test --> Production

Production databases generally run non-ANSI as the default mode. This means that each SQL presented assumes that a Begin Transaction / End Transaction is implied. In other words, reaching each semicolon amounts to a "commit".

In addition, Teradata is case insensitive for object naming ( database, table, column ) and the field content is default as case insensitive. This is different than Oracle.

Please be cautious overriding the BT/ET environment mode as well as using "multi-session" connection ( read up on multi-statement SQL ) . This could create concurrency with unknown effects on performance for your SQL and that of other SQL being processed.

Permanent and Spool Space is NOT MANAGED the same as in Oracle.

 

PooledDataSource Example

import javax.naming.*;
import java.sql.*;
import javax.sql.*;
import java.util.Hashtable;
class CreateDSExample 
{
    public static void main(String[] args) 
    {
        Hashtable env2 = new Hashtable();
        env2.put(Context.INITIAL_CONTEXT_FACTORY,
        "com.sun.jndi.fscontext.RefFSContextFactory");
        env2.put(Context.PROVIDER_URL, "file:/tmp/jdbc/ds1");
        try 
        {
            com.ncr.teradata.TeraConnectionPoolDataSource ds = new
            com.ncr.teradata.TeraConnectionPoolDataSource();
            
            //Required parameters

            //gateway server name
            ds.setServerName("ews2.ElSegundoCA.ncr.com");             

            // Port number on gateway server
            ds.setportNumber("8005");                               

            // DataBase server name
            ds.setDSName("cs4300s3");                                             
            

            // If running a type 2 driver the two lines preceding this should be
            // removed and the following two lines should be uncommented.


            // Port number on gateway server
            //ds.setportNumber("0");                                              

            // DataBase server name
            //ds.setDSName("");                                                   

            // Description
            ds.setdescription("test data source");                                
            
            //optional parameters - these setxxx methods can be omitted

            ds.setuser("dmr"); 
            ds.setpassword("mypasswd"); 
            ds.setdebugStr("ON");      
            ds.setFetchRows("10000"); 
            ds.setTransactMode("tera"); 
            ds.setCharSet("ascii"); 
            ds.setprint("2");   
            ds.setspl("3");                                                       
            ds.setDatabaseName("dmr"); 
            
            //ds.setAccountId("myAcct"); 
            Context ctx = new InitialContext(env2);
            ctx.unbind("TeraData1");
            ctx.bind("TeraData1", ds);  
        }
        catch(NamingException ne) 
        {
            System.out.println("HELP");
            System.out.println("Exp: " + ne.getExplanation());
            ne.printStackTrace();
        }
    }
}

 

---

Teradata timestamps

If you are using Hibernate with WebSphere Application Server and you get:

Exception: java.sql.SQLException: [NCR][Teradata JDBC Driver]:TeraResultSet:getTimeStamp function failed

First, turn on show sql by going to...

Console | server | Process Definition | JVM | Custom Properties

...and set...

hibernate.show_sql=true

If it shows that the Teradata date type is "Date", and your Hibernate mapping looks something like...

<key-property name="property" type="java.util.Calendar" column="column"/>

...you will get an exception when you call...

Timestamp ts = rs.getTimestamp(name);

The solution is to use net.sf.hibernate.type.CalendarDateType Here is the beginning of the class...

**
 * calendar_date: A type mapping for a Calendar 
 * object that represents a date.
 * @author Gavin King
 */
public class CalendarDateType extends MutableType {
 
 public Object get(ResultSet rs, String name) throws HibernateException, SQLException {
  
  Date date = rs.getDate(name);
  ...

To implement, change the "java.util.Calendar" type mappings in the Hibernate mapping to "calendar_date"

 

See also:

1. NCR Information Products Publishing library
2. Teradata home page
3. Teradata Driver for the JDBC Interface
4. Teradata Install Guide for Solaris
5. us.mcdevitt.TdatJdbcDriver

 

---