Is thera a way to find forced execution context on datastore objects, somewhere in ODI metadata database?

Question

I have a ODI 12c project with 30 mappings. I need to check if every "Component context" on every datastore object (source or target) is set to "Execution context" (not forced). Is there a way to achive this by querying ODI underlying database so I don't have to do this manually, and to avoid possible mistakes ?

I have a list of ODI 12c Repository tables and comments on table columns which I got from the Oracle support website, and after hours of digging through database I still can't see this information stored in any table.

My package is located in SNP_PACKAGE, SNP_MAPPING has info about mapping , and SNP_MAP_COMP describes objects in mapping. I have searched through many different tables as well.

Answer 1

Here is also a query to retrieve this information directly from database.

-- Forced Contexts on Datastores in Mapping

SELECT MAPP.NAME MAP_NAME, MAPP_COMP.NAME DATASTORE_NAME, 
MAPP_REF.QUALIFIED_NAME FORCE_CONTEXT
FROM SNP_MAPPING MAPP
   INNER JOIN SNP_MAP_REF MAPP_REF 
       ON MAPP_REF.I_OWNER_MAPPING = MAPP.I_MAPPING
    INNER JOIN SNP_MAP_PROP MAPP_PROP  
       ON MAPP_REF.I_MAP_REF = MAPP_PROP.I_PROP_XREF_VALUE
   INNER JOIN ODIW12.SNP_MAP_COMP MAPP_COMP 
       ON MAPP_COMP.I_MAP_COMP = MAPP_PROP.I_MAP_COMP
WHERE
   MAPP_REF.ADAPTER_INTF_TYPE = 'IContext' 
   and MAPP.NAME like %yourMapping%

Answer 2

A bit late but for anyone else looking

Messing about the tables is a no-no. APIs are better. Specially if you are to modify anything. https://docs.oracle.com/en/middleware/data-integrator/12.2.1.3/odija/index.html

Run the following groovy script in ODI (Tools/Groovy/New Script). Should be simple enough to modify. Using the SDK gets a lot easier if you manage to set up a complete development env in IntelliJ or another Java IDE. Groovy in ODI opens up a whole new world.

//Created by DI Studio
import oracle.odi.domain.mapping.Mapping
import oracle.odi.domain.mapping.finder.IMappingFinder

tme = odiInstance.getTransactionalEntityManager()

IMappingFinder mapf = (IMappingFinder) tme.getFinder(Mapping.class)
Collection<Mapping> mappings = mapf.findByProject("PROJECT","FOLDER")
println("Found ${mappings.size()} mappings")

mappings.each { map ->
    map.physicalDesigns.each{ phys ->
        phys.physicalNodes.each{ node ->
            println("${map.project.name}...${map.parentFolder.parentFolder?.name}.${map.parentFolder.name}.${map.name}.${phys.name}.${node.name}.defaultContext=${(node.context.defaultContext) ? "default" : node.context.name}")
        }
    }
}

It prints default or the set (forced) context. Seems forced context has been deprecated in 12c. Physical.node.context.defaultContext seems to mirror Component Context (Forced) in ODI Studio 12.2.1.3. https://docs.oracle.com/en/middleware/data-integrator/12.2.1.3/odija/index.html

Update 2019-12-20 - including getExecutionContextName The following script lists in a hierarchical manner and maybe easier to read the code. Not sure if you get what you are originally was after without having mapping with your exact setup.

//Created by DI Studio
import oracle.odi.domain.mapping.Mapping
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.mapping.component.DatastoreComponent

tme = odiInstance.getTransactionalEntityManager()

String project = "PROJECT"
String parentFolder = "PARENT_FOLDER"

IMappingFinder mapf = (IMappingFinder) tme.getFinder(Mapping.class)
Collection<Mapping> mappings = mapf.findByProject(project, parentFolder)
println("Found ${mappings.size()} mappings")

println "Project: ${project}"
mappings.each { map ->
    println "\tMapping: ..${map.parentFolder.parentFolder?.name}/${map.parentFolder.name}/${map.name}"
    map.physicalDesigns.each{ phys ->
        println "\t\tPhysical: ${phys.name}"
        phys.physicalNodes.each{ node ->
            println "\t\t\tNode: ${node.name}"
            println "\t\t\t\tdefaultContext: ${(node.context.defaultContext)}" 
            println "\t\t\t\tNode context name: ${node.context.name}"
            println "\t\t\t\tDatastoreComponent ExecutionContextName: ${DatastoreComponent.getDatastoreComponent(node)?.getExecutionContextName(node).toString()}"
        }
    }
}

Answer 3

Below is a list of some tables and columns that might hold the value you are looking for.

These tables and columns are from ODI 12.1.2, depending on the exact ODI version you are using, the structure could be a little different.