DEV EPM

Hi all, today I’m going to show a very simple trick that is very useful for everyone that uses ODI in version 11.1.1.5 or below. How many times you got a code to debug from Operator and you needed to keep replacing all those ODI variables for its correct values in order to troubleshoot something? If you work with ODI development probably you do it every day. Depending on the case, it can be very time consuming because you need to get all input variables, execute all SQLs from refresh variables and so on. Luckily for those on ODI 11.1.1.6 and beyond, Oracle have added the variables values to the Operator steps, so no need to go hunting variables anymore, but what about the older versions?

There are a lot of alternatives around there and I have used one in particular for a long time. It is a simple Jython procedure that has an option where I add all ODI variables that I want to show and a step with a “raise” Jython command with ignore errors selected:

It works pretty fine, when you execute it inside a package all variables values that you have added in the option will show in Operator, but it is always a pain in large projects because you may have 20 variables or so and you need to copy and paste its name one by one to the procedure’s option, but it was way better than having nothing at all.

Tired of doing this copy and paste work, a coworker (thanks Eduardo for the great idea and the SQL!) came with the follow idea: Why don’t we query ODI repository metadata to get all variables that were used in the ODI scenario and “raise” its value? Simple but great idea because it is just a matter to place the procedure anywhere in the ODI package and it will show all the variable’s values at that point.

It’s very easy to accomplish this. First you need to have a topology set to the ODI work repository, and then you need to create a procedure with two steps. First step will have the ODI work repository logical schema set in the “Command on Source” tab:

And the below Java BeanShell code written in the “Command on Target” tab:

SELECT DISTINCT VAR FROM (SELECT LEVEL, VAR_NAME, SYS_CONNECT_BY_PATH(VAR_NAME || ‘: #’ || VAR_NAME, ‘ \n ‘) AS VAR FROM (SELECT  ROWNUM AS CURR , ROWNUM -1 AS PREV , SSS.VAR_NAME , SST.DEF_TXT, SSTL.TASK_END , SSTL.TASK_BEG FROM ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” )+”.SNP_SESS_TASK SST, ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” ) + “.SNP_SESS_TASK_LOG SSTL , ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” ) + “.SNP_SESS_STEP SSS WHERE 1 = 1 AND SST.TASK_TYPE = ‘V’ AND SSS.STEP_TYPE IN (‘V’, ‘VS’, ‘VE’, ‘VD’) AND SSTL.NNO = SSS.NNO AND SSTL.NB_RUN = SSS.NB_RUN  AND SST.SESS_NO = SSTL.SESS_NO  AND SST.SESS_NO = SSS.SESS_NO  AND SST.SCEN_TASK_NO = SSTL.SCEN_TASK_NO  AND SSS.SESS_NO = ” + odiRef.getSession(“SESS_NO”) + “) CONNECT BY PREV = PRIOR CURR  START WITH CURR = 1  ORDER BY LEVEL DESC ) WHERE ROWNUM = 1

In resume, the above query goes against the ODI repository and returns one line containing all ODI variables used in the ODI scenario until that point. This query can be accomplished in several different ways (for example using listAgg Oracle’s feature in a newer database) and you may tweak it to return more valuable information if needed. With this information added to “odivars” variable, it is just a matter to “raise” it in the second step:

Let’s see how it works. Imagine the below ODI scenario:

When we execute it and take a look in ODI Operator, this is what we get in the first procedure execution:

It shows the values “123” and “A” for the first two variables. When we look at the second procedure execution we get all the three variables:

As you can see, it works with all kind of variables (refresh, set, declare, global, project only, etc) and if you want even more freedom, you may add this code inside your Knowledge modules in the very first steps, so you will know all the variables values that was used in your integration steps without the need to drag and drop a procedure to the ODI package.

That’s it folks. I hope it is useful for you. Thanks again Eduardo for the idea! See ya!

This entry was posted on February 28, 2014 at 4:06 pm and is filed under ODI, ODI Architecture with tags ODI, ODI Architecture, ODI Expert. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.