DBCOMMIT= LIBNAME Option

Causes an automatic COMMIT (permanently writing data to the DBMS) after processing a specified number of rows.

Valid in:	SAS/ACCESS LIBNAME statement
Alias:	CHECKPOINT= [Teradata]
Default:	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)
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	BULKLOAD= data set option, DBCOMMIT= data set option, ERRLIMIT= data set option, Maximizing Teradata Load Performance , ML_CHECKPOINT= data set option, Using FastLoad

Syntax

Details

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. Usually, when you set DBCOMMIT=0, COMMIT is issued only once: after a procedure or DATA step completes. However, the commit is performed after each statement when you use the SQL procedure.

If you explicitly set this 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=.

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.