Teradata Data Source

This article includes information about how to install the Teradata JDBC driver, how to connect to a Teradata database, and a list of the Teradata data types that  supports. With the JDBC driver installed, the Teradata data source is available as an option in the connection wizard when creating an API.
lac42
This article includes information about how to install the Teradata JDBC driver, how to connect to a Teradata database, and a list of the Teradata data types that 
Layer7 Live API Creator
 supports. With the JDBC driver installed, the 
Teradata
 data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
Install the Teradata JDBC Driver
Copy the license file, the 
terajdbc4.jar
 JAR file (the JDBC driver), and the 
tdgssconfig.jar
 JAR file (the security configuration) into the directory that is based on the Java container on which you have installed 
Layer7 Live API Creator
:
The self-contained, single-user version of Live API Creator that is based on Jetty
Copy the files into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Apache Tomcat
Copy the files into the 
%{CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the files into the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into your web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation
For more information about the versions of the JDBC drivers that 
Layer7 Live API Creator
 supports, see Installation Requirements and Supported Platforms.
To prevent errors such as the following when connecting to the database, ensure that you copy the security configuration into the directory:
java.lang.NullPointerException at com.teradata.tdgss.jtdgss.TdgssConfigApi.GetMechanisms(Unknown Source) at com.teradata.tdgss.jtdgss.TdgssManager.<init>(Unknown Source) at com.teradata.tdgss.jtdgss.TdgssManager.<clinit>(Unknown Source)
For more information about the JDBC driver versions that 
Layer7 Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a Teradata Database
You can connect to your Teradata database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to a Teradata data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your Teradata database when creating your API.
Prerequisites:
 You know your JDBC connection information.
 If you are connecting to a Teradata database schema that includes views and you have installed a Teradata JDBC driver version prior to 16,
 
 
Layer7 Live API Creator
 does not detect and list those views in API Creator. To access these views, define a Free SQL resource type and invoke the view using this resource.
For more information:
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    Teradata
     as the data source.
  4. Complete the 
    Host
    Port
    , and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
    Database
     
    The name for this database. You can use double quotes for the schema name to accept the value of mixed-case (non-standard) database name.
    Example:
     
    myDatabase
    .
    Username
     
    The username that is used to connect to this database. You can use double quotes for the username to accept the value of mixed-case (non-standard) username.
You are connected to the Teradata database.
Teradata Supported Data Types
When you are setting up a connection to an external data source, 
Layer7 Live API Creator
 must map the data types in the data source to data types in 
Layer7 Live API Creator
. The following table lists the different data type mappings applicable when working with a Teradata database.
 
Layer7 Live API Creator
 supports a subset of the full range of Teradata data types.
For more information about the Teradata data types, see the Teradata documentation. 
 
Layer7 Live API Creator
 supports read and write of the following data types:
Data type
Modeled as...
Notes
Anytype
byte
Array Type
not supported
Array type 1 (A1) dimension and Array type multi-dimension (AN)
byte fixed
byte
binary varying
byte
binary large object (BLOB)
bytes
bigint
number
Integer
byteint
number
Bit
character varying
string
(VARCHAR) can have multiple charset types LATIN, GRPAHIC, KANJISSJIS, or UNICODE
character large object (CLOB)
bytes
date
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
decimal
bigdecimal
Decimal
float
real
number
number
interval
Interval Type
 
Layer7 Live API Creator
 models the following data types as Interval Type: interval year, month, day, hour, minute, and second
interval month
ObjectType
{month: 0}
interval minute
{minute: 0}
interval minute to second
{minute:0, second: 0}
interval second
{second: 0}
interval day to second
{day: 0, second: 0}
Interval day to hour
{day: 0, hour: 0}
Interval Day
{day: 0}
Interval hour to minute
{hour: 0, minute: 0}
Interval hour to second
{hour: 0, second:0}
Interval hour
{hour: 0}
interval year to month
{year: 0, month: 0}
interval year
{year: 0}
integer
number
json
string
number
number
period date
PeriodDate
Modeled as two SimpleDate objects:
{from: 'date', to: 'date'}
For example:
{ "from": "1901-01-31", "to": "9999-12-31"}
or as string:
"period('2001-01-01', '2002-01-01')"
period time
PeriodTime
Modeled as two SimpleTime objects:
{from: 'time', to: 'time'}
For example:
{from: '01:59:59', to: '24:00:00'}
or as string:
"period('01:59:59' , '24:00:00')"
period timestamp(n)
PeriodTimstamp
Modeled as two SimpleTimestamp objects - (n) is the number of fractional seconds precision:
{"from": "1970-01-01T08:00:00", "to": "1970-01-01T17:00:00"}
or as string:
"period('1970-01-01T08:00:00','1970-01-01T17:00:00')"
period timestamp w/timezone(n)
PeriodTimestampOffset
Modeled as two SimpleTimestampOffset - (n) is the number of fractional seconds precision:
{"from": "1970-01-01T08:00:00+00:50","to": "1970-01-01T17:00:00+00:50"}
or as string:
"period('1970-01-01T08:00:00+00:50' , '1970-01-01T17:00:00+00:50' )"
period time w/timezone(n)
PeriodTimeOffset
Modeled as two SimpleTimeOffset - (n) is the number of fractional seconds precision:
{"from": "08:00:00+00:50", "to": "17:00:00+00:50"}
or as string:
"period('08:00:00+00:50' , '17:00:00+00:50')"
real
real
smallint
number
time
SimpleTime
Modeled as SimpleTime 00:00:00 format.
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp with timezone
SimpleTimeOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
xml
string
Limit your Result Set for Large Teradata Tables or Views
Accessing your data using system filters (
sysfilter
) or system sorts (
sysorder
) in endpoint URL requests can be complicated, especially if you are using the period, interval, or Any data types.
If you have large Teradata tables or views, consider limiting your result set by defining a table-based resource that specifies filtering and row ordering.
For more information about how to apply filters and sorts to each level of an endpoint, see Define Table-Based Resource Types.
Teradata Data Source Limitations
The following limitations are known in Teradata data sources:
  • Filters do not support the period, interval, or Any data types.
  • Sorts do not support the interval data types.