Increase Scenario Internal id Sequence Using ODI SDK

Increase scenario internal id sequence using ODI SDK:

In this post I will be demonstrating ODI SDK code that can be used to increase the internal id sequence in ODI 11g.  Now the question is, in what scenario we should implement this code? Well recently we had some issue with our dev environment. Unfortunately we had no daily backup of our repository. So when we restored it to couple of months back we started facing id clash issue. To be more specific when we create a new scenario in our dev repository, there will be another scenario with the same internal id in production. That means when you will promote the dev version to production it will replace the existing scenario and support team will be breaking their head thinking, someone has deleted it.

So now either you have to create the same scenario again and again unless you get an unique number or you need to import it in duplicate mode in production. Importing duplicate mode is not a good idea so I thought to write this piece of code that can create and delete a scenario multiple times in a loop. Since sequence number never goes back it will fulfill my requirement.

Here is the code for your reference. Do let me know if you have got some other ideas as well.


* Author: Bhabani Mahapatra
* Website: http:\\
* Created on Nov 21 2017
* Purpose: Increase the scenario sequence using API
* NB: Executing API is dangerous unless you understand how it works.
* */

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.ITransactionStatus;
import oracle.odi.domain.project.OdiPackage;
import oracle.odi.domain.project.finder.IOdiPackageFinder;
import oracle.odi.domain.runtime.scenario.OdiScenario;
import oracle.odi.domain.runtime.scenario.Tag;
import oracle.odi.domain.runtime.scenario.finder.IOdiScenarioFinder;
import oracle.odi.generation.IOdiScenarioGenerator;

public class DropAndCreateODIScenario {

public static void main(String[] args) {

String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
String Driver = "oracle.jdbc.OracleDriver";
String Master_User = "odimaster";
String Master_Pass = "odi";
String WorkRep = "WORKREP34";
String Odi_User = "SUPERVISOR";
String Odi_Pass = "sunopsis";

//create instances for master and work
//authenticate yourself
MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(Url, Driver, Master_User,
Master_Pass.toCharArray(), new PoolingAttributes());
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(WorkRep, new PoolingAttributes());
OdiInstance odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo));
Authentication auth = odiInstance.getSecurityManager().createAuthentication(Odi_User, Odi_Pass.toCharArray());
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

//run the loop for delete and create scenario. number of loops will increase scenario id in multiple of 1000
for(int i=1;i<2;i++) {
System.out.println("Deleting scenario for loop " +i);
OdiScenario scen = ((IOdiScenarioFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiScenario.class)).findByTag(new Tag("SCN_TEST_BHABANI_FOR_REPOSITORY_ID", "001"));
//create scenario
try {
for (oracle.odi.domain.project.OdiPackage odiPackageCollection : ((IOdiPackageFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiPackage.class)).findByName("TEST_BHABANI_FOR_REPOSITORY_ID", "DATAMIGRATION","Test_Dev"))
System.out.println("Generating Scenario for loop " + i);
IOdiScenarioGenerator sdkIntScenario = new OdiScenarioGeneratorImpl(odiInstance);
String odiScenarioName = "SCN_" + odiPackageCollection.getName();
sdkIntScenario.generateScenario(odiPackageCollection, odiScenarioName, "001");
} catch (Exception e) {
System.out.println("exception " + e.getMessage());

System.out.println("Scenario generate Completed");


About the author

Bhabani( - Currently Bhabani is working as Technical Lead Product Development at Harman International. He has good expertise on Oracle, ODI, Pervasive Data Integrator, MSBI, Talend, Cloud Integration (AWS, GCP, Azure) , Map-R and Apache Airflow. He is also contributing in ODI-OTN forum for last 7 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

Leave a reply


Are you a human? *