MODE= LIBNAME Option

Specifies whether the connection to Teradata uses the ANSI or Teradata mode.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	ANSI
Data source:	Teradata
See:	SQL Pass-Through Facility Specifics for Teradata

Syntax

Details

Example

Syntax

MODE=TERADATA | ANSI

Syntax Description

TERADATA: specifies that SAS/ACCESS opens Teradata connections in Teradata mode.

ANSI: specifies that SAS/ACCESS opens Teradata connections in ANSI mode.

Details

This option allows Teradata connections to open in the specified mode. Connections that open with MODE=TERADATA use Teradata mode rules for all SQL requests that are passed to the Teradata DBMS. This impacts transaction behavior and can cause case insensitivity when processing data.

During data insertion, not only is each inserted row committed implicitly, but rollback is not possible when the error limit is reached if you also specify ERRLIMIT=. Any update or delete that involves a cursor does not work.

ANSI mode is recommended for all features that SAS/ACCESS supports, and Teradata mode is recommended only for reading data from Teradata.

Example

This example does not work because it requires the use of a cursor.

libname x teradata user=prboni pw=XXXX mode=teradata;
 /* Fails with "ERROR:  Cursor processing is 
not allowed in Teradata mode." */
 proc sql;
update x.test
set i=2;
quit;

This example works because the DBIDIRECTEXEC= system option sends the delete SQL directly to the database without using a cursor.

libname B teradata user=prboni pw=XXX mode=Teradata;
options dbidirectexec;
proc sql;
delete from b.test where i=2;
quit;