IBM Integracion Bus (IIB) COMMIT, ROLLBACK and UNCOORDINATED tests

There aren't many references to the COMMIT, ROLLBACK and UNCOORDINATED keywords in the IBM Integration Bus (IIB; prevously known as WebSphere Message Broker) 9 documentation.

The main reference I have found is in the "The transactional model" section of the official documentation; in the current document version, in the "Database auxiliary transactions" sub-section, it states: "Use the ESQL COMMIT and ROLLBACK statements to commit and roll back auxiliary database transactions. Obtain operations outside the main transaction by specifying the UNCOORDINATED keyword on the individual database statements (for example, the INSERT and UPDATE statements)"; in the "Queue auxiliary transactions"  sub-section, it notes: "The COMMIT and ROLLBACK statements therefore operate only on databases.".

Although the keywords are listed in that sections, I haven't found more specific references (including syntax trees); doing trial and error tests, it can be seen that otherwise as indicated in the documentation, the  UNCOORDINATED keyword can't be specified on the individual database statements:

In the "Dynamic Database Name and Multiple Database Support" IBM presentation (for WebSphere Message Broker v6), the following information can be found: "Any uncommitted database operations that have not been committed by nodes with their transaction property set to COMMIT are automatically committed by the input node if the flow ends normally, and rolled back if there is an unhandled exception".

In an (IBM internal?) presentation (source 1, source 2) syntax trees and usage examples can be found; however, for example, it includes examples of UNCOORDINATED usage that you really can't use in the tool (see imagen above).

According to all this, to my understanding, it can be said you can use the COMMIT and ROLLBACK keywords in some way, to control UNCOORINDATED database transactions (you can mark a transaction as UNCOORDINATED in some way).

After doing several tests, I found the following:

• The COMMIT and ROLLBACK keywords can be used alone or o accompanied by a specific database  reference (for example, just "COMMIT;" or "COMMIT Database.DSN1;").
• The UNCOORDINATED keyword can't be used anywhere.
• In a Compute node accessing databases, the negotations with the database referred in the "Data Source" node property make part of the flow's global (coordinated) transactionality; this transactionality is affected by alone COMMIT and ROLLBACK sentences.
• You can reference the database referred in the "Data Source" node property in two ways (implicitly or explicitly); the transactionality behaviour for this Database is always the same, no matter which form you use:
• Implicitly: for example "UPDATE Database.TableX..." or "COMMIT;".
• Explicitly: for example "UPDATE Database.DSN1.TableX..". or "COMMIT Database.DSN1;", where DSN1 is the value of the "Data Source" property.
• In a Compute node accessing databases, the negotations with databases other than the referred in the "Data Source" node property (for example INSERT INTO Database.DSNX...) behave as "auxiliary database transactions"; they are affected only by COMMIT or ROLLBACK statements accompanied by the specific database  reference (for example "COMMIT Database.DSNX;").

Test scenary:

Node MQ Input is transactional; it's queue is persistent (with a backout value greater than 0 and a backout queue).

The Compute node contains the following:

The first two sentence groups (X) make reference to the same DSN defined in the node's "Data Source" property (the first group does it implicitly and the second group does it explicitly).

The value of the "Transaction" Compute node property varies between the tests.

The "Compute Error" Compute node executes the following or nothing, depending on the test.

Tests:

Test1: Compute node "Transaction" is "Automatic", COMMIT or ROLLBACK not invoked, flow fails (THROW USER EXCEPTION in "Compute Error" node). 

Result: no data is inserted in any of the databases.

Explanation: no matter the database nor the coordinated or uncoordinated nature, having no COMMIT sentences executed, due to the "Automatic" node transaction type, the flow, after failing, ROLLBACKs to all the pending transactions.

Test2: Compute node "Transaction" is "Automatic", COMMIT or ROLLBACK not invoked, flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Result: data is inserted in all databases.

Explanation: no matter the database nor the coordinated or uncoordinated nature, having no COMMIT sentences executed, due to the "Automatic" node transaction type, the flow, after successful finishing, COMMITs to all the pending transactions.

Test3: Compute node "Transaction" is "Commit", COMMIT or ROLLBACK not invoked, flow fails (THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table X (TABLAX; Compute node's "Data Source"'s DSN) inmediatly after finishing Compute node execution (it doesn't matter if the entire flow finish with success o failure). No data is inserted in Table Y (TABLAY).

Explanation: the interaction with the main database (X; Compute node's "Data Source"'s DSN) are governed with the "normal semantics", that is, according the success or failure of the node execution (node, not flow, because of the "Commit" value of "Transaction"). The interactions with other databases are governed according this:

Fig X1.

Result: just when the Compute node finish its execution (even when the flow haven't finished), data is inserted in Table X (TABLAX):

In that moment, as expected, Table Y (TABLAY) is empty:

When the flow ends, Table Y (TABLAY) is still empty:

Test4: Compute node "Transaction" is "Commit", COMMIT or ROLLBACK not invoked, flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table X (TABLAX; Compute node's "Data Source"'s DSN) inmediatly after finishing Compute node execution (it doesn't matter if the entire flow finish with success o failure); in that moment, no data is visible in Table Y (TABLAY). When the flow ends, data is inserted in Table Y (TABLAY).

Explanation: the same of test 3.

Result: just when the Compute node finish its execution (even when the flow haven't finished), data is inserted in Table X (TABLAX) (view test 3 image).

In that moment, as expected, Table Y (TABLAY) is empty:

When the flow ends, Table Y (TABLAY) has data:

Test5: Compute node "Transaction" is "Automatic", COMMIT invoked (alone, without DSN specification), flow fails (THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table X (TABLAX; Compute node's "Data Source"'s DSN) inmediatly after COMMIT is executed (it doesn't matter if the node or the entire flow finish with success o failure). The COMMIT execution only affects DSN X. No data is inserted in Table Y (TABLAY).

Explanation: the interaction with the main database (X; Compute node's "Data Source"'s DSN) are governed with the "normal semantics", that is, according the success or failure of the node execution or can be affected with COMMIT or ROLLBACK sentences without DSN specification. The interactions with other databases are governed according the stated in FigX1.

Result: just when COMMIT is executed (even when the node haven't finished), data is inserted in Table X (TABLAX).

When the flow ends, the two tables keep with the same state (TABLAX with data, TABLAY empty).

Test6: Compute node "Transaction" is "Commit", COMMIT invoked (alone, without DSN specification), flow fails (THROW USER EXCEPTION in "Compute Error" node). 

The results and explanation are the same of Test 5.

Test7: Compute node "Transaction" is "Commit" or "Automatic", COMMIT invoked (alone, without DSN specification), flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table X (TABLAX; Compute node's "Data Source"'s DSN) inmediatly after COMMIT is executed (it doesn't matter if the node or the entire flow finish with success o failure). The COMMIT execution only affects DSN X. When the flow ends, data is inserted in Table Y (TABLAY).

Explanation: the same as Test5.

Result: just when COMMIT is executed (even when the node haven't finished), data is inserted in Table X (TABLAX).

In that moment, as expected, Table Y (TABLAY) is empty:

When the flow ends, Table Y (TABLAY) has data:

Test8: Compute node "Transaction" is "Automatic", COMMIT invoked (with DSN specification for database Y), flow doesn't fails (no THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table Y (TABLAY) inmediatly after COMMIT is executed (even if the node hasn't finished execution; it doesn't matter if the node or the entire flow finish with success o failure). The COMMIT execution only affects DSN Y. No data is inserted in Table X (TABLAX) because the flow fails.

Explanation: the interaction with the main database (X; Compute node's "Data Source"'s DSN) are governed with the "normal semantics", that is, according the success or failure of the node or flow execution or can be affected with COMMIT or ROLLBACK sentences without DSN specification; they are obviously not affected by COMMITs accompanied by a specific DSN reference. The interactions with other databases are governed according the stated in FigX1.

Result: just when COMMIT is executed (even when the node haven't finished), data is inserted in Table Y (TABLAY).

In that instant, TABLAX is empty.

When the flow ends, TABLAX is still empty.

Test9: Compute node "Transaction" is "Commit", COMMIT invoked (with DSN specification for database Y), flow fails (THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table Y (TABLAY) inmediatly after COMMIT is executed (even if the node hasn't finished execution; it doesn't matter if the node or the entire flow finish with success o failure). The COMMIT execution only affects DSN Y. Once the node finishes its execution, data is inserted im Table X (TABLAX); it doesn't matter if the entire flow finish with success o failure.

Explanation: the same as Test8.

Result: just when COMMIT is executed (even when the node haven't finished), data is inserted in Table Y (TABLAY).

In that instant, TABLAX is empty.

When the node ends, TABLAX has data

.

Test10: Compute node "Transaction" is "Commit" or "Automatic", COMMIT invoked (with and without DSN specification for database Y), flow fails (THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table Y (TABLAY) and Table X (TABLAX) inmediatly after COMMITs are executed (even if the node hasn't finished execution; it doesn't matter if the node or the entire flow finish with success o failure).

Explanation: the same as Test8.

Result: just when COMMITs are executed (even when the node haven't finished), data is inserted in Table X (TABLAX) and Table Y (TABLAY).

Test11: Compute node "Transaction" is "Commit" or "Automatic", ROLLBACK invoked (without DSN specification), flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: data is inserted in Table Y (TABLAY) after the flow ends. Table X (TABLAX) is empty.

Explanation: the same as Test8.

Result: 

Regardless the node transactionality configuration, the data in Table Y (TABLAY) is only commited when the flow finalizes (not the node).

Test12: Compute node "Transaction" is "Commit" or "Automatic", ROLLBACK invoked (with and without DSN specification), flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Expected Result: both tables are empty when the flow fhinishes.

Explanation: the same as Test8.

Result: 

Test13: Compute node "Transaction" is "Automatic", ROLLBACK and COMMIT invoked (with and without DSN specification), flow doesn't fail (no THROW USER EXCEPTION in "Compute Error" node). 

Result: 

Test14: COMMIT and ROLLBACK placed in not permitted places (Compute node without Data Source). 

The following error is generated (in spanish):

'[Microsoft][Administrador de controladores ODBC] No se encuentra el nombre del origen de datos y no se especificó ningún controlador predeterminado'