Archive for September, 2014

ODI Substitution Tags demystified

Posted in ODI, ODI Architecture with tags , , , , on September 16, 2014 by radk00

Hi all, I bet that all ODI developers already got confused when working with ODI substitution tags. Every time that I need to work with those, I need to repeat all my previous tests again because I forget which tag is the right one to use (even after many years working with ODI….). That’s one of the reasons why I decided to write this post, so I can read my own post in future references (memory, memory, where are you???). Also because it is nice to share our experiences with others, so they can benefit from it (at least I hope so 🙂 ) if they face similar issues.

For all those that are not familiar with it, what are ODI substitution tags? Substitution tags are those characters that you find when you open a Knowledge Module or a procedure in ODI, like these ones: <%>, <?>, <@> and <$>. Here is one example:

1

Those tags are part of ODI Substitution API and they are used to produce dynamic code based on several things like ODI variables, procedure options, data models, interface diagram and so on. Basically the Substitution API is the greatest development power in ODI (that’s why I love ODI so much), since it can produce dynamic code based in the existing information around it. Oracle has a good documentation about it here: Oracle Doc

Although Oracle talks about substitution API in their documentation, it does not state about the tags themselves and in their examples they only talk about the most common tag that is the <%>. But in reality we have four types of it, each one behaving in its own way. Basically each one of them is executed in a specific order, which affects their results in a way greater than you could imagine.

I’ll explain each one of them using a real situation that happened to me some time ago. I was working in a project that contained several different servers (ODI agents) with different Operational Systems on them. I was developing a dynamic component code that had to change its behavior based on which OS that component was going to be executed, so if the OS server was Linux I would execute one particular command and if it was Windows another command. To test in which OS the component was in, I used the following Substitution API command with the following tag (the only one that I knew back then):

<%= System.getProperty(“os.name”) %>

It didn’t work as I expected. The result of this command was always returning the OS name of the machine in which I was launching the execution (the ODI client) not the one that the command was going to be executed (the ODI agent). It puzzled me, so I started to study which options I could use… that’s when I learned about <?>, <@> and later on <$> tags.  Each one of them has a different effect in ODI code generation because each one of them has its own parse phase! This is a very simple concept and it truly affects the ways that ODI generate its dynamic code.

So let me show you what I did to test all those options. First consider that my ODI client is in a Windows 2003 OS and the ODI agent is a Windows Server 2008 R2 OS (the Linux OS was not available for me anymore, but this Windows 2008 will suffice for this example, since we just need different types of OSs). I created the following procedure:

2

It is a simple procedure with a Jython code that will “raise” the name of the OS as an error. I just marked “Ignore Errors” as well, so I could add more steps to this procedure. I did the same for all substitution tags:

3

Then you execute the procedure, making sure that it uses the ODI agent (that resides in a different OS):

4

Let’s begin with <%>. After you execute the procedure and go to “Code” tab, we have the following:

5

Very interesting and it matches my previous experience. The <%> is returning the ODI client OS which indicates that the substitution API is generating the command when it is sent to the agent, but before it gets to the agent! Obviously if we go to “Definition” tab, we will get the execution of this command, which will be “Windows 2003”:

6

Let’s see what it did using <?> substitution tag:

7

Nice! This is exactly what I previously wanted: get the ODI agent OS and decide which command was going to be executed based on that OS. So ODI is generating the code in the agent server, before the command is sent to Operator and before ODI variables are substituted (what, Operator, variables??? More on those topics in a few moments…). If we go to “Definition” tab, we will get the execution of this command, which will be “Windows Server 2008 R2”:

8

Ok, let’s see <@>:

9

Humm….. More interesting stuff and that’s why I mentioned Operator/ODI variables before. This code is being generated in the agent, but after the command appears in the Operator and after ODI variables are substituted. When we go to “Definition” tab, we will get the execution of this command, which will be “Windows Server 2008 R2” again:

10

The <@> tag is great for you to evaluate ODI variables content and depending on that variable value, do something different in your code, like the example below:

11

The interface filter in this example will change accordingly to #V_COND variable. If #V_COND is 1, then it will filter all records with BANK_ACCOUNT_TYPE = ‘1’. If #V_COND is different from 1, then it will load all records.

Those three examples perfectly show us that each tag executes in a different parse phase and each one has precedence over the other. And now you must be thinking: does it means that we can mix and match those tags and create even more dynamic code??? Oh sure yes! Here you can see great examples from Sonra guys:

Sonra Part 1

Sonra Part 2

They show us how we can create “loops” using one tag and apply that result to another tag that will get executed right after that, which give us an extreme dynamic tool for developing. I already told you that I love ODI right?

Ok, but what about <$>? I left this one for the end because this tag was added later to the game (it seems that it is available from 11.1.1.6 version on). This tag resides between <?> and <@> which give us the unique ability of generating the code in the agent server, before the command is sent to Operator but AFTER ODI variables are substituted. Ok, this is crazy stuff 🙂 Why do we need it? The key here is that ODI will contain all ODI variables already evaluated (meaning containing its correspondent refresh/set values) and the code will be displayed in Operator already parsed with the correct ODI variables logic, which makes your code much more readable for the Operators users. Besides that, having a fourth parse phase is great to do even crazier dynamic self-constructed code (long live dynamic coding!!!). Here is “the same” example using <$> in an ODI interface from Oracle ODI blog (the place where I learned about the existence of <$>):

Oracle Blog

In our OS example, the <$> result will not be different from what we got using <?>:

12

And in Definition tab:

13

That’s it folks, so in resume here is the list of substitution tags and their parsing phase order:

<%>: generates the command when it is sent to the agent, but BEFORE it gets to the agent;

<?>:  generates the code in the agent server, BEFORE the command is sent to Operator and BEFORE ODI variables are substituted

<$> (available from 11.1.1.6 on): generates the code in the agent server, BEFORE the command is sent to Operator but AFTER ODI variables are substituted

<@>: generates the code in the agent server, AFTER the command appears in the Operator and AFTER ODI variables are substituted

Hope you have enjoyed it! See you next time!

Advertisements

5 Steps to do an In-Place Migration of OWB/DB from 11.2.0.1 to 11.2.0.4

Posted in EPM, InfraStructure, Install, Migration, Oracle 11.2.0, Oracle 11.2.0.4, Oracle Database, OWB, Upgrade on September 1, 2014 by RZGiampaoli

 

Hi all, today I decided to get out of my comfort zone to talk about the in-place OWB migration and a little bit of infrastructure. I decided to do that because the follow reasons:

First, I hate infrastructure, probably because I am not good at it and in all clients that I’ve worked, this is always a headache. Some times because it is poorly done, some others because everything grows but the infra. But the worst part is:

Nobody knows Hyperion or the BI tools enougth and in the end when something bad happens, it ends up that I’ll need to fix the problem, and normally there’re two problems, the infra itself and the guys from the infra asking for evidences that the issue is from infra. Sorry guys, but this is a consultant point of view J

Second, because it took me 2 days to understand what was the OWB migration and how to do it.

Third, I searched the entire internet and did not find any complete step-by-step tutorial about it. Probably because the Migration happens to be the database migration not the OWB migration.

Before I start, let me talk a little about my infra. As I said, I hate infra and I am not good at it then please consider this post as a tentative to help people and not an absolute truth coming from an expert or something.

Because I hate infra, I tried to make mine the best it could be (or at least, the best I could make it).

The Server:

The_Server

This is a cheap machine (even in Brazil that everything is expensiveJ). I only upgraded it with 32 gb of memory and by now has only one HD with 1 Tb (I have more HD in my drawers But I’m not needing it by now).

The Virtualization Software:

For this server I installed in a 1 Gb pendrive the VMware ESXi 5.1.0. This is a good tip, the ESXi is by far (and I mean lightning years far) the best virtualization software in the market, and for a server with max 32 Gb memory, it’s for free.

The VMs:

The_VMs

I have six VMs up and running all the time in this server. Never had any performance issue, in fact they are a lot faster than the servers in some of my clients are. Of course, I have a clean installation of everything.

By the name of the machines you guys could figure out that I like old mythology J and I’ll explain the infra using it.

First we have Cerberus, the hell hound that has a zentail installed on it to protect the entrance of the hell realm. Also it’s used to external access, VPN, FTP and DNS.

Then we have Tartarus, the realm of the dead’s, and also what supports the earth (or gaia that’s the name of my extra net). This is an oracle 11g Database, because all the repositories are on it. It is the base for everything.

Also I have Niflheim, that’s almost the same thing but for the Nordics. This is an Oracle 12c database. There are Golden gates running between both databases for replication and test proporses.

After that, I have Olympus, where all the foundation, planning, eas server and reports are installed.

Then we have Pantheon, which is on top of Olympus, where OBIEE and Endeca is installed.

All these machines are running on top of Oracle unbreakable Linux OS.

In the end, we have Hyperion, which is a Windows server 2008 R2 machine where I have only Essbase and ODI agent installed. Why I did that, because both tools heavily use the file system and using windows make everything a lot easier to access, configure, and work with.

Also, I found in an old version of Essbase (11.1.1.3) that some ESSCMD commands doesn’t exists in Linux, like “deploy studio” command. I had a client that had to migrate the Essbase to windows because this and some performance issues.

However, the thing was the file system and I installed some clients in this machine this way my friends needs only my VPN and a terminal Server to access this machine and make some tests.

Enough about my infra and let us talk about the migration. First of all there’s no OWB migration. OWB is not like ODI that you need just to get/patch of a new version and update the repository.

For OWB you need to upgrade the entire database, and then you can connect using your new client (and if you use windows, you will not have the client in the same version of the repository. However, we’ll talk about this later).

Ok, sinceI do not like to have many oracle homes in my server I decided to do an “In-Place” upgrade. Upgrade is a strong word for this because it is an installation from the scratch not a patch or something.

If you have any type of Virtualization now is an excellent time to create a snapshot from your database. Thanks God I did that because he knows, I needed.

Ok, after try a lot of stuff and commands that never worked I did it in my way, and worked, then please if someone see something incredibly ugly (and maybe I’ll not know that it was an ugly thing) please let me know.

Basically for the “In-Place” installation we need to deceive Oracle installer to believe that your server has no other oracle instance. For this, you need to detach the oracle home from your inventory.xml. Oracle give us a command that I could never make it work:

On Windows:

%ORACLE_HOME%\oui\bin\setup.exe -detachHome ORACLE_HOME=D:\oracle\product\11.2.0.2\dbhome_1

On Linux:  

$ORACLE_HOME/oui/bin/runInstaller -detachHome ORACLE_HOME=11.2_ORACLE_HOME_LOCATION

I found other “Versions” of this command but I couldn’t make any of them works as well. Then I had to find my own version, which I can guarantee, it is a lot easier that this command. Rename the “inventory.xml” to anything you want. In my case this files is in this folder:

/u01/app/oraInventory/ContentsXML/

This will make the oracle installer believe that he will be the firstJ.

After that you need just to rename the dbhome folder or the version folder, anyone will do the trick.

/u01/app/oracle/product/

Renaming the folder will remove any need of backups because everything you need will be there.

Cool, after these two-steps you can install the DB software. Make sure to install only the software. The DB we will migrate later on.

I will not cover the installation here because even I did not have any issue with that, and, there are many tutorials that cover the installation in the internet. Basically you need only to click in next and remember to select software only. That is all.

Ok, after the software install we need to copy some files from the backup that we did when we renamed our previous directory.

We need to copy everything that is inside the folder “dbs” to the new “dbs” Folder. For me the path is like this:

/u01/app/oracle/product/11.2.0/db_1/dbs/

Doing that you have the initialization files from the old db in the new one.

Also copy the files in the follow folder to the respective ones in the new installation:

  • ORACLE_HOME/network/admin
  • ORACLE_HOME/hostname_dbname
  • ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname
  • ORACLE_HOME/owb/network/admin

With this, we have everything to upgrade the old db to the new version. Now we need to connect in the sqlplus as sysdba:

Go to the folder $ORACLE_HOME/rdbms/admin/ (this is the location of all sql we need to run to upgrade the repository) and then:

$ sqlplus / as sysdba

We need not to start the DB in upgrade mode. For this:

SQL> startup UPGRADE;

This will mount the database but will give access only for sysdba users. Now we just need to run the follow scripts:

Pre-Upgrade Script

SQL> spool upgrade_info.log

SQL> @utlu112i.sql

SQL> spool off

(This is a pre-upgrade script. Oracle said to copy it from the rdbms directory but I didn’t get why then.. If you have any problem please follow oracle orientations);

Upgrade Script

SQL> spool upgrade_info.log

SQL> @catupgrd.sql

SQL> spool off

(This is the upgrade script. It takes a while to run then, be patient);

Pos upgrade scripts

SQL> spool pos_upgrade_info.log

SQL> @utlu112s.sql

SQL> spool off

(This script provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed)

Recompile Invalid Objects

SQL> @utlrp.sql

(This will recompile any remaining stored PL/SQL and Java code in another session)

After this, we need just to:

SQL> shutdown immediate;

SQL> startup;

That’s it. Everything I did to upgrade the Oracle database in place. As I said, I am not a DBA and probably if a DBA read this post he will laugh, a lot, but it worked and it was the only way I found to do it.

The Client

Ok now, if you have your client in a windows machine, you will realize that oracle did not release a windows version for the OWB client 11.2.0.4. The latest client for windows is in the 11.2.0.3 version. However, Oracle said that this version is fully supported to work with the 11.2.0.4 version. You need only to:

Download the 11.2.0.3 version for windows:

Install the patch 16568042:

Change the file:

$OWB_HOME/owb/bin/admin/Preference.properties

Set:

OverrideRepositoryVersionCheck=true

OverrideRuntimeVersionCheck=true

That is it. You database is upgraded, your client is upgraded and you need only to upgrade the OWB repository. I will not cover this here because this can be found in the internet easily.

Also, if you want to change the language of your OWB client you need only to:

Open $ORACLE_HOME/ide/bin/ide.conf and add the following line to the file:

AddVMOption -Duser.language=en

I know that this is very different from what we have been posting here but it took me 2 days to make this works, and I do not want anybody else losing other 2 days to do something, that in the end was very simple. If you think of it now, for the “In-place” migration we only need to:

  1. Rename the inventory.xml file;
  2. Rename the db_home or the 11.2.0 folder;
  3. Install the new version of the software;
  4. Copy some files from the old install to the new one;
  5. Run three scripts;

That is all. I wish you guys found this useful and until next time. Thank you.