Teradata Reference

Understanding the Table Services Driver for Teradata

Data Service Connection Options for Teradata

Understanding the Table Services Driver for Teradata

The table services driver for Teradata provides Read and Update access to Teradata database tables and creates tables that can be accessed by both table services and Teradata.

The table services driver for Teradata supports most of the FedSQL functionality. The driver also enables an application to submit native Teradata SQL statements.

The table services driver for Teradata is a remote driver, which means that it connects to a server process to access data. The process might be running on the same machine as the table services, or it might be running on another machine in the network.

The table services driver for uses shared libraries that are referenced as shared objects in UNIX. You must add the location of the shared libraries to one of the system environment variables, and set any other environment variables that are required by the Teradata client libraries. The following Korn shell commands provide an example:

LD_LIBRARY_PATH=/opt/teradata/client/14.10/
   lib64:/opt/teradata/client/14.10/tbuild/lib64:/
   opt/teradata/client/14.10/tdicu/lib64:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH%:}
export COPERR=/opt/teradata/client/14.10/lib
export COPLIB=/opt/teradata/client/14.10/lib
export NLSPATH=/opt/teradata/client/14.10/tbuild/msg64/%N

Data Service Connection Options for Teradata

Connection Options

Connection options are used to establish a connection to a data source. Specify one or more connection options when defining a data service. Here is an example:

driver=sql;conopts=(driver=teradata;catalog=acat;
uid=myuid;pwd='{sas002}C5DDFFF91B5D31DFFFCE9FFF';
server=terasoar;database=model)

The following connection options are supported for a Teradata database.

Option	Description
CATALOG	`CATALOG=catalog-identifier;` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. Any identifier is valid (for example, catalog=tera). Note: You must specify a catalog.
DATABASE	`DATABASE=database-name;` Specifies the Teradata database. If you do not specify DATABASE=, you connect to the default Teradata database, which is often named the same as your user ID. If the database value that you specify contains spaces or non-alphanumeric characters, you must enclose it in quotation marks.
DRIVER	`DRIVER=TERA;` Identifies the data service to which you want to connect, which is a Teradata database. Note: You must specify the driver.
SERVER	`SERVER=server-name;` Specifies the Teradata server identifier.

Advanced Connection Options

The following advanced options are supported for Teradata database.

Option

Description

ACCOUNT

ACCOUNT=account-ID;

Specifies an optional account number that you want to charge for the Teradata session.

CLIENT_ENCODING

CLIENT_ENCODING=encoding-value

Used to specify the character set for the session. UTF8 is the default if encoding is not specified. These character sets are supported:

ASCII
EBCDIC
EBCDIC037_0E
KATAKANAEBCDIC
KANJIEUC_0U
LATIN9_0A
THAI874_4A0
LATIN1250_1A0
CYRILLIC1251_2A0
LATIN1254_7A0
HEBREW1255_5A0
ARABIC1256_6A0
LATIN1258_8A0
TCHBIG5_1R0
SCHINESE936_6R0
KANJI932_1S0
HANGUL949_7R0
TCHINESE950_8R0
LATIN1252_3A0
SCHEBCDIC935_2IJ
TCHEBCDIC937_3IB
HANGULEBCDIC933_1II
EBCDIC273_0E
EBCDIC277_0E
KANJIEBCDIC5035_0I
KANJIEBCDIC5026_0I
UTF8
UTF16

CT_PRESERVE

CT_PRESERVE
                                    = STRICT | SAFE | FORCE | FORCE_COL_SIZE

Enables users to control how data types are mapped. Note that data type mapping is disabled when CT_PRESERVE is set to STRICT. If the requested type does not exist on the target database, an error is returned. Here are the options:

STRICT The requested type must exist in the target database. No type promotion occurs. If the type does not exist, an error is returned.
SAFE Target data types are upscaled only if they do not result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
FORCE This is the default for all drivers. The best corresponding target data type is chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure that all characters can be stored in the new encoding.
FORCE_COL_SIZE This option is the same as FORCE, except that the column size for the new encoding is the same as the original encoding. This option can be used to avoid column size creep. However, the resulting column might be too large or too small for the target data.

DEFAULT_ATTR

DEFAULT_ATTR=(attr=value;...)

Used to specify connection handle or statement handle attributes supported for initial connect-time configuration, where attr=value corresponds to any of the following options:

CURSORS=n- Connection handle option. This option controls the driver’s use of client-side, result set cursors. The possible values are 0, 1, or 2.

0	Causes the driver to use client-side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.
1	Causes the driver to always use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is not used.
2	(Default) Causes the driver to never use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is used if available. Otherwise, the cursor is forward-only.

Example: DEFAULT_ATTR=(CURSORS=2)

USE_EVP=n - Statement handle option. This option optimizes the driver for large result sets. The possible values are 0 (OFF) or 1 (ON), which is the default. Example: DEFAULT_ATTR=(USE_EVP=0)
XCODE_WARN=n - Statement handle option. Used to warn about possible character transcoding errors that occur during row input or output operations. Possible values are 0 (returns an error), 1 (returns a warning), or 2 (ignore transaction errors). 0 is the default. Example: DEFAULT_ATTR=(XCODE_WARN=1)

DRIVER_TRACE

DRIVER_TRACE='API
                                    | SQL | ALL';

Requests tracing information, which logs transaction records to an external file that can be used for debugging purposes. The driver writes a record of each command that is sent to the trace log based on the specified tracing level, which determines the type of tracing information. Here are the tracing levels:

ALL Activates all trace levels.
API Specifies that API method calls be sent to the trace log. This option is most useful if you are having a problem and need to send a trace log to SAS Technical Support for troubleshooting.
DRIVER Specifies that driver-specific information be sent to the trace log.
SQL Specifies that SQL statements that are sent to the database management system (DBMS) be sent to the trace log. Tracing information is DBMS specific, but most table services drivers log SQL statements such as SELECT and COMMIT.

Default: Tracing is not activated.

Note: If you activate tracing, you must also specify the location of the trace log with DRIVER_TRACEFILE=. Note that DRIVER_TRACEFILE= is resolved against the TRACEFILEPATH set in ALTER SERVER. TRACEFILEPATH is relative to the server's content root location.

(Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

Interaction: You can specify one trace level, or you can concatenate more than one by including the | (OR) symbol. For example, driver_trace='api|sql' generates tracing information for API calls and SQL statements.

DRIVER_TRACEFILE

DRIVER_TRACEFILE=‘filename';

Used to specify the name of the text file for the trace log. Include the filename and extension in single or double quotation marks (for example, driver_tracefile='\mytrace.log').

Default: The default TRACEFILE location applies to a relative filename, and it is placed relative to TRACEFILEPATH.

Requirement: DRIVER_TRACEFILE is required when activating tracing using DRIVER_TRACE.

Interaction: (Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

DRIVER_TRACEOPTIONS

DRIVER_TRACEOPTIONS=APPEND
                                    | THREADSTAMP | TIMESTAMP;

Specifies options in order to control formatting and other properties for the trace log:

APPEND Adds trace information to the end of an existing trace log. The contents of the file are not overwritten.
THREADSTAMP Prepends each line of the trace log with a thread identification.
TIMESTAMP Prepends each line of the trace log with a time stamp.

Default: The trace log is overwritten with no thread identification or time stamp.

PASSWORD

PASSWORD=password;

Specifies a Teradata password. The password must match your USER= value. The alias is PWD=.

Note: You must specify the PASSWORD= option.

ROLE

ROLE=security-role;

Specifies a security role for the session.

USER

USER=user-id;

Specifies a Teradata user ID. If the ID contains blanks or national characters, enclose it in quotation marks. The alias is UID=.

Note: You must specify the USER= option.