The LIBNAME Statement for Relational Databases

MODE= LIBNAME Option

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

Default value:	ANSI
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Teradata

Syntax
Syntax Description
Details
Examples
See Also

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 opening of Teradata connections in the specified mode. Connections that are opened 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, while Teradata mode is recommended only for reading data from Teradata.

Examples

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 next 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;