Forum Stats

  • 3,853,717 Users
  • 2,264,259 Discussions
  • 7,905,435 Comments

Discussions

How to take backup of a table in ODI

User_XG491
User_XG491 Member Posts: 4 Green Ribbon

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 :)

Answers

  • Christyxo
    Christyxo Member Posts: 151 Silver Badge

    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 ;