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.
lac40
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 
CA Live API Creator
 supports.
In this article:
Install the Teradata JDBC Driver
After you have installed the JDBC driver, the Teradata data source is available as an option in the connection wizard when creating an API.
If you have installed
CA Live API Creator
 on Tomcat, copy the license file, the
terajdbc4.jar
JAR file (the JDBC driver), and the
tdgssconfig.jar
JAR file (the security configuration) into the
%{CATALINA_HOME}/lib
 directory.
If you install only the JDBC driver without the security configuration, you might get an error such as the following when attempting to connect to the database:
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)
If you have installed the self-contained, single-user version of 
CA Live API Creator
 based on Jetty, copy the license file, the 
terajdbc4.jar
 JAR file (the JDBC driver), and the 
tdgssconfig.jar
 JAR file (the security configuration) into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
If you are connecting to a Teradata database schema that includes views using a Teradata JDBC driver version prior to 16,
CA 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:
For more information about the JDBC driver versions that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a Teradata Database
Connect to your Teradata database by creating your API.
For more information about how to create an API, see Create your API.
Prerequisites:
 You know your JDBC connection information.
  1. In the connection wizard, select 
    Teradata
    as the target database.
  2. 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 database.
Teradata Supported Data Types
When you are setting up a connection to an external data source,
CA Live API Creator
 must map the data types in the data source to data types in
CA Live API Creator
. The following table lists the different data type mappings applicable when working with a Teradata database.
CA 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.
CA 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
bytes
Also known as BLOB
bigint
number
Integer
byteint
number
Bit
character varying
string
(VARCHAR) can have multiple charset types LATIN, GRPAHIC, KANJISSJIS, or UNICODE
character large object
bytes
Also known as CLOB
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
Various types exist that are modeled as Interval Type (interval year, month, day, hour, minute, 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.