Data may propagate between workflows or systems by combining a Database forwarding agent with a Database collection agent, where the exchange point is a mutual database table.
When using the same table, the collection agent must make sure that it does not collect data that the forwarding agent is simultaneously feeding with data from its current batch.
Note!
Transfer of UDRs between workflows is ideally handled using the Inter Workflow agents. The Database agent approach is useful in case of wanting to change the content of the UDRs. Another use is when wanting to pass on MIM values and merge batches at the same time. In the Inter Workflow agent case, only the MIM values for the first (Header MIMs) and last batch (Trailer and Batch MIMs) are considered. Using the Database agents, MIM values may be mapped into database columns.
Pending Transaction Table
The database, hosts a table where pending transactions are registered. A pending transaction is an ongoing population of a table by a Database forwarding agent. The pending transaction continues from a Begin Batch to an End Batch. The purpose of this table is for Database collection agents to avoid collecting pending data from the table that a Database forwarding agent is currently distributing to.
The pending transaction table holds database names and table names. Thus, before a collection session starts, the collector evaluates if there are any pending Transaction IDs registered for the source database and table. If there are, rows matching the Transaction IDs will be excluded.
In the following figure, the Database collection agent will exclude all rows with transaction ID 187.
A Database forwarding agent may be configured to target a stored procedure, instead of a table directly. In such cases the user must specifically select the table that the stored procedure will populate (SP Target Table). The reason for that is that the pending transaction table must contain the table name, not the SP name, so that the selected table name in the Database collection agent can be matched.
Exchanging Storable Data
All UDRs have a special field named Storable. This field contains the complete UDR description and all its data. If UDRs, having many fields or a complex structure to be exchanged, it could be suitable to store the content of the Storable field in the database. In that way the table would only need one column. The database type of that column must be a RAW, LONG RAW or a BLOB.
Note!
The data capacity of the column types RAW, LONG RAW and BLOB differs. Consult the database documentation. For performance reasons it is advised to use the smallest type possible that fits the UDR content.
When configuring the Database forwarding agent, the Storable field from the UDR is be assigned to the table column in a straight forward fashion. However, when collecting that type of data the column assignment must not be made to the Storablefield. Instead To UDR is selected in the Value Type field.
When the Database collection agent detects a mapping of type To UDR, the selected UDR type is not consulted for what UDR type to create. The information about the UDR type will be found in the data of the column itself. Thus, if the UDR stored in the column is of another type than selected in the Source tab, the type to be distributed by the Database collection agent is the type actually found.