The whole main idea of this logic is to capture any created , regenerated and re process any failed scenarios while importing . Its just a simple Process to do the Export and Import of Scenarios from One Work Repository to another Work Repository with out manually doing any of the steps .
The Logic  in short, works like this , a small procedure (ODI_SCEN)  captures the created ,regenerated scenarios from the Source Work Repository and automatically determines whether the scenarios needs to imported or not . If so , using jython and startcmd , respective scenario is exported and later at the Target Work Repository  the scenarios are imported and finally the Exported Scenarios.

Presently this method is deployed to work with single Master and Multiple work repository.

I have developed this idea keeping into mind that after the development is done , the export and import of scenario to next stage is high since lots of code fixes ,updates and issues are resolved at this stage and so scenarios are regenerated and send to the next stage and for an administrator and even developer this is lots of work.

The below screenshot shows the big picture of the process and the are steps are as follows.

1. A table is created  called ODI_SCEN , for my example i have created into ODI_TEMP ( my work area) which keeps history of all scenarios processed  and even that got deleted .

2. An ODI procedure (ODI_SCEN) is run which collects all the data required  such what scenarios are being inserted and what  are updated  and what got deleted.  The information is collected from the previous work repository table (SNP_SCEN) tables and then stored into ODI_SCEN.

3. The variable V_REM_SCEN  is used to calculate if there is any scenarios to be run , else end the process.

4. V_GET_SCEN is used to retrieve the scenario name and version .

5. Export Scen is a jython code which gets the scenario name , version and uses  startcmd to export the XML from the previous(development) repository.

6. IMPORT_SCEN  is used to import the scen from the location where the export is stored and import in Insert_Update mode.

7. If the above step is successful the Import Flag in ODI_SCEN is updated with SUCCESS  else with SKIP.

8. V_REM_SCEN is called to check if there are any other scenarios to be processed or not and the loop is processed till all the scenarios are Imported and once done  finally SKIP_TO_FAILURE is called to mark all the skip to Failure so that these scenarios can be processed again in the next run .

9. Finally delete all the exported scenarios.

PROCEDURE ODI_SCEN

The first step is to create an ODI procedure called ODI_SCEN  and create the following options

• PROCESS
• SUCCESS
• SKIP
• SKIP_TO_FAILURE

of the type ‘ CHECK BOX ‘ and default value as ‘ NO’

and now created the following command and in the given order and required option as shown below in detailed.

CREATE TABLE

First step is to create a temporary table where we are going to record and analyze which scenarios to be imported and and which to skip . Please change the schema and  table name as required .

CREATE TABLE ODI_TEMP.ODI_SCEN
 (
    ROW_ID       NUMBER(10)  DEFAULT 1,
    SCEN_NAME    VARCHAR2(50),
    SCEN_VERSION VARCHAR2(10),
    SCEN_TYPE    VARCHAR2(20),
    SCEN_INSERT_DATE DATE,
    PROCESSED_DATE DATE,
    IMPORT_FLAG VARCHAR(50),
    CONSTRAINT ROW_ID_PK PRIMARY KEY (ROW_ID)
  )

The SCEN_NAME stores the scenario name , SCEN_VERSION  stores the scenario_version , SCEN_TYPE the object type such as variable  , procedure , Interface , SCEN_INSERT_DATE  stores the date when the scenarios gets created and PROCESSED_DATE stores the date when the scenario gets inserted into ODI_SCEN table .
UPDATE

Here this query checks to see if already existing scenarios have been regenerated and if so capture them so that we can import them .

UPDATE ODI_TEMP.ODI_SCEN
SET
  (
    PROCESSED_DATE,
    SCEN_INSERT_DATE,
    IMPORT_FLAG
  )
  =
  (SELECT SYSDATE,
    SNP_SCEN.LAST_DATE,''
  FROM ODI_WORK.SNP_SCEN ,
    ODI_TEMP.ODI_SCEN
  WHERE SNP_SCEN.SCEN_NAME       =ODI_SCEN.SCEN_NAME
  AND SNP_SCEN.SCEN_VERSION      =ODI_SCEN.SCEN_VERSION
  AND ODI_SCEN.SCEN_INSERT_DATE != SNP_SCEN.LAST_DATE
   )
   WHERE (SCEN_NAME,SCEN_VERSION,ODI_SCEN.SCEN_INSERT_DATE) NOT IN
  ( SELECT SCEN_NAME,SCEN_VERSION,SNP_SCEN.LAST_DATE FROM ODI_WORK.SNP_SCEN
  )

INSERT

Here this query checks for newly created scenarios and capture them into the table and import them accordingly

DECLARE
  V_COUNT      NUMBER(5);
  V_MAX_ROW_ID NUMBER(5);
BEGIN
  SELECT COUNT(*)INTO V_COUNT
  FROM ODI_WORK.SNP_SCEN
  WHERE ( SCEN_NAME, SCEN_VERSION, LAST_DATE) NOT IN
    ( SELECT SCEN_NAME , SCEN_VERSION ,SCEN_INSERT_DATE
	FROM ODI_TEMP.ODI_SCEN
    );
  SELECT CASE WHEN MAX(ROW_ID)>1 THEN MAX(ROW_ID) ELSE 0 END
	INTO V_MAX_ROW_ID FROM ODI_TEMP.ODI_SCEN ;
  WHILE (V_COUNT>0) LOOP
  INSERT INTO ODI_TEMP.ODI_SCEN
  SELECT V_MAX_ROW_ID+ROWNUM,
    SCEN_NAME ,
    SCEN_VERSION ,
    CASE
      WHEN I_POP IS NOT NULL
      THEN 'INTERFACE'
      WHEN I_PACKAGE IS NOT NULL
      THEN 'PACKAGE'
      WHEN I_TRT IS NOT NULL
      THEN 'PROCEDURE'
      WHEN I_VAR IS NOT NULL
      THEN 'VARIABLE'
    END,
    LAST_DATE,
    SYSDATE,
   ''
  FROM ODI_WORK.SNP_SCEN
  WHERE ( SCEN_NAME, SCEN_VERSION, LAST_DATE) NOT IN
    (SELECT SCEN_NAME , SCEN_VERSION ,SCEN_INSERT_DATE
	FROM ODI_TEMP.ODI_SCEN) ;
    V_COUNT := V_COUNT -1 ;
    COMMIT;
END LOOP;
END;

MARK FOR DELETION

some time few scenarios are dropped or renamed to a newer scenario  and this way we capture  the dropped scenarios  and marked with deleted flag. Here there is no process to drop the scenarios already existing in the target repository , which have to carried out manually.

UPDATE ODI_TEMP.ODI_SCEN SET IMPORT_FLAG='DELETED'
WHERE
SCEN_INSERT_DATE IS NULL AND
PROCESSED_DATE IS NULL

IGNORE AUTOMATED SCEN

This step is to ignore to capture the main scenario through which all the automated action of export and import is carried out.

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='DONT PROCESS'
WHERE SCEN_NAME='AUTOMATE_ODI_SCEN'

MARK FOR SUCCESS

This query is used to mark the Import_Flag to Success if the Import is successful

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='SUCCESS'
WHERE ROW_ID IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND ROWNUM =1
 AND SCEN_INSERT_DATE IS NOT NULL
  AND (IMPORT_FLAG IS NULL
   OR IMPORT_FLAG='FAILURE')
)

MARK_SKIP

This query is used to mark the Import_Flag to SKIP if the Import is failed

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='SKIP'
WHERE ROW_ID IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND ROWNUM =1
  AND SCEN_INSERT_DATE IS NOT NULL
  AND (IMPORT_FLAG IS NULL OR IMPORT_FLAG='FAILURE')
)

SKIP_TO_FAILURE

This query update all the skipped scenarios import flag to failure ,reason being that since apart being null i am also reprocessing failed scenarios , if in the above step i use import flag as failure it will keep on processing the same failed record  and it goes into an infinite loop .

UPDATE ODI_TEMP.ODI_SCEN
SET FLAG='FAILURE'
WHERE FLAG='SKIP'

V_REM_SCEN

this query is used to check for the remaining number of records to be processed.

SELECT COUNT(*)
FROM ODI_TEMP.ODI_SCEN
WHERE TRUNC(SYSDATE)  =TRUNC(ODI_SCEN.PROCESSED_DATE)
AND SCEN_INSERT_DATE IS NOT NULL
AND (IMPORT_FLAG     IS NULL
OR IMPORT_FLAG        ='FAILURE')

V_GET_SCEN

this query is used to retrieve the scenario name and other detail so we can Export and Import scenarios

SELECT 'SCEN_'||SCEN_NAME||SCEN_VERSION||'.XML'
FROM ODI_TEMP.ODI_SCEN
WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
AND ROW_ID                         IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND( IMPORT_FLAG                   IS NULL
  OR IMPORT_FLAG                      ='FAILURE')
  )

STARTCMD

ODI Integrator tools can be called from the command or terminal using startcmd.bat or startcmd.sh , in the below step using the jython code and startcmd,  i am exporting the scenario .The difference between the UNIX and WINDOWS is just calling the right startcmd.sh or startcmd.bat depending on the environment.

Note : Make sure the odiparams file is updated with source Master and Work repository details and startcmd is calling the right odiparams file, else you will encounter error in this step.

If the agent is based on  Unix ,  i would recommend to use the Unix drive for temporary storage of Exported scenarios as you may encounter slow performance while using local agent.

Unix

——–

import os
scen='#V_GET_SCEN'
scen_name=scen[5:-7]
scen_ver=scen[-7:-4]
file_name='/home/oracle/odi_scen_dmp/#V_GET_SCEN'
odiscen="./startcmd.sh OdiExportScen -SCEN_NAME="+scen_name+"
-SCEN_VERSION="+scen_ver+" -FILE_NAME="+file_name
if os.system(odiscen) <> 0:
    raise odiscen

Windows

————–

import os
scen='#V_GET_SCEN'
scen_name=scen[5:-7]
scen_ver=scen[-7:-4]
file_name='C:/ODI_SCEN_DMP/#V_GET_SCEN'
odiscen="startcmd.bat OdiExportScen -SCEN_NAME="+scen_name+"
-SCEN_VERSION="+scen_ver+" -FILE_NAME="+file_name
if os.system(odiscen) <> 0:
    raise odiscen

Steps to create the above Process Flow.

———————————————–

Once we have build the Procedure , Variables . Now we are ready to integrate into one smart Solution.

Step 1

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it as ‘PROCESS_SCEN’ . Here we are going to collect the detail of list of Scenarios to be Processed.

Step 2

Drag the Variable V_REM_SCEN in refresh Mode.

Step 3

Evaluate the variable V_REM_SCEN in evaluate mode where > 0 and when true then connect to V_REM_SCEN else End the process.

Here we are checking whether we have any new or Failed scenarios to be processed else we exit the Process

Step 4

Drag the Variable V_GET_SCEN in refresh Mode.

Step 5

Drag and Drop the Procedure StartCMD .This ODI Procedure will go the required Work Repository and fetch the required Scenario to be imported and call  the step name as ‘ EXPORT_SCEN’

Step 6

Use the OdiImportScen and point to the File Location Specified in StartCMD and use the Insert/Update Mode .

Step 7

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SUCCESS. If the Import is successful then ODI will update the ODI_SCEN table with SUCCESS Flag.

Step 8

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SKIP . If the OdiImportScen fails due to some reason it will update the table with the Flag Skip ,so that the particular scenario is skipped for this round of process.

Step 9

Drag the Variable V_REM_SCEN in refresh Mode.

Step 10

Evaluate the variable V_REM_SCEN in evaluate mode where = 0 and when true then connect to SKIP_TO_FAILURE else call V_GET_SCEN

Step 11

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SKIP_TO_FAILURE. Here i am changing the Flag from SKIP to FAILURE so that the failed scenarios can be processed again in future run .

Step 12

Delete the Scenarios exported using OdiFileDelete

Step 13

Finally connect the Variables and Procedure in this way.

Note: You might require to use DBLINK to read Work repository table across repositories.

Lets test the above process on various scenarios.

First import the ‘ AUTOMATE_ODI_SCEN ‘

Scenario – Creating a New Scenario in my source repository.

I  am creating a new scenario called SRC_AGE_GROUP and lets see if this comes through

and it works

Scenario –  Deleting a scenario

For this example i am deleting the above scenario

Scenario – Regenerated  EMPLOYEES  and created a new scenario – REGIONS 002

Please find the XML required and codes can be modified accordingly .

PACKAGE AUTOMATE_ODI_SCEN

PROCEDURE ODI_SCEN

PROCEDURE STARTCMD

VARIABLE V_REM_SCEN

VARIABLE V_GET_SCEN