ODI Master: Create Backup Table

1. Understand the Purpose of Creating a Backup Table

Purpose: The backup table is created to store a copy of the data from the target table. This allows you to restore the data if something goes wrong during the data load process.
Timing: The backup table should be created before any changes are made to the target table (e.g., before updating or inserting data into the target table).

2. Define the Backup Table Naming Convention

The backup table will follow a naming convention that appends _BCK to the original target table name.

Example: If the target table is named TARG_NAME, the backup table will be named TARG_NAME_BCK.

3. Write the SQL Command to Create and Populate the Backup Table

The command to create and populate the backup table should copy all relevant data from the target table.
Here’s the SQL command to create and populate the backup table:

· CREATE TABLE <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK AS

· SELECT <%=odiRef.getTargetColList("", "[COL_NAME]", ",", "")%>

· FROM <%=odiRef.getTable("L","TARG_NAME","A")%>;

CREATE TABLE <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK: This creates a new table named with the original target table name, followed by _BCK (the backup suffix).
SELECT <%=odiRef.getTargetColList("", "[COL_NAME]", ",", "")%>: This dynamically fetches all the columns from the target table, ensuring the backup table is a mirror of the original table’s structure and data.
FROM <%=odiRef.getTable("L","TARG_NAME","A")%>: This references the target table from which the data will be copied.

4. Integrate the Backup Table Creation into the ETL Workflow

This task should be included in the ETL workflow before any data load tasks that modify the target table.
In tools like Oracle Data Integrator (ODI), you can integrate this SQL command as a task or step within the workflow, ensuring it runs automatically when the ETL process starts.

5. Ensure Correct Column Mapping

The getTargetColList() function dynamically fetches the list of target columns, ensuring the backup table will include all the necessary columns from the original target table.
This ensures that the backup table structure matches the target table, including column names and data types.

6. Verify the Backup Table After Creation

After running the task, verify that the backup table has been created and populated correctly by querying it.

o SELECT * FROM <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK;

Ensure that the data in the backup table mirrors the data in the target table at the time of backup.

7. Monitor the Backup Table Creation Process

Monitor the ETL process to ensure the backup table is created correctly. Any issues such as permission errors, table already existing, or data inconsistencies should be addressed.
You might want to log any issues or exceptions to track the success or failure of this task.

8. Ensure No Conflicts with Existing Backup Tables

If a backup table with the same name already exists (for example, from a previous run), you need to either:

Drop the existing backup table before creating a new one.
Use a timestamp or unique identifier in the backup table name (e.g., TARG_NAME_BCK_20250307) to prevent naming conflicts.

· DROP TABLE <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK;

9. Automate the Process

Automate the creation of the backup table as part of your regular ETL job. This ensures that the backup is always taken before the target table is modified during the data load process.

10. Test the Backup Table Creation Process

Before using this process in a production environment, test it in a development or staging environment to ensure the backup table is created correctly without any issues.

11. Document the Process

Document the backup table creation process in your ETL documentation, so all team members are aware of the backup strategy and the importance of creating backup tables.

ODI Master