|
Variable |
What you see in omnient.ini |
Replacement value |
Recommended target column type |
|
User name |
sv_trans_username |
Name of the user who committed the transaction containing the row operation that is being recorded. Note that on IBM i, if the transaction containing the operation is a pseudo transaction (that is, one containing a group of self-committing operations), the user name may not actually be the user that did the update. In this case, the transaction inherits the name of the user who did the first update in the transaction, and this is then used as the transaction user ID for all updates in the transaction. |
VARCHAR(70) Note: 70 is the maximum that this value can have, but the actual maximum will vary depending on the source DBMS that created the transaction. |
-
In addition, you can choose to map the before values of any or all the columns from your source table.
Example:
Source table has 5 columns: A, B, C, D, E; Target table has been setup with 18 columns:
-
Columns A, B, C, D, E
-
Columns for each of the following:
-
Before value of A
-
Before value of B
-
Before value of C
-
Before value of D
-
Before value of E
-
System variable Row Manipulation Type
-
System variable Sending DBMS Type
-
System variable Sending Server Name
-
System variable Sending Table Name
-
System variable Transaction ID
-
System variable Transaction Row Sequence
-
System variable Transaction Timestamp
-
System variable User Name
The source table has column A as the primary key, BUT the target table must have a unique set of columns that can make up the primary key on the target, for example, two system variables (sv_trans_id, sv_trans_row_seq). These together are always unique since the Transaction ID is a program-generated number and the Transaction Row Sequence is a number from 1 to the row number changed during the transaction.
Map the system variables using one of the following ways:
-
After you distribute the tables from source to target(s):
-
Right-click one of the target tables.
-
Select Properties.
-
Notice that the system values and the before values have not been mapped.
As long as you have named your columns as defined above, selecting the Defaults button on the Mapping tab automatically maps all system variables.
-
A second way to map the system variables is similar to mapping the before values.
-
-
On the Mapping tab, under Method for the before value or the system variable, select Copy input.
-
Click Edit Source at the bottom of the Mapping tab to access the Methods Mapping Parameters window.
- Under the Parameter Type column, select whether this is an “Update Before Value” or a “System
Variable.”
A data enhancement function to supply the “before” value of a column to which it is applied allows you to determine the prior value of a column that has been updated. In the case of Insert and Delete row operations, the before value of a column is undefined or null.
If you select Update Before Value, Parameter Value contains the source table column names from which you select the appropriate one.
If you select System Variable, the list for Parameter Value contains only the eight system variable names from which you select the appropriate one.
For system variables only, you can also use the following method:
-
Select Expression from the Method column of the Mapping tab.
-
Click Edit Source.
-
Select the system variable function from the Method list on the Target Column Expression work page. This does not work for “Update Before Value.”
-
Validate and commit your model.