Chapter Contents
Previous
Next
|
Chapter Contents |
Previous |
Next |
| LIBNAME Statement: Teradata Specifics |
| Valid: | Anywhere |
Syntax |
| LIBNAME libref SAS/ACCESS-engine-name <SAS/ACCESS-engine-connection-options> <SAS/ACCESS-LIBNAME-options>; |
teradata. Alternatively, you can use
sasiotra. The engine name is required. Note that SAS/ACCESS implements
engines differently in different operating environments. Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS data set options. When you specify an option in the LIBNAME statement, the option applies to all objects (Teradata tables and views) that are referenced by the libref. In contrast, a SAS/ACCESS data set option applies only to the specified data set. If you specify a like-named option in both the SAS/ACCESS engine LIBNAME statement and after a SAS data set name (referencing a DBMS table or view), SAS/ACCESS uses the value of the data set option. See Data Set Options: Teradata Specifics for more information.
| Details |
The LIBNAME statement associates a libref with the SAS/ACCESS engine for Teradata to access DBMS tables or views. You specify a particular Teradata table or view name using a two-level SAS name. An example of a two-level SAS name is MYDBLIB.EMPLOYEES_Q2. In this example, MYDBLIB is the SAS libref that points to a particular group of Teradata DBMS objects. EMPLOYEES_Q2 is the name of a specific Teradata table.
When you specify MYDBLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. Version 8 and above of the SAS System support reading, updating, creating, and dropping (deleting) Teradata tables.
To disassociate or clear a libref from the DBMS, use a LIBNAME statement,
specifying the libref (for example, MYDBLIB) and the CLEAR option as shown
in the example that follows.
libname mydblib CLEAR;
The database engine will disconnect from the database and free resources that are associated with that connection.
| SAS/ACCESS Engine Connection Options |
The following SAS/ACCESS engine connection options are required for Teradata and must be used together:
The following SAS/ACCESS engine connection options are optional for Teradata:
| Alias: USERNAME= |
| Aliases: PASS= and PW= |
The following information applies to 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 can use the TDPID= option to override the default by specifying a dbcname, eight characters or less. SAS/ACCESS adds the specified dbcname to the login string that it submits to Teradata. (Note: 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. In order 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).
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 the following dbcname entries and IP addresses:
| dbccop1 10.25.20.34 | |
| myservercop1 130.96.8.207 | |
| xyzcop1 33.44.55.66 | |
| xyzcop2 11.22.33.44 |
Example 1: TDPID= is not specified.
| In example 1, the TDPID= option is not specified, establishing a login to the Teradata server that runs at 10.25.20.34. |
Example 2: TDPID= myserver
| In example 2, you specify a login to the Teradata server that runs at 130.96.8.207. |
Example 3: TDPID=xyz
| In example 3, 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. |
The following information applies to CHANNEL-ATTACHED systems (MVS).
TDPID= specifies the subsystem name, which 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 MVS 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 MVS.
Alias: SERVER=
| SAS/ACCESS LIBNAME Options |
When you specify options in the LIBNAME statement, the option applies to all tables and views that the libref represents. If you do not specify an option, the default value is in effect and applies to the same objects.
The SAS/ACCESS interface to Teradata supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options, except for DBMAX_TEXT=. In addition to the supported options, the following LIBNAME options are used only in the interface to Teradata or have Teradata-specific aspects to them:
|
|
The SAS/ACCESS Interface to Teradata allows an entry up to 30 characters for the USERNAME and PASSWORD.
| Default: DBPROMPT=NO |
For a full description of this option, refer to the LIBNAME option, DBPROMPT=.
By default, a libref points to the database that is named the same as your user name. You can use this option to point to a different database. The DATABASE= option enables you to view or modify a different user's DBMS tables or views, assuming that you have the requisite Teradata privileges to that user's tables and views. For example, to read a different user's tables, you must have the Teradata privilege SELECT for that user's tables.
| Default: The database that is named the same as your user name. | |
| Aliases: SCHEMA= or DB= |
In the example that follows, user KAMDAR prints the EMP table which is located in the OTHERUSER database.
libname mydblib teradata user=kamdar pw=ellis schema=otheruser; proc print data=mydblib.emp; run;
Note: For more information about changing the default database, see
the DATABASE statement in your Teradata documentation. ![[cautionend]](../common/images/cautend.gif)
Use DBINDEX=YES to improve performance of specific processing. For example, a DATA step that contains the KEY= option. Or, when using a libref with PROC SQL to join a Teradata table and a SAS data set.
| Default: DBINDEX=NO |
For a more complete description of this option, refer to the LIBNAME option, DBINDEX=.
The PreFetch Arguments include:
| unique_storename | a unique name that you specify. This value names the Teradata macro that PreFetch creates to store selected SQL statements in the first run of a job. During subsequent runs of the job,SAS/ACCESS presubmits the stored SQL statements in parallel to the Teradata DBMS. |
| #sessions | controls the number of statements that PreFetch submits in parallel to Teradata. A valid value is 1 through 9. If you do not specify a #sessions value, the default is 3. |
| algorithm | specifies the algorithm that PreFetch uses to order the selected SQL statements. Currently, the only valid value is SEQUENTIAL. |
| Default: Prefetch is not enabled. |
Some SAS procedures, for example PROC TRANSREG and PROC DISCRIM, require two passes to complete analysis of the data. SPOOL=YES specifies for SAS on the first pass to spool all rows to a file. Then, on the second pass, for SAS to read the row data from the SAS spool file.
SPOOL=NO requires SAS/ACCESS to issue the identical SELECT statement to Teradata twice. As a result, Teradata must do unnecessary, extra work. Additionally, because the Teradata table can be modified between passes, SPOOL=NO can cause data integrity problems. For both reasons use SPOOL=NO with discretion.
| Default: SPOOL=YES |
For a complete description of this option, refer to the LIBNAME option, SPOOL=.
Note: This section discusses the LIBNAME
SAS/ACCESS lock
options for Teradata. When reading, do not confuse the Teradata keyword value,
ACCESS, and the SAS/ACCESS Interface
to the Teradata DBMS.
Before using the SAS/ACCESS lock options for Teradata, it is important to understand that these options modify Teradata's standard locking for row, tables, and views. Teradata usually locks at the row level; SAS/ACCESS lock options lock at the table or view level. For details about the scope of SAS/ACCESS lock options, read the next topic, Understanding the Scope of SAS/ACCESS Lock Options. For a complete description of Teradata locking, see the LOCKING statement in your Teradata SQL Reference manual.
In general, Teradata's row locks are preferable. Although SAS/ACCESS lock
options do not support row-level locking, they can be appropriate for special
situations, see Identifying Situations to Use SAS/ACCESS Lock Options.
If SAS/ACCESS lock options do not meet
your specialized needs, Teradata provides additional locking features using
views. See CREATE VIEW in your Teradata SQL Reference manual for details.
| SAS /ACCESS Lock Options | lock the ... | modifying standard Teradata locking which locks the ... |
|---|---|---|
| READ_ISOLATION_LEVEL= READ_LOCK_TYPE= READ_MODE_WAIT= |
TABLE 1 | ROW |
| UPDATE_ISOLATION_LEVEL= UPDATE_LOCK_TYPE= UPDATE_MODE_WAIT= |
TABLE 2 | ROW |
| 1 Specifying READ_LOCK_TYPE=VIEW locks
the view.
2 Specifying UPDATE_LOCK_TYPE=VIEW locks the view. |
||
Use of SAS/ACCESS lock options
can decrease overall system performance. Apply READ or WRITE locks only when you must apply special locking on Teradata tables. Further,
when using SAS/ACCESS lock options,
limit the span (see next topic) of the locks as much as possible.
LIBNAME read or update lock options affect all the tables
referenced by your libref that you open. In contrast, a like-named data set
option applies only to the table specified. Since the span of LIBNAME SAS/ACCESS
locks can be broader than you intend, limit the effect of SAS/ACCESS locks
by using only the comparable data set options whenever possible.
To use SAS/ACCESS locking options you must specify a set of three SAS/ACCESS LIBNAME Read Lock Options or SAS/ACCESS LIBNAME Update Lock Options. Which set you specify depends on the type of processing you are doing. But, if you specify an incomplete set, SAS/ACCESS returns an error message.
After you correctly specify a set of SAS/ACCESS lock options, SAS/ACCESS generates locking modifiers on your behalf to Teradata. (SAS/ACCESS lock options merely cause the LIBNAME engine to transmit a locking request to the DBMS; the Teradata DBMS performs all the data locking.)
For example, assume that you specify all three READ lock options. SAS/ACCESS generates locking modifiers that contain values for each of the READ options. Similarly, if you specify all three UPDATE lock options, SAS/ACCESS generates locking modifiers that contain values for each UPDATE option.
Note: As mentioned earlier, if you do not use SAS/ACCESS locking
options, SAS/ACCESS will not generate
locking modifiers. Without locking modifiers, Teradata's lock defaults are
in effect.
| SAS/ACCESS Read Lock Option | Values |
|---|---|
| READ_ISOLATION_LEVEL= | ACCESS|READ|WRITE |
| READ_LOCK_TYPE= | TABLE|VIEW |
| READ_MODE_WAIT= | YES|NO |
| ACCESS | obtains an ACCESS lock even when a WRITE lock is
in effect by another user. This lock can cause inconsistent or unusual results
during a read operation.
Effect: permits other users any type of lock. |
| READ | obtains a READ lock.
Effect: permits other users to READ lock but not modify the table. Typically, READ is adequate for most SAS/ACCESS processing. |
| WRITE | obtains a WRITE lock. (You cannot explicitly release
a WRITE lock. It is released when the DBMS object on which it is applied is
closed.)
Effect: Excludes other users from requesting a READ or WRITE lock on the table. For this reason, a WRITE lock is unnecessarily restrictive. |
| TABLE | locks the entire Teradata table. |
| VIEW | locks the entire Teradata view. |
If you do not use SAS/ACCESS read lock options, Teradata applies a lock either at the row or table level. Since SAS/ACCESS does not support a ROW lock, use SAS/ACCESS lock options only when you must lock the entire table or view.
| YES | specifies for Teradata to wait to acquire the lock.
If you specify READ_MODE_WAIT=YES, SAS/ACCESS waits indefinitely until it can acquire the lock. |
| NO | specifies for Teradata to fail the lock request if
the specified DBMS resource is locked.
If you specify READ_MODE_WAIT=NO, and a different user holds a restrictive lock, then the executing SAS step will fail. SAS/ACCESS continues processing the job by executing the next step. |
| SAS/ACCESS Update Lock Option | Values |
|---|---|
| UPDATE_ISOLATION_LEVEL= | ACCESS|READ|WRITE |
| UPDATE_LOCK_TYPE= | TABLE|VIEW |
| UPDATE_MODE_WAIT= | YES|NO |
| ACCESS | obtains an ACCESS lock during update preparation.
Effect: Permits other users to READ or WRITE lock the table. An ACCESS lock avoids a potential deadlock but can cause data corruption if another user is updating the same data. |
| READ | obtains a READ lock during update preparation.
Effect: Permits other users to READ lock the table. Note: If two
users specify UPDATE_ISOLATION_LEVEL=READ, and attempt to update the same
DBMS object, there is a high probability of a deadlock because the lock is
held at the table or view level. |
| WRITE | obtains a WRITE lock during update preparation.
Effect: Excludes all other users, except those who specify ACCESS locks, from reading the table. A WRITE lock eliminates a potential deadlock and ensures data integrity. Note: Important: Since WRITE locks exclude all users, except those who
specify ACCESS locks, see Limiting the Span (Effect) of SAS/ACCESS Lock Options
for how to scope your lock appropriately. |
| TABLE | locks the entire Teradata DBMS table. |
| VIEW | locks the Teradata DBMS view. |
If you do not use SAS/ACCESS update lock options, Teradata applies a lock either at the row or table level. Since SAS/ACCESS does not support a ROW level lock, use SAS/ACCESS lock options only when you must lock the entire table or view.
| YES | specifies for Teradata to wait to acquire the lock.
If you specify UPDATE_MODE_WAIT=YES, SAS/ACCESS waits indefinitely until it can acquire the lock. |
| NO | specifies for Teradata to fail the lock request if
the specified DBMS resource is locked.
If you specify UPDATE_MODE_WAIT=NO, and a different user holds a restrictive lock, then the executing SAS step will fail. SAS/ACCESS continues processing the job by executing the next step. |
This section describes situations that might require
SAS/ACCESS lock
options instead of the standard locking provided by Teradata. As mentioned
earlier, the options change the scope of the lock from row to table and thereby
affect concurrent access to DBMS objects.
When you READ lock a table, you can lock out both yourself and other users from updating or inserting into the table. Conversely, when other users update or insert into the table, they can lock you out from reading the table. In situation 1, you want to reduce the isolation level during a read operation. To do this, you specify the following read SAS/ACCESS lock options and values:
| READ_ISOLATION_LEVEL=ACCESS | |
| READ_LOCK_TYPE_TABLE | |
| READ_MODE_WAIT=YES. |
The effect of the options and settings in Situation 1
|
|
When you read or update a table, contention can occur: the DBMS is waiting for other users to release their locks on the table that you want to access. This contention suspends your SAS/ACCESS session. In situation 2, to avoid contention during a read operation, you specify the following SAS/ACCESS read lock options and values:
| READ_ISOLATION_LEVEL=READ | |
| READ_LOCK_TYPE=TABLE | |
| READ_MODE_WAIT=NO. |
The effect of the options and settings in Situation 2
|
|
/*Generates a quick survey of unusual customer purchases.*/
libname cust teradata user=kamdar password=ellis
READ_ISOLATION_LEVEL=ACCESS
READ_LOCK_TYPE=TABLE
READ_MODE_WAIT=YES;
proc print data=cust.purchases(where= (bill<2));
run;
data local;
set cust.purchases (where= (quantity>1000));
run;
In Example 1, SAS/ACCESS
|
|
/*Updates the critical Rebate row.*/
libname cust teradata user=kamdar password=ellis;
proc sql;
update cust.purchases(UPDATE_ISOLATION_LEVEL=WRITE
UPDATE_MODE_WAIT=YES
UPDATE_LOCK_TYPE=TABLE)
set rebate=10 where bill>100;
quit;
In Example 2, SAS/ACCESS
/* The SAS/ACCESS lock options prevent the session hang */
/* that occurs when I read and insert into sametable */
libname tra teradata user=kamdar password=ellis
connection=unique;
proc sql;
insert into tra.sametable
select * from tra.sametable(read_isolation_level=access
read_mode_wait=yes
read_lock_type=table);
In Example 3, SAS/ACCESS
| Example: Specifying a LIBNAME Statement to Access Teradata Data |
In this example, the libref MYDBLIB uses the SAS/ACCESS Interface to Teradata to connect to a Teradata database. The SAS/ACCESS engine connection options are USER= and PASSWORD=.
libname mydblib teradata user=kamdar password=ellis; proc print data=mydblib.employees; where dept='CSR010'; run;
|
Chapter Contents |
Previous |
Next |
 Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.