3.1.3.2 Generating Change Logs with Hive

The change log captures any modifications made such as adding new data, changing the data, or deleting the existing data.

To generate change logs in Hive, follow these steps:

1. Configure the CHG_LOG_REF table. For the Change log table component, the following values are available:

   Table 3-4 H2T Change Log Component Values

   Value	Description
   Table Name	This value is the name of the FSDM table in which a change has been made.
   Column Name	This value is the name of the FSDM column in the table in which a change has been made.
   Source File Name	This value is the name of the source extract file from which the field with the changed value was loaded.
   Source Field Name	This value is the name of the field with the changed value in the source extract file where the value is changed.
   Format	This value is a textual representation of the column or field format or data type.
   Change Entry User Identifier	This value is the identifier of the person who entered the change. This is a unique identifier for the user who makes the change.
   Change Entry User System Logon Identifier	This value is the user name of the user who makes the change.
   Mantas Change Log Identifier	This value is the Oracle-specific identifier for this change log record that is unique across the FSDM.
   Change Date	This value is the date on which the change was made.
   Change Date - UTC	This value is the date in UTC on which this data change was made.
   Change Time	This value is the time at which the change was made.
   Change Time - UTC	This value is the time in UTC on which this data change was made.
   Change Time Offset	This value is the Number of Hours Offset for Change Log Time.
   Old Value	This value is the old value which was assigned to the specified table column.
   New Value	This value is the new value which is assigned to the specified table column.
   Key1	This value is the textual representation of the value associated with the first column in the Primary Key or the user-defined primary key of the table containing the changed record.
   Key2	This value is the textual representation of the value associated with the second column in the Primary Key or the user-defined primary key of the table containing the changed record.
   Key3	This value is the textual representation of the value associated with the third column in the Primary Key or the user-defined primary key of the table containing the changed record.
   Key4	This value is the textual representation of the value associated with the fourth column in the Primary Key or the user-defined primary key of the table containing the changed record.
   Change Type	This value is the code that indicates whether the change is an insert (add), a delete (removal), or an update.
   Customer Notification Suppression Indicator	This value indicates whether the customer is notified of the change through email or not.
   Source System	This value is the source system from which this data content is extracted.
   Processing Batch	This value is the Ingestion batch in which Oracle processed this data record.
   Submission Date	This value is the Business date for which the data record is provided to Oracle.

2. Run Ingestion. This will set a baseline for the table you wish to capture in the change log.
3. After Ingestion is done, create a script to create a back up table for the table to be captured in the change log. For example, if you are generating a change log for the ACCT table, create the backup table ACCT_BKP.
4. The next day, run Ingestion after running H2T.
5. Use the following DT to capture the change log:

   CHGLOG_CAPTUREHIVE

6. Associate a task to the new DT. For more information about OFSAAI Data Transformation (DT), Post Load Changes in the Oracle Financial Services Analytical Applications Infrastructure User Guide.

The following tables provide the columns found in the CHG_LOG_REF table with an example of what will appear in each column.

Column Name	Example
CHG_TBL_NM	ACCT
CHG_COL_NM	PRMRY_CUST_INTRL_ID,ACCT_STAT_CD,INSTN_CNTRY_CD,STMT_SUPR_F L,NOTFY_LTR_SUPR_FL,RECALCITRANT_FL,US_POA_SIGN_FL,STNDG_INS TR_US_ACCT_FL
CHG_QUERY_KEY	ACCT_INTRL_ID
CHG_OUTPUT_KEY	ACCT_INTRL_ID
CHG_LOG_TBL_ENBL_FL	Y
SRC_TBL_NM	ACCT_BKP Note: This field must contain the back up table name (created in Step 3) for which the Change log will be created.
SRC_COL_NM	PRMRY_CUST_INTRL_ID,ACCT_STAT_CD,INSTN_CNTRY_CD,STMT_SUPR_F L,NOTFY_LTR_SUPR_FL,RECALCITRANT_FL,US_POA_SIGN_FL,STNDG_INS TR_US_ACCT_FL
SRC_TBL_QUERY_KEY	ACCT_INTRL_ID
SRC_OUTPUT_KEY	ACCT_INTRL_ID
CHG_LOG_DATASET	<CLOB>

Column Name	Example
CHG_TBL_NM	ACCT
CHG_COL_NM	PRMRY_CUST_INTRL_ID,ACCT_STAT_CD,INSTN_CNTRY_CD,STMT_SUPR_F L,NOTFY_LTR_SUPR_FL,RECALCITRANT_FL,US_POA_SIGN_FL,STNDG_INST R_US_ACCT_FL
CHG_QUERY_KEY	ACCT_INTRL_ID
CHG_OUTPUT_KEY	ACCT_INTRL_ID
CHG_LOG_TBL_ENBL_FL	Y
SRC_TBL_NM	ACCT_BKP Note: This field must contain the back up table name (created in Step 3) for which the Change log will be created.
SRC_COL_NM	PRMRY_CUST_INTRL_ID,ACCT_STAT_CD,INSTN_CNTRY_CD,STMT_SUPR_F L,NOTFY_LTR_SUPR_FL,RECALCITRANT_FL,US_POA_SIGN_FL,STNDG_INST R_US_ACCT_FL
SRC_TBL_QUERY_KEY	ACCT_INTRL_ID
SRC_OUTPUT_KEY	ACCT_INTRL_ID
CHG_LOG_DATASET	<CLOB>

As part of the product, the CHG_LOG_REF table is pre-populated with along with fields of interest. Clients can add additional tables and their respective fields to the CHG_LOG_REF table in order to capture any modifications that occur in those tables in the Change Log.