How to take backup of a table in ODI

User_XG491

Hi All,

I am new ODI user, I am trying to build a new Mappings between two tables, I was having a plan to first take the backup of the target table, use the backup table as the target. and once the mappings were successful , replace the backup table with actual target table.

(Do I need to take the backup at DB level and then import the backup table/connect to the backup table in ODI ?)

It would be a great help if one of you could you help me with details/steps for the same :)

Christyxo

Is there a reason you want to apply the changes to the backup and not the original? You can remove 1 step in your process by making a backup and then updating the original. The backup exists if there's a problem and you want to revert to the old.

If you want to continue with your 3 stage process you can use the begin and end commands within a mapping.

I assume that you have your target table, and backup table modelled in ODI? If not, make your backup (can be empty), and reverse engineer that in ODI.

I'm also assuming that your table isn't partitioned, indexed, or has referential integrity? All of which change the process you need to. But as a very brief example, you could create a mapping that is loading the backup table, and run a begin and after command to swap the tables:

In the begin command I used this:

BEGIN
 DROP TABLE 
  <%=odiRef.getSchemaName( "D" )%>.BACKUP_TABLE ;

 CREATE TABLE 
  <%=odiRef.getSchemaName( "D" )%>.BACKUP_TABLE 
 AS 
  ( SELECT * FROM <%=odiRef.getSchemaName( "D" )%>.ORIGINAL_TABLE ) ;
END ;

In the end command I used this. :

BEGIN
 ALTER TABLE 
  <%=odiRef.getSchemaName( "D" )%>.ORIGINAL_TABLE 
 TO BACKUP_TABLE_1;

 ALTER TABLE 
  <%=odiRef.getSchemaName( "D" )%>.BACKUP_TABLE 
 TO ORIGINAL_TABLE;

 ALTER TABLE 
  <%=odiRef.getSchemaName( "D" )%>.BACKUP_TABLE_1 
 TO BACKUP_TABLE;

END ;