Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

Auto Commit Options Exist for SAP HANA

|| 0

Auto Commit Options Exist for SAP HANA
Stechies

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)
Off: Manual execution of COMMIT is required

HDBSQL interactive option: a

a ON: COMMIT is issued after each executed SQL statement (default)
a OFF: Manual execution of COMMIT is required

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>
SQL command: IMPORT .. 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)
OFF: Manual execution of COMMIT is required

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.


Advertisement
Advertisement
Advertisement