LIBNAME Statement Specifics for Teradata

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to Teradata supports and includes examples. For a complete description of this feature, see LIBNAME statement.

Here is the LIBNAME statement syntax for accessing Teradata.

LIBNAME libref teradata <connection-options> <LIBNAME-options>;

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

teradata

specifies the SAS/ACCESS engine name for the Teradata interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. Here are the connection options for the Teradata interface.

USER=<'>Teradata-user-name<'> | <">ldapid@LDAP<"> | <">ldapid@LDAPrealm-name<">

specifies a required connection option that specifies a Teradata user name. If the name contains blanks or national characters, enclose it in quotation marks. For LDAP authentication with either a NULL or single realm, append only the @LDAP token to the Teradata user name. In this case, no realm name is needed. If you append a realm name, the LDAP authentication server ignores it and authentication proceeds. However, if multiple realms exist, you must append the realm name to the @LDAP token. In this case, an LDAP server must already be configured to accept authentication requests from the Teradata server.

PASSWORD=<'>Teradata-password<'>

specifies a required connection option that specifies a Teradata password. The password that you specify must be correct for your USER= value. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you do not want to enter your Teradata password in clear text on this statement, see PROC PWENCODE in the Base SAS Procedures Guide for a method for encoding it. For LDAP authentication, you use this password option to specify the authentication string or password. If the authentication string or password includes an embedded @ symbol, a backslash (\) is required and it must precede the @ symbol. See Teradata LIBNAME Statement Examples.

ACCOUNT=<'>account_ID<'>

is an optional connection option that specifies the account number that you want to charge for the Teradata session.

TDPID=<'>dbcname<'>

Alias: SERVER=

specifies a required connection option if you run more than one Teradata server. TDPID= operates differently for network-attached and channel-attached systems, as described below.

For NETWORK-ATTACHED systems (PC and UNIX), dbcname specifies an entry in your (client) HOSTS file that provides an IP address for a database server connection.

By default, SAS/ACCESS connects to the Teradata server that corresponds to the dbccop1 entry in your HOSTS file. When you run only one Teradata server and your HOSTS file defines the dbccop1 entry correctly, you do not need to specify TDPID=.

However, if you run more than one Teradata server, you must use the TDPID= option to specifying a dbcname of eight characters or less. SAS/ACCESS adds the specified dbcname to the login string that it submits to Teradata. (Teradata documentation refers to this name as the tdpid component of the login string.)

After SAS/ACCESS submits a dbcname to Teradata, Teradata searches your HOSTS file for all entries that begin with the same dbcname. For Teradata to recognize the HOSTS file entry, the dbcname suffix must be COPx (x is a number). If there is only one entry that matches the dbcname, x must be 1. If there are multiple entries for the dbcname, x must begin with 1 and increment sequentially for each related entry. (See the example HOSTS file entries below.)

When there are multiple, matching entries for a dbcname in your HOSTS file, Teradata does simple load balancing by selecting one of the Teradata servers specified for login. Teradata distributes your queries across these servers so that it can return your results as fast as possible.

The TDPID= examples below assume that your HOSTS file contains these dbcname entries and IP addresses.
- Example 1: The TDPID= option is not specified, establishing a login to the Teradata server that runs at 10.25.20.34.
  dbccop1 10.25.20.34
- Example 2: Using TDPID= myserver or SERVER=myserver, you specify a login to the Teradata server that runs at 130.96.8.207.
  myservercop1 130.96.8.207
- Example 3: Using TDPID=xyz or SERVER=xyz, you specify a login to a Teradata server that runs at 11.22.33.44 or to a Teradata server that runs at 33.44.55.66.
  xyzcop1 33.44.55.66
  or
  xyzcop2 11.22.33.44
For CHANNEL-ATTACHED systems (z/OS), TDPID= specifies the subsystem name. This name must be TDPx, where x can be 0–9, A–Z (not case sensitive), or $, # or @. If there is only one Teradata server, and your z/OS system administrator has set up the HSISPB and HSHSPB modules, you do not need to specify TDPID=. For further information, see your Teradata TDPID documentation for z/OS.

DATABASE=<'>database-name<'>

Alias: DB=

specifies an optional connection option that specifies the name of the Teradata database that you want to access. This option enables you to view or modify a different user's Teradata DBMS tables or views, if you have the required privileges. (For example, to read another user's tables, you must have the Teradata privilege SELECT for that user's tables.) If you do not specify DATABASE=, the libref points to your default Teradata database, which is often named the same as your user name. If the database value that you specify contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

SCHEMA=<'>database-name<'>

specifies an optional connection option that specifies the database name to use to qualify any database objects that the LIBNAME can reference.

LIBNAME-options

define how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Teradata, with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for Teradata

Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
BULKLOAD=	NO
CAST=	none
CAST_OVERHEAD_MAXPERCENT=	20%
CONNECTION=	SHAREDREAD for channel-attached systems (z/OS); UNIQUE for network attached systems (UNIX and PC platforms)
CONNECTION_GROUP=	none
DATABASE= (see SCHEMA=)	none
DBCOMMIT=	1000 when inserting rows; 0 when updating rows
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBMSTEMP=	NO
DBPROMPT=	NO
DBSASLABEL=	COMPAT
DBSLICEPARM=	THREADED_APPS,2
DEFER=	NO
DIRECT_EXE=
DIRECT_SQL=	YES
ERRLIMIT=	1 million
FASTEXPORT=	NO
LOGDB=	Default Teradata database for the libref
MODE=	ANSI
MULTISTMT=	NO
MULTI_DATASRC_OPT=	IN_CLAUSE
PREFETCH=	not enabled
PRESERVE_COL_NAMES=	YES
PRESERVE_TAB_NAMES=	YES
QUERY_BAND=	none
READ_ISOLATION_LEVEL=	see Locking in the Teradata Interface
READ_LOCK_TYPE=	none
READ_MODE_WAIT=	none
REREAD_EXPOSURE=	NO
SCHEMA=	your default Teradata database
SESSIONS=	none
SLEEP=	6
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	DBMS
TEMPORAL_QUALIFIER=	CURRENT VALIDTIME for valid-time column; CURRENT TRANSACTIONTIME for transaction-time column
TENACITY=	0 for FastLoad; 4 for FASTEXPORT= and MultiLoad
TPT=	YES
TR_ENABLE_INTERRUPT=	NO
UPDATE_ISOLATION_LEVEL=	see Locking in the Teradata Interface
UPDATE_LOCK_TYPE= LIBNAME Option	none
UPDATE_MODE_WAIT=	none
UTILCONN_TRANSIENT=	NO

Teradata LIBNAME Statement Examples

These examples show how to make the proper connection by using the USER= and PASSWORD= connection options. Teradata requires these options, and you must use them together.

This example shows how to connect to a single or NULL realm.

libname x teradata user=”johndoe@LDAP” password=”johndoeworld”;

Here is an example of how to make the connection to a specific realm where multiple realms are configured.

libname x teradata user=”johndoe@LDAPjsrealm” password=”johndoeworld”;

Here is an example of a configuration with a single or NULL realm that contains a password with an embedded @ symbol. The password must contain a required backslash (\), which must precede the embedded @ symbol.

libname x teradata user="johndoe@LDAP" password="johndoe\@world"