Translating journal codes into meaningful information

When you map your tables using LiveAudit, you can map your audit columns to the &ENTTYP journal control field to let you know what kind of change has been made on your source table. Because there are many changes that can take place, if you map a journal control field to a target column, CDC Replication generates a distinct two-letter journal code on your audit table to help you identify the event that occurred on the source. For example, if there has been a row inserted in your source table, then in response to this update, CDC Replication inserts a row into the target table with the journal code 'PT'.

The journal codes your system generates depend on the database platform that you are using. You may want to customize journal codes so that they are more meaningful in your organization. For example, instead of having the journal code PT to indicate that there has been a new row inserted in your source table, you may want to use a one-letter code such as I to identify the insert.

The %IF column function evaluates conditional expressions and returns different values if the expression is true or false. The following example illustrates how you can use the %IF column function to convert journal codes into custom letters 
%IF(&ENTTYP=PT OR &ENTTYP=RR,I, %IF(&ENTTYP= UB, B, %IF(&ENTTYP= UP, 
A, %IF(&ENTTYP=DL,D,R))))
This expression results in the following activities:
  • If a row has been inserted or refreshed on the source table, then CDC Replication generates I on the audit table.
  • If the before image on the source table has been updated, then CDC Replication generates B on the audit table.
  • If the after image has been updated on the source table then CDC Replication generates A in the audit table.
  • If a row on the source table has been deleted, then CDC Replication generates D on the audit table, otherwise it generates R on the audit table.