SQL Pass-Through Facility Specifics for Teradata

Key Information

For general information about this feature, see SQL Pass-Through Facility.

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 its 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 more 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.

As a best practice, set the SAS_NON_XVIEW_TABLES environment variable to YES when you invoke SAS. Enabling SAS_NON_XVIEW_TABLES has been shown to improve performance when loading data. Include this code in your SAS command:

-set SAS_NON_XVIEW_TABLES YES

Including Comments in SQL Code

To use comments in your SQL code, enclose them between ‘/*’ and ‘*/’ characters. SAS/ACCESS recognizes that content between these characters is a comment. SAS/ACCESS removes the comment before passing SQL statements to the database.

proc sql;
connect to teradata as tera1 (user=myuser password=mypass server=myserver
   tpt=yes fastexport=yes);
create table mytable as
select  * from connection to tera1
 (select *  from classdat /* this is a comment */
   where age > 15 );
disconnect from tera1;
quit;

Although Teradata indicates comments with ‘– –’ characters, if you use '– –' as a comment in PROC SQL, any clauses that follow '– –' are treated as part of the comment.

SQL Examples

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

proc sql;
   connect to teradata as dbcon (user=myusr1 pass=mypwd1);
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=myusr1 password=mypwd1 );
   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=myusr1 password=mypwd1 );
   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=myusr1 password=mypwd1 );
   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=myusr1 pass=mypwd1 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=myusr1 pass=mypwd1 mode=teradata);
   select * from connection to teradata (select * from
   casetest where x='case insensitivity desired');
quit;