Data Set Options for Relational Databases

MULTISTMT= Data Set Option

Specifies whether insert statements are sent to Teradata one at a time or in a group.

Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

MULTISTMT=YES | NO

Syntax Description

YES: attempts to send as many inserts to Teradata that can fit in a 64K buffer. If multistatement inserts are not possible, processing reverts to single-row inserts.
NO: send inserts to Teradata one row at a time.

Details

To specify this option, you must first set MULTILOAD=YES.

When you request multistatement inserts, SAS first determines how many insert statements that it can send to Teradata. Several factors determine the actual number of statements that SAS can send, such as how many SQL insert statements can fit in a 64K buffer, how many data rows can fit in the 64K data buffer, and how many inserts the Teradata server chooses to accept. When you need to insert large volumes of data, you can significantly improve performance by using MULTISTMT= instead of inserting only single-row.

When you also specify the DBCOMMIT= option, SAS uses the smaller of the DBCOMMIT= value and the number of insert statements that can fit in a buffer as the number of insert statements to send together at one time.

You cannot currently use MULTISTMT= with the ERRLIMIT= option.

Examples

Here is an example of how you can send insert statements one at a time to Teradata.

libname user teradata user=zoom pw=XXXXXX server=dbc;
proc delete data=user.testdata;
run;

data user.testdata(DBTYPE=(I="INT") MULTISTMT=YES);
     do i=1 to 50;
         output;
     end;
run;

In the next example, DBCOMMIT=100, so SAS issues a commit after every 100 rows, so it sends only 100 rows at a time.

libname user teradata user=zoom pw=XXXXX server=dbc;
proc delete data=user.testdata;
run;

proc delete data=user.testdata;run;
data user.testdata(MULTISTMT=YES DBCOMMIT=100);
do i=1 to 1000;
   output;
   end;
run;

In the next example, DBCOMMIT=1000, which is much higher than in the previous example. In this example, SAS sends as many rows as it can fit in the buffer at a time (up to 1000) and issues a commit after every 1000 rows. If only 600 can fit, 600 are sent to the database, followed by the remaining 400 (the difference between 1000 and the initial 600 that were already sent), and then all rows are committed.

libname user teradata user=zoom pw=XXXXX server=dbc;
proc delete data=user.testdata;
run;

proc delete data=user.testdata;
run;
data user.testdata(MULTISTMT=YES DBCOMMIT=1000);
do i=1 to 10000;
   output;
   end;
run;

This next example sets CONNECTION=GLOBAL for all tables, creates a global temporary table, and stores the table in the current database schema.

libname user teradata user=zoom pw=XXXXX server=dbc connection=global;
proc delete data=user.temp1;
run;

proc sql;
   connect to teradata(user=zoom pw=XXXXXXX server=dbc connection=global);
   execute (CREATE GLOBAL TEMPORARY TABLE temp1 (col1 INT )
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

data work.test;
  do col1=1 to 1000;
     output;
  end;
run;

proc append data=work.test base=user.temp1(multistmt=yes);
run;