Specifying Transaction Control Information

Multiple input links writing to a single UniVerse data source can be associated together as a transaction group. The transaction grouping feature is turned on and off using the Enable transaction grouping check box on the Transaction Handling tab (it is off by default).

About this task

If transaction grouping is off, you can specify the following information on the Transaction Handling tab:

  • Type a suitable value in the Rows per transaction field. This is the number of rows written before the data is committed to the data table. The default value is 0, that is, all the rows are written before being committed to the data table.
  • Select a suitable Isolation Level. The isolation level specifies how potential conflicts between transactions (for example, dirty reads, nonrepeatable reads, and phantom reads) are handled.

If transaction grouping is enabled, the following rules govern the grouping of links:

  • All the input links in the transaction group must originate from the same Transformer stage.
  • The ordering of the links within the transaction group is determined in the preceding Transformer stage.
  • A transaction group cannot use a Rows per transaction other than 1. Using an Isolation level of Auto-commit is permitted, but obviates the effect of organizing links in a transaction group.

You should be aware of the following facts about transaction groups (assuming that you commit on every row):

  • A transaction starts at the beginning of each iteration of the Transformer stage preceding the UniVerse stage. Any uncommitted changes left over from a previous transaction are rolled back.
  • The links in the transaction group are processed in the order laid down in the Transformer stage. Individual links can be skipped if constraints laid down in the preceding Transformer stage so dictate.
  • Each link in the transaction group can specify whether to rollback on failure. A rollback on any link causes the transaction to be abandoned and any subsequent links in the group to be skipped.
  • Each link in the transaction group can be set to rollback if a constraint on that link is not met. Again, such a rollback causes the transaction to be abandoned and any subsequent links in the group to be skipped.
  • The row counter for each link will be incremented only if the SQL associated with the link executes successfully and the transaction is successfully committed.
  • The transaction ends after the last link in the transaction group is processed, unless a preceding link performs a rollback, in which case the transaction ends there.

To specify transaction control information for a transaction group:

Procedure

  1. Click the Transaction Handling tab.
  2. Select the Enable transaction grouping check box.
  3. Choose an appropriate transaction isolation level to use from the Isolation level list. The isolation level specifies how potential conflicts between transactions (for example, dirty reads, nonrepeatable reads, and phantom reads) are handled. (If you select Auto-commit, you are specifying that every statement will effectively be executed in a separate transaction, which will obviate the advantages of transaction groups.)
  4. For transaction groups, Rows per transaction is automatically set to 1 and you cannot alter it.
  5. Supply necessary details about the transaction group in the grid. The grid has a line for every link in the transaction group. The links are shown in transaction processing order, which is set in the preceding Transformer stage. Each line contains the following information:
    • Input name. The name of the input link. You cannot change this.
    • On Skip. This is used to specify whether to continue or to roll back if a link is skipped due to a constraint on it not being satisfied. Choose Continue or Rollback from the list.
    • On Fail. This is used to specify whether or not to continue or rollback on failure of the SQL statement. Choose Continue or Rollback from the list.
    • SQL. Shows the SQL statement associated with the input link. You cannot change this, but clicking the cell will display the entire statement.
      Note: If the UniVerse stage uses a local connection to DSEngine (the data source is localuv), then you cannot associate data written from different input links in a single transaction group. Every input link appears in a transaction group on its own, even if the links originate from the same Transformer stage. You can view the transaction group information for a particular link by choosing it from the Input name list.