Database Collection Agent Transaction Behavior

Owned by Jenni Wallin
Last updated: Mar 24, 2023 by Kevin Wu
2 min read

The Database collection agent performs additional operations to ensure that data is not recollected or lost if the workflow aborts before the collection of a batch has finished correctly.

  1. A unique Transaction ID is retrieved for each new batch.
     

  2. The pending transaction table is queried for all pending Transaction IDs, to be compared with the transaction IDs in the working table, from which the agent will collect.
     

  3. The SQL query is built and executed, and all matching rows are collected. In addition to the user defined condition, the agent adds some conditions to the query, to ensure that pending data, cancelled data and data marked as collected is not collected.
     

  4. For each row that has been successfully converted to a UDR, the agent updates its Transaction ID column to the Transaction ID retrieved in bullet 1.
     

  5. When all rows matching the query have been successfully collected, the After Collection configuration in the Source tab, is used.

    1. If Remove, all rows with the given Transaction ID are removed in batches of the size configured as the Commit Window Size, in the Advanced tab.

    2. If Mark as Collected, all rows with the given Transaction ID are updated with the reserved Transaction ID value -1.

    3. If Run SP, the user defined stored procedure is executed. For further information, see After Collection Stored Procedure in Tables and Stored Procedures.

Emits

The agent emits commands that changes the state of the file currently processed.

Command

Description

Begin Batch

Emitted after the SQL select statement execution.

End Batch

Emitted after the SQL select statement execution, when all possible matching rows have been successfully inserted as UDRs in the workflow.

Note!

If the SQL select statement does not return any data, Begin and End Batch will not be emitted. Not even if Produce Empty Files is selected in a Forwarding Disk agent.

Retrieves

The agent retrieves commands from other agents and based on them generates a state change of the file currently processed.

Command

Description

Cancel Batch

All rows with the current Transaction ID are updated with the reserved Transaction ID -2. If these rows are to be recollected, the Transaction ID column must first be set to 0(zero). If set to NULL this row cannot be collected.

The database row that issued the Cancel Batch request is written to the System Log.

Note!

If the Cancel Batch behavior defined on workflow level is configured to abort the workflow, the agent will never receive the last Cancel Batch message. In this situation the rows will not be updated with the reserved Transaction ID  -2 .

Hint End Batch

An End Batch call will be issued, causing the original batch returned by the SQL query to be split at the current UDR. The database commit command is executed, followed by a new select statement to fetch the remaining UDRs from the table.