About Teradata
SAS/ACCESS Interface to TeradataFor general information about this feature, seeOverview of the SQL Pass-Through Facility. Teradata examples areavailable.Here are the SQL pass-through facility specifics forthe Teradata interface.The dbms-name is TERADATA.TheCONNECT statement is required.The Teradata interface can connect to multipleTeradata servers and to multiple Teradata databases. However, if you use multiplesimultaneous connections, you must use an aliasargument to identify each connection.The CONNECT statement database-connection-arguments are identical to the LIBNAMEconnection options.The MODE=LIBNAME option is available with the CONNECT statement. By default, SAS/ACCESS opensTeradata connections in ANSI mode. In contrast, most Teradata tools, suchas BTEQ, run in Teradata mode. If you specify MODE=TERADATA, Pass-Throughconnections open in Teradata mode, forcing Teradata mode rules for all SQLrequests that are passed to the Teradata DBMS. For example, MODE= impactstransaction behavior and case sensitivity. See your Teradata SQL referencedocumentation for a complete discussion of ANSI versus Teradata mode.By default, SAS/ACCESS opensTeradata in ANSI mode. You must therefore use one of these techniques whenyou write PROC SQL steps that use the SQL pass-through facility.Specify an explicit COMMIT statement to closea transaction. You must also specify an explicit COMMIT statement after anyData Definition Language (DDL) statement. The examples below demonstrate theserules. For further information about ANSI mode and DDL statements, see yourTeradata SQL reference documentation.Specify MODE=TERADATA in your CONNECT statement.When MODE=TERADATA, you do not specify explicit COMMIT statements as describedabove. When MODE=TERADATA, data processing is not case sensitive. This optionis available when you use the LIBNAME statement and also with the SQL pass-throughfacility.CAUTION:Donot issue a Teradata DATABASE statement within the EXECUTE statement in PROCSQL. Add the SCHEMA= option to your CONNECT statement if you must change thedefault Teradata database. In this example, SAS/ACCESS connectsto 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 connectsto the Teradata DBMS using
Key features of the Teradata that are enabled through these drivers include:
- Teradata Volatile Table with Practical Examples - Teradata Point
- Teradata SQL Assistant 13 with Teradata .Net provider
- Download .NET Data Provider for Teradata by Teradata Corporation
- Download Teradata SQL Assistant Java Edition by Teradata
- - SAS/ACCESS Interface to Teradata support for Teradata Tools
- - How To List Teradata Client Packages [Teradata Tools and
- Teradata Utilities : FASTLOAD – Part 1 – TeraData Tech
The tera alias, drops and then recreates the SALARY table, inserts tworows, and then disconnects from the Teradata DBMS. Notice that COMMIT mustfollow 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 connectsto the Teradata DBMS using the tera alias, updates a row, and then disconnects from the Teradata DBMS.The COMMIT statement causes Teradata to commit the update request. Withoutthe 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 usesthe tera2 alias to connectto the Teradata database, selects all rows in the SALARY table, displays themusing PROC SQL, and disconnects from the Teradata database. No COMMIT statementis needed in this example because the operations are only reading data. Nochanges 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=TERADATAis specified to avoid case-insensitive behavior. Because Teradata Mode isused, 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')
