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 Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.