DBCOMMIT= Data Set Option

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

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Alias:	CHECKPOINT= [Teradata]
Default:	the current LIBNAME setting
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	BULKLOAD= LIBNAME option, BULKLOAD= data set option, DBCOMMIT= LIBNAME option, ERRLIMIT= LIBNAME option, ERRLIMIT= data set option, INSERT_SQL= LIBNAME option, INSERT_SQL= data set option, INSERTBUFF= LIBNAME option, INSERTBUFF= data set option, ML_CHECKPOINT= data set option, , Using FastLoad

Syntax

Details

Example

Syntax

DBCOMMIT=n

Syntax Description

n: specifies 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, COMMIT is issued only once—after the procedure or DATA step completes.

If you explicitly set the DBCOMMIT= option, SAS/ACCESS fails any update with a WHERE clause.

If you specify both DBCOMMIT= and ERRLIMIT= and these options collide during processing, COMMIT is issued first and ROLLBACK is issued second. Because COMMIT is issued (through the DBCOMMIT= option) before ROLLBACK (through the ERRLIMIT= option), DBCOMMIT= overrides ERRLIMIT=.

DB2 Under UNIX and PC Hosts: When BULKLOAD=YES, the default is 10000.

Teradata: For the default behavior of this option, see FastLoad description in the Teradata section. DBCOMMIT= and ERRLIMIT= are disabled for MultiLoad to prevent any conflict with ML_CHECKPOINT=.

Example: Specify the Number of Row to Process

A commit is issued after every 10 rows are processed in this example:

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