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 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

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 still fails 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: Specifying 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;