ODI Code Migration –Export and Import

Sometimes we required to prepare new ODI instances with existing odi customizations content by merging to new content repository.Best example would be new OBIA instance setup.
In such cases we usually do the export/import of odi repositories as a whole
or Manually Normal export/import of custom code into new instance
or Smart export/import which usually takes lot of time.

What if we have too many customizations to migrate ?,
Smart export/import will fail most of the times with java heap issues for such huge xmls.

And only option left is normal export/import of odi content.One more challenge here is identifying only the changed objects to migrate in the source odi repository.

I have come up with some back-end sql queries to identify each and every objects of OBIA 11.1.1.x source odi repository so that those can be exported and imported into new target odi repository in normal fashion.

Export Source ODI objects:

Step 1. Export Topology

Connect to Source instance using ODI Studio.

Navigate to TopologyàExport the Topology

Step 2.Export Data stores.

Use below query to identify modified Datastores

select      MOD_FOLDER_NAME, mod_name, SMOD_NAME,    (case when snp_mod_folder.first_user<> 'SUPERVISOR' then 'Export Model Folder' when  snp_model.first_user<> 'SUPERVISOR' then 'Export Model' else 'Export Sub Model' end ) export from snp_mod_folder,snp_model,snp_sub_model where snp_model.i_mod(+)=snp_sub_model.i_mod and SNP_MOD_FOLDER.I_MOD_FOLDER(+)=SNP_MODEL.I_MOD_FOLDER and snp_sub_model.last_user <> 'SUPERVISOR' order by 4,1,2,3;

export them .

Step 3. Export Variables

Identify modified variables using below query

select      var_name,    (case when var_type='G' then 'Global' else 'Project' end ) var_type from snp_var  where last_user <> 'SUPERVISOR' order by 2,1;

Export them.

Step 4. Export Sequences

Identify modified sequences using below query and export.

select    seq_name,    case when seq_type='G' then 'Global' else 'Project' end as Seq_type from snp_sequence where last_user <> 'SUPERVISOR' order by 2,1;

Step 5. Export User Functions

Identify using below query and export

select (case when I_project is null then 'Global' else 'Project' end) as    Ufunc_type,group_name,ufunc_name from snp_ufunc where last_user <> 'SUPERVISOR' order by 1,group_name,ufunc_name;

Step 6. Export KMs

Identify using below query and export them

select trt_type,trt_name from snp_trt where last_user <>'SUPERVISOR' and trt_type <>'U' order by trt_type desc,trt_name ;

Step 7. Export Task Folders

Identify Using below query and export them.

select folder_name from snp_folder where first_user <> 'SUPERVISOR'  and last_user <> 'SUPERVISOR' and par_i_folder=(select i_folder from snp_folder where folder_name='Mappings')—Specify your project main folder order by par_i_folder asc,folder_name asc;

Step 8. Export Load Plan Components.

Use below query to find modified load plan components and export.

select scen_folder_name,load_plan_name from snp_load_plan,snp_scen_folder  where  SNP_SCEN_FOLDER.I_SCEN_FOLDER=snp_load_plan.i_scen_folder  and snp_load_plan.last_user <> 'SUPERVISOR'   and substr(load_plan_name,1,1) in ('0','1','2','3')  order by 2;

Step 9. Export BIACM content

Login BIACM --> Export Setup Data and export all the objects listed under this.

Import into Target ODI Repository:

Import all the ODI objects to the Target instance using ODI studio.

Step 1. Backup Repository schema

Take backup of ODI master and work repositories for safer side.

Step 2. Export Topology

Connect to Target instance through ODI studio.

Navigate to TopologyàExport Topology for backup connections.

Step 4. Renumber repository ID

Navigate to Topology àRepositoris

Then Renumber for both Master and Work Repositories IDs

Step 5. Import Topology

Navigate to Topology àImport Topology and choose the source topology to import.

Step 6.Import KMs

Navigate to ProjectsàBI Apps ProjectàKnowledge ModulesàSelect KM and right click import .

Step 7. Import Data stores

Navigate to DesigneràModels

Then import all Model folders,Models and Data Stores.

Step 8. Import Variable

Import Global Variables.

Navigate to DesigneràGlobal ObjectsàGlobal Variables

Import Project Variables.

Navigate to DesigneràProjectsàBI Apps ProjectàVariables

Step 9. Import Sequences

Navigate to DesigneràProjectsàBI Apps ProjectàSequences.

Step 10. Import Functions

Import Global User Functions.

Navigate to DesigneràGlobal ObjectsàGlobal User Functions

Import Project User Functions.

Navigate to DesigneràProjectsàBI Apps ProjectàUser Functions

Step 11.Import Folders

Navigate to DesigneràProjectsàBI Apps ProjectàMappings

Import sub folders

Step 12. Import Load Plan components

Import all the exported load plan components in target instance.

Step 13. Import BIACM content

Use Import setup data in BIACM portal page to import all the Data load parameters, Reporting Parameters, Domain Mappings and System setups.

Step 14. Validate the topology

Make sure Datasource Num ID and Context is set proper.

Step 15. Create Load Plans and Run