What kind of Auto Commit options exist for SAP HANA?
SAP HANA provides the following Auto Commit options:
| Auto Commit Type | Description |
| Auto Commit after each DML statement | SAP HANA Studio -> SQL Editor -> Properties -> Sessions -> Auto Commit On: COMMIT is issued after each executed SQL statement (default) HDBSQL interactive option: a a ON: COMMIT is issued after each executed SQL statement (default) HDBSQL command line option: -z -z: Manual execution of COMMIT is required |
| Auto Commit after each batch of rows during a DML statement | SQL command: ALTER TABLE ... COLUMN BATCH <size> BATCH <size>: COMMIT is issued after <size> records are processed |
| Auto Commit after each DDL statement | SQL command: "SET TRANSACTION AUTOCOMMIT DDL [ ON | OFF ] ON: COMMIT is issued after each DDL operation (default) Used e.g. by online repartitioning (SAP Note 2012533), but not officially documented |
In addition some undocumented parameters exist that can control the COMMIT frequency of DML operations in the row store by defining batch sizes:
- indexserver.ini --> [transaction] --> parallel_batch_insert_threshold
- indexserver.ini --> [row_engine] --> bulk_delete_threshold
It is not recommended to touch these parameters unless explicitly requested by SAP.