Specifies whether to activate the TPT API Stream operator.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Default: | NO |
| Restriction: | You cannot currently use MULTISTMT= with the ERRLIMIT= option. |
| Data source: | Teradata |
| See: | DBCOMMIT= LIBNAME option, DBCOMMIT= data set option, ERRLIMIT= LIBNAME option, ERRLIMIT= data set option, MULTILOAD= data set option, MULTISTMT= LIBNAME optionQUERY_BAND= data set option, TPT_APPL_PHASE= data set option, TPT_BUFFER_SIZE= data set option, TPT_CHECKPOINT_DATA= data set option, TPT_DATA_ENCRYPTION= data set option, TPT_ERROR_TABLE_1= data set option, TPT_ERROR_TABLE_2= data set option, TPT_LOG_TABLE= data set option, TPT_MAX_SESSIONS= LIBNAME option, TPT_MAX_SESSIONS= data set option, TPT_MIN_SESSIONS= data set option, TPT_PACK= data set option, TPT_PACKMAXIMUM= data set option, TPT_RESTART= data set option, TPT_TRACE_LEVEL= data set option, TPT_TRACE_LEVEL_INF= data set option, TPT_TRACE_OUTPUT= data set option, TPT_UNICODE_PASSTHRU= LIBNAME option |
Table of Contents
use TPT Stream processing and use the maximum buffer size available for your TTU client version and database version.
send inserts to Teradata one row at a time.
When you request multi-statement inserts, SAS first determines how many insert statements it can send to Teradata. Several factors determine the actual number of statements that SAS can send. These factors include these values:
When you need to insert large volumes of data, you can significantly improve performance by using MULTISTMT= instead of inserting only one row at a time.
When you also specify DBCOMMIT=, SAS determines the number of insert statements to send together. It uses the smaller of the DBCOMMIT= value and the number of insert statements that can fit in a buffer.
The temporary processing tables that are accessed by the TPT Multistmt utility are target-table_ET and target-table_RS. To specify a different table name, use the TPT_ERROR_TABLE_1= and TPT_CHECKPOINT_DATA= data set options, respectively.
This example shows how to send insert statements one at a time to Teradata.
libname user teradata user=myusr1 pw=XXXXXX server=dbc;
proc datasets library=user;
delete testdata;run;
data user.testdata(DBTYPE=(I="INT") MULTISTMT=NO);
do i=1 to 50;
output;
end;
run;In this example, DBCOMMIT=100. Therefore, SAS issues a commit after every 100 rows, sending only 100 rows at a time.
libname user teradata user=myusr1 pw=XXXXX server=dbc;
proc datasets library=user;
delete testdata;run;
data user.testdata(MULTISTMT=YES DBCOMMIT=100);
do i=1 to 1000;
output;
end;
run;In this example, DBCOMMIT=1000, which is much higher than in the previous example. SAS sends as many rows as it can fit in the buffer at one time (up to 1000), and it issues a commit after every 1000 rows. If only 600 can fit, 600 are sent to the database. It is followed by the remaining 400. SAS then commits all rows.
libname user teradata user=myusr1 pw=XXXXX server=dbc;
proc datasets library=user;
delete testdata;run;
data user.testdata(MULTISTMT=YES DBCOMMIT=1000);
do i=1 to 10000;
output;
end;
run;This example specifies CONNECTION=GLOBAL for all tables, creates a global temporary processing table, and stores the table in the current database schema.
libname user teradata user=myusr1 pw=XXXXX server=dbc connection=global;
proc datasets library=user;
delete temp1;run;
proc sql;
connect to teradata(user=myusr1 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;