Migrating OWB to ODI: Groovy script to update LKM DB-Link option value

OWB staging mappings in general use a INSERT-SELECT-FROM statement over a DB-link. When this is migrated to ODI we get a similar mapping which uses an LKM with a select over a DB-link and an IKM which generates an insert statement with an APPEND hint. All just the way I want it. When I scheduled my mappings in a Load Plan in parallel funny things started to happen. Some mappings would run, others would fail with ORA-02019: connection description for remote database not found.
It turns out that each mapping generates a create and drop database link statement with the same name. When they are run in parallel the DB-link is created and dropped multiple times. There is a simple solution: setting the SOURCE_ACCESS_DB_LINK option for LKM. If we now create the link before any mappings are run, ODI will not generate the DB-link code, but instead use the pre-defined one.
I did not want to modify all my mappings by hand so I wrote a groovy script to do it for me.

This script works for ODI 12.1.3. In 11g the structure of the interface is slightly different so this solution won’t work.

import oracle.odi.core.OdiInstance
import oracle.odi.core.config.MasterRepositoryDbInfo
import oracle.odi.core.config.OdiInstanceConfig
import oracle.odi.core.config.PoolingAttributes
import oracle.odi.core.config.WorkRepositoryDbInfo
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition
import oracle.odi.domain.mapping.Mapping
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.project.OdiFolder
import oracle.odi.domain.project.OdiPackage
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.project.finder.IOdiPackageFinder

/* --------
Begin update section
Replace the follwing values with your connectivity information.
Alternatively, you can use ODI substitution APIs to fill in these parameters dynamically
----------- */

def url = "jdbc:oracle:thin:@odi1.localdomain:1521:<dbservice>" /*Master Repository: JDBC URL */
def driver = "oracle.jdbc.OracleDriver" /*Master Repository: JDBC driver */
def schema = "DEV_ODI_REPO" /*Master Repository: Database user for schema access*/
def schemapwd = "******" /*Master Repository JDBC URL */
def workrep = "WORKREP" /*Name of the Work Repository */
def odiuser = "SUPERVISOR" /* ODI User name used to connect to the repositories */
def odiuserpwd = "******" /* ODI User password to connect to the repositories */

def projectCode = "<projectcode>"
def dblinkName = "<dblink>"

/* --------
End of update section
----------- */

// Repository and ODI Instance
def masterInfo = new MasterRepositoryDbInfo(url, driver, schema, schemapwd.toCharArray(), new PoolingAttributes())
def workInfo = new WorkRepositoryDbInfo(workrep, new PoolingAttributes())
def odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo))

// Authentication
def auth = odiInstance.securityManager.createAuthentication(odiuser, odiuserpwd.toCharArray())
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)

// Transaction Instance
def txnDef = new DefaultTransactionDefinition()
def tm = odiInstance.getTransactionManager()
def tme = odiInstance.getTransactionalEntityManager()
def txnStatus = tm.getTransaction(txnDef)

/* Shortcuts to some finder classes*/
def fm = ((IMappingFinder) tme.getFinder(Mapping.class))         // Find Mapping

try {
    def mappingList = fm.findAll().findAll { w -> w.getProject().getCode() == projectCode && w.getName().contains('STG') }

    mappingList.each { m ->
        println("- " + m.getName())

        m.getPhysicalDesigns().each { p ->
            p.getPhysicalNodes().findAll { a -> a.getLKMName() == 'LKM Oracle to Oracle Pull (DB Link)' }.each
                    { n ->
                        println("\t Original value: " + n.getLKMOptionValue('SOURCE_ACCESS_DB_LINK').getOptionValue())
                        n.getLKMOptionValue('SOURCE_ACCESS_DB_LINK').setValue(dblinkName)
                    }
        }
    }
    // Commit transaction, Close Authentication and ODI Instance
    tm.commit(txnStatus)
    auth.close()
    odiInstance.close()
}
catch (Exception e) {

    // Commit transaction, Close Authentication and ODI Instance in Exception Block
    tm.rollback(txnStatus)
    auth.close()
    odiInstance.close()
    println(e)
}