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) |
|---|---|
| Category: | Data Set Control |
| Alias: | CHECKPOINT= [Teradata] |
| Default: | LIBNAME option value |
| Interaction: | This option is not honored when the DBIDIRECTEXEC system option is enabled. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | BULKLOAD= LIBNAME option, BULKLOAD= data set option, CONNECTION= LIBNAME 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, TPT_CHECKPOINT_DATA= data set option, Using FastLoad |
Table of Contents
specifies an integer greater than or equal to 0.
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 specify 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: This option generates row-based checkpoints for TPT FastLoad when FASTLOAD=YES and for TPT Multi-Statement when MULTISTMT=YES. DBCOMMIT= is disabled for TPT MultiLoad to prevent any conflict with ML_CHECKPOINT=. For TPT MultiLoad, use the TPT_CHECKPOINT_DATA= data set option.
Vertica: For updates or deletions, any value for DBCOMMIT= is reset to 0, because SAS/ACCESS can commit changes only once at the end of an action. Also, for updates or deletions, if a value is not already specified for the CONNECTION= LIBNAME option, then CONNECTION= is set to UNIQUE.
A commit is issued after every 10 rows are processed in this example:
data oracle.dept(dbcommit=10);
set myoralib.staff;
run;