Transactions

How are transactions handled within Warehouse functions?

Ordinarily Warehouse by default treats every record read and processed within the outermost READ loop as a single transaction.

However, when the operation is located within a function Warehouse will not issue an implicit commit. The Warehouse script must be coded to issue an explicit commit if this is desired.

What is COMMITRATE?

COMMITRATE determines the frequency with which Warehouse commits transactions. COMMITRATE is a property that is global to an executing script and an option that can be altered by means of the SET command.

The default COMMITRATE is 1, meaning that Warehouse will physically commit every logical transaction, where a logical transaction is defined as all activity bracketed within the outermost READ loop.

If the COMMITRATE has been set to 0, Warehouse will never issue a commit and it is the script’s responsibility to issue a commit. And if the COMMITRATE is set to a value n greater than 1, then Warehouse will physically commit after every n logical transactions.

A transaction commit prompts the underlying database transaction manager to issue a waited physical log write to disk to guarantee durability. A larger commit rate is more performant since there will be fewer waits for log writes. On the other hand, bigger is not always better: the larger the COMMITRATE value, the longer shared system resources may be pinned (memory) or locked (rows), thus preventing other work from proceeding in parallel.

What COMMITRATE is appropriate when using a Message File?

If you are reading from a Message File, the COMMITRATE should be left to default to 1.

The Message File type has limited transactional capabilities compared to a modern RDBMS. A COMMITRATE of 1 against the target database will ensure that source and target are kept in synch in the event of a restart and recovery operation.

What happens to my open transaction when I ESCAPE out of a script?

If you ESCAPE out of a script, an open transaction will be rolled back. In order to commit the work, you will need to issue an explicit COMMIT before invoking the ESCAPE. The COMMIT can be issued in the RECOVER block, if needed.