Staging framework

Use this framework to create a historical table fed with all data changes in a new row. The journal of transactions is provided with the additional columns: last update date, receiver, sequence IDs and action type.

1. From the window called Adding tables to the model, select the Staging framework in the list.
2. Select the tables and click Ok. The list of columns is displayed.
   
3. Select the columns you wish to capture. 
   The first columns are technical ones.
   DD_ACTION cannot be unselected, as no other column could be added if the insert/update/delete transactions were not included.
   For the other columns, you can select or deselect them according to your wish.
   The staging table will result from the update made in at least one of the selected columns.

   Information noteNote: Transactions like insert and delete are considered modifiers to the columns as a whole.

   The transformation is performed automatically and you are free to check it or edit it, if needed.

4. Go to the Properties of the table. The window displayed shows the columns sent to the target.
   
   The definition of the key sequence is taken into account to create the target table and, above all, to generate the “where” clause for update and delete statements.
   DATE_TIME and ACTION fields were added at the beginning of the list.
   You can change the key field(s) of the target (if any).

   There are various options for the key sequence definition. This example describes some of them:

   • Set a key on DateKey column if you want to get the last transaction for each DateKey in the target table. Only updates will appear in the target table.

     If you also set a key on ACTION, you will get all transactions (insert, update, delete) occurred according to the DateKey.

   • If no key is assigned to a column: a line is added in the target table for every action. This addition is not related to the value of DateKey.

5. Click Finish.
6. Follow Preparing the replication procedure starting from step 4, then Operating a replication.