DEV EPM

Hi all, it has being a while that we don’t post! Busy days you know…. Anyway, let’s see what we got today.

The situation that I’m about to describe often happens in large/old ODI projects. Imagine the following: you received a task to change an ODI component that was created one year ago by someone else that is not even in the company anymore. The code is running fine in PROD and the business want a small fix to it. You open the ODI package and it contains a lot of interfaces, procedures, variables, etc. You need to change the code in one single interface, which seems very simple. You change it, save it, generate a new scenario and move it to PROD. When it gets there, the job fails due to an error in another interface that you did not touch! You start to troubleshoot and figure out that someone else changed something in DEV, saved it, but did not move the code to PROD. Unfortunately this ”unwanted” code change was included by you when you generated your scenario and now the mess is already created. If you already passed through this situation, than this post may help you.

Code versioning and code migration processes in general are things that everybody knows that are necessary, but sometimes they are overlooked by the companies because people think they are too complicated or does not work very well. ODI is a big example of this, since its native versioning system is not very intuitive and most of the times does not work in the way that we want to. There are companies out there that even build their own code versioning system (outside of ODI) to manage ODI code versions. I dare to say that most of the companies don’t even have any kind of code versioning or formal code migration process for ODI at all, which causes some big headaches on similar situations as the one that I just described.

The technique that I’ll explain here is not about code versioning itself. I’ll describe something that we may use when we do not have any other way to guarantee that the scenario that we are generating was not changed by someone else during a time period. Just to let you know, all the following SQL was done in ODI 11.1.1.9 version.

Let’s begin with the basics. Everything that you create in ODI is stored in SNP tables in its WORK and MASTER repositories. For this post we will focus in two main tables from the WORK repository:

• SNP_SCEN: contains the basic information about the scenarios that exists in that WORK repository (like name, version, creation date and so on);
• SNP_SCEN_TASK: the “main” scenario table that contains all the steps/tasks that are performed by a scenario. You may query this table and see exactly which tasks (like SQL commands, variables, flows) that scenario will perform when you run it in Operator;

So now let’s get back to our problem. There is a scenario that is running fine in Production for one year now (let’s say it calls ODI_SCENARIO Version 1_00_00) and this scenario is also in Development. I’ll make a change in only one interface (I’ll add a simple SUBSTR in a column named PACK_SLIP) of this scenario in Development and create a new version of it (ODI_SCENARIO Version 2_00_00). How do I guarantee that my code change was the only thing that changed in this new scenario and that it does not contain any other code from other developers? The answer lies on the SNP_SCEN_TASK table.

If you go to ODI WORK repository in Production, you may run the following query to get all the steps that the scenario is currently executing on its 1_00_00 Version:

SELECT NNO,
SCEN_TASK_NO,
TASK_TYPE,
TASK_NAME1,
TASK_NAME2,
TASK_NAME3,
EXE_CHANNEL,
DEF_CONTEXT_CODE,
DEF_LSCHEMA_NAME,
DEF_CONNECT_ID,
DEF_IND_COMMIT,
DEF_ISOL_LEVEL,
DEF_PLAN_COMP,
COL_CONTEXT_CODE,
COL_LSCHEMA_NAME,
COL_CONNECT_ID,
COL_ISOL_LEVEL,
COL_IND_COMMIT,
COL_PLAN_COMP,
ORD_TRT,
IND_ERR,
LOG_LEV_DET,
IND_LOG_NB,
DEF_TECH_INT_NAME,
COL_TECH_INT_NAME,
IND_LOG_METHOD,
COL_TXT,
COL_IND_ENC,
COL_ENC_KEY,
DEF_TXT,
DEF_IND_ENC,
DEF_ENC_KEY,
IND_LOG_FINAL_CMD
FROM SNP_SCEN_TASK
WHERE SCEN_NO IN
(SELECT SCEN_NO
FROM SNP_SCEN
WHERE SCEN_NAME = 'ODI_SCENARIO'
AND SCEN_VERSION = '1_00_00')
ORDER BY SCEN_TASK_NO,NNO;

There are a lot of important columns in this table that can give you a lot of valuable information. However, COL_TXT and DEF_TXT are generally the most important ones since they contain the code that is generated in the “Source and Target tabs” inside the procedures and interfaces. After you run this SQL in Production environment, you may export it to whatever you like. In this example here, I’ll export it as “Text” using Oracle SQL Developer as the following (Right click on any row and select “Export”):

Save it somewhere in your computer:

The result will be something like this:

Now let’s run the SQL in the Development ODI WORK repository. The only thing that we will change now is our filter that will go from SCEN_VERSION = ‘1_00_00’ to SCEN_VERSION = ‘2_00_00’, which is the new scenario version that we just generated. Do the same steps as the Production SQL and you should end up with something like this:

Now you need to compare both codes. I like to go simple and use Notepad ++ with “Compare” plugin. You may use any other tool for comparing txt files (Beyond Compare is awesome as well). In Notepad ++ you just need to open both files, click on the Production file and “Set the First Compare”, then click on de Development file and “Compare”.

You will have something similar to this when you compare:

The “Compare NavBar” shows a lot of differences, way more than the one that I just did. However, we need to analyze it calmly to verify what do they really mean. You may navigate thought the changes using the “Next” button in the tool bar.

There will be some blocks of code that contains “similar differences” due to the nature of ODI. For example, when you change one single thing in one column of one interface, it will be reflected in several steps within the Knowledge Module (in C$/I$/E$ creation for example). This is one example of it:

This change is saying that we changed the order of PACK_SLIP column (which was the column that we added a SUBSTR command). Actually we didn’t change the order, but we changed its content. However, when ODI create its temporary tables (like C$, I$ and E$) we cannot control the order that they are going to be created as the code is generated automatically by ODI. So we don’t need to worry about this change, as it was somehow “expected”. When we click “Next”, we are going to have similar ones where the column just changed its order. Continuing further down, we will get to the place where our change occurred:

Cool, this is the place that we changed our code and it looks good. Let’s keep going to see what else has changed. Now we will get something weird. A lot of “1” changes just appeared until the end of the file (explaining why we had a lot of changes in the comparison Navigation Bar):

This “1” comes from IND_LOG_FINAL_CMD column, which identifies if the step should “Log Final Command” or not. This does not affect the code itself, but for the sake of my analyses I went to the KM to see if someone had changed this option:

My suspicious was right and someone changed this option in one of the KMs, which got reflected in a lot of places in my ODI scenario. There was no more changes in my comparison, so I could conclude that:

• PACK_SLIP changed the order in some temporary tables creation, which is ok;
• I saw my PACK_SLIP mapping change (SUBSTR) in the Development code;
• There was a change in the KM to “Log Final Command” in a specific KM step, which is also ok and does not affect the code itself;

No more differences were found between the scenarios, so I may safely deploy it to production. If someone else had changed something more critical, the compare method would have catch that and we could revert it back before moving to Production.

There are other ways for you to get and compare the codes, like if both scenarios are in the same DB you could just run two SQLs and compare them or you could export both XML scenario files and compare those, but this post here gives you a generic way that can be done in most of the cases and it is fairly easy to be used.

That’s it guys, I hope you have enjoyed!