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.
The first step is to create an ODI procedure called ODI_SCEN and create the following options
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.
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 .
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 )
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') )
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') )
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'
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')
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') )
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.
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
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.
Drag the Variable V_REM_SCEN in refresh Mode.
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
Drag the Variable V_GET_SCEN in refresh Mode.
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’
Use the OdiImportScen and point to the File Location Specified in StartCMD and use the Insert/Update Mode .
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.
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.
Drag the Variable V_REM_SCEN in refresh Mode.
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
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 .
Delete the Scenarios exported using OdiFileDelete
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 .