SQL Pass-Through Facility Specifics for Teradata

Key Information

For general information about this feature, see SQL Pass-Through Facility. Teradata examples are available.

Here are the SQL pass-through facility specifics for the Teradata interface.

The dbms-name is TERADATA.
The CONNECT statement is required.
The Teradata interface can connect to multiple Teradata servers and to multiple Teradata databases. However, if you use multiple simultaneous connections, you must use an alias argument to identify each connection.
The CONNECT statement database-connection-arguments are identical to the LIBNAME connection options.

The MODE= LIBNAME option is available with the CONNECT statement. By default, SAS/ACCESS opens Teradata connections in ANSI mode. In contrast, most Teradata tools, such as BTEQ, run in Teradata mode. If you specify MODE=TERADATA, pass-through connections open in Teradata mode, forcing Teradata mode rules for all SQL requests that are passed to the Teradata DBMS. For example, MODE= impacts transaction behavior and case sensitivity. See your Teradata SQL reference documentation for a complete discussion of ANSI versus Teradata mode.
By default, SAS/ACCESS opens Teradata in ANSI mode. You must therefore use one of these techniques when you write PROC SQL steps that use the SQL pass-through facility.
- Specify an explicit COMMIT statement to close a transaction. You must also specify an explicit COMMIT statement after any data definition language (DDL) statement. The examples below demonstrate these rules. For further information about ANSI mode and DDL statements, see your Teradata SQL reference documentation.
- Specify MODE=TERADATA in your CONNECT statement. When MODE=TERADATA, you do not specify explicit COMMIT statements as described above. When MODE=TERADATA, data processing is not case sensitive. This option is available when you use the LIBNAME statement and also with the SQL pass-through facility.

CAUTION:

Do not issue a Teradata DATABASE statement within the EXECUTE statement in PROC SQL. Add the SCHEMA= option to your CONNECT statement if you must change the default Teradata database.

Examples

In this example, SAS/ACCESS connects to the Teradata DBMS using the dbcon alias.

proc sql;
   connect to teradata as dbcon (user=testuser pass=testpass);
quit;

In the next example, SAS/ACCESS connects to the Teradata DBMS using the tera alias, drops and re-creates the SALARY table, inserts two rows, and disconnects from the Teradata DBMS. Note that COMMIT must follow each DDL statement. DROP TABLE and CREATE TABLE are DDL statements. The COMMIT statement that follows the INSERT statement is also required. Otherwise, Teradata rolls back the inserted rows.

proc sql;
   connect to teradata as tera ( user=testuser password=testpass );
   execute (drop table salary) by tera;
   execute (commit) by tera;
   execute (create table salary (current_salary float, name char(10)))
            by tera;
   execute (commit) by tera;
   execute (insert into salary values (35335.00, 'Dan J.')) by tera;
   execute (insert into salary values (40300.00, 'Irma L.')) by tera;
   execute (commit) by tera;
   disconnect from tera;
quit;

For this example, SAS/ACCESS connects to the Teradata DBMS using the tera alias, updates a row, and disconnects from the Teradata DBMS. The COMMIT statement causes Teradata to commit the update request. Without the COMMIT statement, Teradata rolls back the update.

 proc sql;
   connect to teradata as tera ( user=testuser password=testpass );
   execute (update salary set current_salary=45000
            where (name='Irma L.')) by tera;
   execute (commit) by tera;
   disconnect from tera;
  quit;

In this example, SAS/ACCESS uses the tera2 alias to connect to the Teradata database, selects all rows in the SALARY table, displays them using PROC SQL, and disconnects from the Teradata database. No COMMIT statement is needed in this example because the operations are reading only data. No changes are made to the database.

proc sql;
   connect to teradata as tera2 ( user=testuser password=testpass );
   select * from connection to tera2 (select * from salary);
   disconnect from tera2;
  quit;

In this next example, MODE=TERADATA is specified to avoid case-insensitive behavior. Because Teradata Mode is used, SQL COMMIT statements are not required.

/* Create & populate the table in Teradata mode (case insensitive). */
proc sql;
   connect to teradata (user=testuser pass=testpass mode=teradata);
   execute(create table casetest(x char(28)) ) by teradata;
   execute(insert into casetest values('Case Insensitivity Desired') ) 
      by teradata;
quit;
/* Query the table in Teradata mode (for case-insensitive match). */
proc sql;
   connect to teradata (user=testuser pass=testpass mode=teradata);
   select * from connection to teradata (select * from
   casetest where x='case insensitivity desired');
quit;