Data Set Options for Relational Databases

DBCOMMIT= Data Set Option

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

Alias:	CHECKPOINT= [Teradata]
Default value:	the current LIBNAME setting
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	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

Syntax
Syntax Description
Details
Example
See Also

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

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

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