Migration Issues in ODI and the dark world of Internal IDs

Almost everyone who has tried to Export and Import ODI objects individually in two different environments has faced error of this kind
“Interface 101555 references a table ID (190555) that does not exist in you repository”
So what is this ID exactly ?
This comes straight from the horse's mouth (Read Oracle)

Each Object has a unique ID associated with itself.  This ID contains the Workrepository ID of the work repository to which it belongs to. At creation time, every work repository has an ID called the “Work

Repository ID”. This ID must be unique across all work repositories of an Oracle DI installation.

Objects IDs within a given work repository are constructed as follows:

[UniqueNumber][WorkID]where 

Where:

·   [UniqueNumber]  is a unique ID in the current work repository, issued from the table of IDs

                (SNP_ID)

·   [WorkID] is the ID of the current work repository

For example, if you create the 20th Interface in the work repository ID 229, its unique ID would be

20229. The next Interface in the same repository will have ID 21229. Every time a new object ID is

constructed the last unique number for this object is incremented in the table SNP_ID. In our example,

this table will contain 21 in the field related to the Interfaces unique number.

All objects created within the same repository have the same 3 last digits. This mechanism is

implemented for 2 reasons:

1. Avoid any ID conflicts when exporting and importing from one work repository to another

2. Understand the provenance of every object, simply by looking at its Internal ID. The 3 last

digits always refer to the repository where it was created

Object Dependencies:

ODI Objects are dependent on each other. For example, An Interface contains a Source Table and a Target table belonging to same or different models. Hence it is imperative to have the objects migrated in proper sequence. For example, if an Interface object is migrated without migrating the Source and Target Models (and the tables within it), the migration will fail. It is necessary to understand which objects are being used by the object that needs to be migrated.

Objects that are used by another object (e.g. Package containing multiple interfaces, procedures etc) are referred using the unique IDs of the component objects. For example, a Package will contain references of the component interfaces and procedures used in it. These references are nothing but the Internal IDs of the component objects. 

Retaining original IDs while migration:

Objects can be imported in Duplication mode or Synonym mode.

Duplication Mode : A new unique ID of the object is created when importing it. This method is not recommended since this method can cause conflicts while migrating ojects that are dependent on each other.

Synonym Mode: Original IDs are retained while importing. The advantage of this mode for importing is that there are no ID conflicts generated.

All objects are to be migrated in Synonym mode. The use of INSERT/UPDATE or INSERT_UPDATE modes are explained in latter section

Order of Migration:

Objects should be migrated (imported) in the following order assuming that Technology and Contexts have been imported already

1.     Model Folder

2.     Model

3.     Interface/Procedures

4.     Packages

5.    Scenarios 

Steps for Importing Objects:

Now that you have exported all relevant objects from the Target Repository, you can import them

safely in the new Repository. To do so, you can follow this procedure:

1.Connect to new Work Repository

2. Import the Model Folders in the order in which they appear in Target Repository. For example,

if you have exported 3 Model Folders “MFOLD1/MFOLD2/MFOLD3” without their child

components, you need to import MFOLD1 before MFOLD2 and so forth. Make sure to set the

import mode to:

a. “Synonym Mode INSERT” if your Model Folder does not exist in the new repository

b. “Synonym Mode UPDATE” if your Model Folder already exists in the new repository

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Model Folder already

exists.

3. Import your Models in “Synonym Mode INSERT_UPDATE”. If a Model depends on another

Model, for example through Foreign Key references between datastores of the 2 models,

make sure to import the referenced Model first.

4. Import your Project that was exported without its child components and make sure to use:

a. “Synonym Mode INSERT” if the Project does not exist

b. “Synonym Mode UPDATE” if the Project already exists

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Project already exists.

5. Import Project Folders in the order in which they appear in Bangkok’s Repository. For

example, if you have exported 3 Folders “/RELEASE2/EXIMBILL/STG_TO_TRG” without their

child components, you need to import “RELEASE2” before “EXIMBILL” and so forth. Make

sure to set the import mode to:

a. “Synonym Mode INSERT” if your Folder does not exist in the new repository

b. “Synonym Mode UPDATE” if your Folder already exists in the new repository

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Folder already exists.

6. Import your Knowledge Modules as follow:

Import Export Best Practices for SCB 17/17

a. Locate the Knowledge Module XML files on your file system and rename their prefix to

“TRT_” instead of “KM_”

b. Right-click on any Folder of your Project and select “Import…> Import Procedure..”

menu

c. Select “Synonym Mode INSERT_UPDATE”,

d. go to the directory where your KMs are stored on your file system

e. Select your KMs, then click OK.

7. Import your Variables, Sequences and Functions in “Synonym Mode INSERT_UPDATE”

8. Finally, import your Interfaces in “Synonym Mode INSERT_UPDATE”