Rollback All Transactions

Discards every open database transaction the job started, so none of the statements run inside them reach the database. It is the undo half of the transactional SQL deployment: the SQL op opens a transaction per database connection, and either this op throws the work away or its counterpart Commit All Transactions makes it permanent.

It has nothing to do with Clarive's own rollback pass. Nothing calls it for you, and putting it in a rule does not make a job rollbackable. You place it, on the failure path, and it acts on the database only.

Configuration

The op has no configuration form. Its Config tab holds only the Name you give the node in the rule tree, and there is nothing else to fill in.

The Options tab still applies, and two of its settings decide when this op fires. Run Rollback unticked keeps it out of the rollback pass, Run Forward unticked keeps it out of the forward run. Both are ticked by default, so on a job that rolls back, the op runs a second time and finds an empty list, for the reason in the next section.

Which transactions it acts on

Only the ones the job opened through Deploy SQL from Nature Items with its Transactional checkbox ticked. One transaction is opened per database connection listed in that op's Database field.

Each transaction gets a number, and the number is what the log line names. The counter behind it advances for every connection the SQL op touches, ticked or not, and it carries on across ops and across steps, so the numbers you see can start high and can have gaps in them.

SQL run without Transactional is not tracked and cannot be undone here. Neither can anything a script or a remote command did to a database.

It only works inside one step

This is the constraint that catches everybody. The list of open transactions lives for the current job step and no longer. When the step ends, the list is discarded along with the live database handles, and a copy of this op in a later step finds an empty list and does nothing.

The same applies to Clarive's rollback pass. The step's stash is written out the moment the forward run ends, and writing it out is what drops the transaction list, so the rollback pass starts with an empty one. Placing this op on the rollback branch of a rule does not undo SQL from the forward run. Those transactions are already committed, or abandoned by the database when the connection closed.

What works is placing the op in the same step as the SQL, on a path that is reached when the SQL fails. Since a failing SQL op stops the rule by default, that path has to be built: TRY statement around the SQL ops with CATCH statement holding this one, or an error trap on the SQL op itself.

What it does and what it says

Each open transaction is rolled back in turn, and each one writes a warning line to the job log naming its number. After the last one the list is cleared, so a second copy of the op in the same step has nothing left to do.

With no open transactions the op writes nothing at all. No line, no warning, no error. A rule where the transactional checkbox was never ticked looks exactly like a rule where the rollback worked. Check for the warning lines when you need to know it fired.

A database that refuses the rollback stops the job. Nothing catches that error, so one bad connection means the transactions after it in the list are never reached and stay open until the connection closes. The message that reaches the log is whatever the database driver said, with no mention of transactions, which is unlike Commit All Transactions, where a failure is labelled as a commit failure and the transactions that did commit are listed first.

The op hands back no value. A Return Key set on it puts an empty value in the stash.

Combining with other ops

Deploy SQL from Nature Items is the op that opens the transactions, and Commit All Transactions is the one that closes them the other way. All three must sit in the same step.

TRY statement and CATCH statement are how you reach this op after a failure. FAIL inside the same catch block ends the job afterwards, once the database is clean.

The SQL op runs whatever file list a nature block has put in scope around it, and falls back to every item the job loaded when there is no such block. Load Nature Items is what makes those blocks find anything, so it belongs earlier in the rule.

To branch on whether the current run is a rollback pass rather than a failure inside this run, use IF ROLLBACK. It answers a different question from the one this op solves.

Example

Wrap the database work so a failure undoes everything before the job stops.

TRY statement
  Deploy SQL from Nature Items
    Database:      ${db_conn}
    Transactional: ticked
    Error Mode:    fail
  Commit All Transactions

CATCH statement
  Rollback All Transactions
  FAIL
    Message: SQL deployment failed, all transactions were rolled back