Load Tracking Records

1. Define the Tracking Table Structure
• Ensure that there is a tracking table for each target table with a "_RGC" suffix. The tracking table should include the following columns:
• JOBID: The Job Id of the ETL session.
• JOBNAME: The name of the ETL job.
• OPERATIONDATE: Timestamp of when the operation occurred.
• OPERATIONTYPE: Type of operation (Insert or Update).
• All columns from the target table, such as <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>.
2. Create the SQL Command for Inserting Tracking Data
• Prepare an SQL insert command that loads tracking records into the tracking table (<%=odiRef.getTable("L","TARG_NAME","A")%>_RGC).
• The SQL statement should include:
• Job-related information such as JOBID and JOBNAME fetched from the ETL session (<%=odiRef.getSession("SESS_NO")%> and <%=odiRef.getSession("SESS_NAME")%>).
• The current timestamp (Current_timestamp) as the OPERATIONDATE.
• A conditional statement to determine the operation type (Insert or Update) based on the value of IND_UPDATE. For example:

§  Case when IND_UPDATE = 'I' then 'Insert' else 'Update' end as OPERATIONTYPE.

• Data columns from the integration layer (<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>).
• A condition to filter rows for which IND_UPDATE <> 'N', meaning only rows marked as Insert (I) or Update (U) should be tracked.
2. Execute the Insert Command for Tracking Records
• The SQL query for inserting tracking records will look like:

o   insert into <%=odiRef.getTable("L","TARG_NAME","A")%>_RGC(JOBID, JOBNAME, OPERATIONDATE, OPERATIONTYPE, <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>)

o   select <%=odiRef.getSession("SESS_NO")%> /* JOBID */,

o          <%=odiRef.getSession("SESS_NAME")%> /* JOBNAME */,

o          Current_timestamp /* OPERATIONDATE */,

o          Case when IND_UPDATE = 'I' then 'Insert' else 'Update' end /* OPERATIONTYPE */,

o          <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>

o   from <%=odiRef.getTable("L","INT_NAME","A")%>

o   where IND_UPDATE <> 'N';

4. Ensure the Tracking Table Exists (Optional)
• If the tracking table does not exist, it may be useful to create it automatically. This can be done by extending the Integration Knowledge Module (IKM) to check if the tracking table exists and create it if necessary.
• This can be handled through scripting or by using the IKM's capabilities to dynamically create the tracking table before loading the records.
5. Validate Tracking Record Insertion
• After executing the insert command, ensure that the tracking records are correctly inserted into the tracking table.
• Check that:
• The JOBID and JOBNAME correspond to the current ETL job session.
• The OPERATIONDATE reflects the correct timestamp.
• The OPERATIONTYPE correctly indicates either Insert or Update.
• All relevant data columns from the source table are included in the tracking table.
6. Monitor and Review the Data
• Periodically check the tracking tables to ensure that the operations are accurately tracked.
• Ensure that the ETL job runs without errors, and that the tracking data matches the operations performed on the target tables.
7. Extend the Customization as Needed
• Customize the tracking data insertion further based on business or regulatory requirements.
• You may include additional metadata, such as the user who performed the operation or any other relevant information required for compliance.