Causes an automatic COMMIT (permanently writing data to the DBMS) after processing a specified number of rows.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Aliases: | DBINITCMD=, INITCMD= [Oracle] |
| 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 deletions) | |
| none [Snowflake] | |
| Restriction: | When you specify a value for DBCOMMIT=, SAS/ACCESS fails for any update that uses a WHERE clause. |
| Interactions: | This option is not honored when the DBIDIRECTEXEC system option is enabled. |
| When both options are specified, DBCOMMIT= overrides ERRLIMIT=. See Details. | |
| 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= data set option, CONNECTION= LIBNAME option, DBCOMMIT= data set option, ERRLIMIT= data set option, Maximizing Teradata Load Performance, ML_CHECKPOINT= data set option, Using FastLoad |
Table of Contents
specifies the number of rows that are processed. This value must be an integer greater than or equal to 0.
DBCOMMIT= affects update, delete, and insert processing. The number of rows that are processed includes rows that are not processed successfully. Usually, when you specify 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.
DB2 under UNIX and PC Hosts: When BULKLOAD=YES, the default is 10000.
Teradata: This option causes TPT FastLoad and TPT Multi-Statement to generate row-based checkpoints. DBCOMMIT= and ERRLIMIT= are disabled for TPT MultiLoad to prevent any conflict with the ML_CHECKPOINT= data set option. For more information, see Using the TPT API.
Vertica: For updates or deletions, any value for DBCOMMIT= is reset to 0, because SAS/ACCESS can commit changes only 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.