DBCOMMIT= Data Set Option

Causes an automatic COMMIT (a permanent writing of data to the DBMS) after a specified number of rows have been processed.

Valid in:	DATA and PROC steps
Default:	LIBNAME statement setting
Supports:	DB2 UNIX/PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Details

Example

Syntax

DBCOMMIT=n

Syntax Description

n: is an integer greater than or equal to 0.

Details

DBCOMMIT= affects update, delete, and insert processing. The number of rows processed includes rows that are not processed successfully. When DBCOMMIT=0, a commit is issued only once (after the procedure or DATA step completes).

If the DBCOMMIT= option is explicitly set, any update that has a WHERE clause fails.

SAS data sets cannot be rolled back, so for these data sources, this option has no effect. However, if explicitly set, the LIBNAME engine will still fail any update that has a WHERE clause even though the value specified on this option has no effect.

Teradata Details: The Teradata driver alias for this option is CHECKPOINT. See the Fastload capability description in the data source reference documentation for Teradata for the default behavior of this option.

Example: Specify the Number of Rows to Process

In the following example, a commit is issued after every 10 rows are processed:

data oracle.dept (dbcommit=10);
   set myoralib.staff;
run;