Execution Variables trick for old versions of ODI


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:

blog1

blog2

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:

blog3

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

blog4

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:

blog5

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

blog6

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

blog7

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:

blog8

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!

Advertisements

4 Responses to “Execution Variables trick for old versions of ODI”

  1. Very nice post!

  2. Anmol Kaushik Says:

    I am slowly becoming a fan of this blog. What a great idea?!

    I have some ideas of my own. Can I share them using this blog?

  3. I am falling in love with this blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: