The LIBNAME Statement for Relational Databases

DBCOMMIT= LIBNAME Option

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

Default value:	1000 when a table is created and rows are inserted in a single step (DATA STEP); 0 when rows are inserted, updated, or deleted from an existing table (PROC APPEND or PROC SQL inserts, updates, or deletes)
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
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 that are processed includes rows that are not processed successfully. If you set 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.

Note: 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=. [cautionend]

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

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