The repositories are database schemas which store all the information regarding the tool and it’s operations. More specifically, anything done from within ODI is stored there. The only exception, are the installation files that are used to run and configure the application itself. Each ODI repository type consists of different table structure and contains different kind of information.
There are two types of repositories:
- Master Repository – Contains anything that is related to servers layout, security, environments and technologies. The data contained in this repository is, mostly, static and will be going through minimal changes. Usually, the changes are configuration of new server connections.
- Work Repository – Operational repository, which contains very dynamic information about any change that has been made, either by the client or the server.
Each work repository is connected to one master repository only.
The work repository consists of two sub-types:- Execution – This repository stores only the scenarios and run-time related information, this repository is strictly used for executing generated scenarios. Usually, used for production and test environments. When working with an environment that is created as execution sub-type, using the designer is restricted.
- Development – This is the development repository which contains all the information an Execution repository does, but in addition, it also contains the meta data and the source code, in other words, all the designer objects, which are used when developing with ODI.
Although, the repositories are for ODI internal use, there are documentations available and they can be used as centralized location for retrieving information regarding statistics, monitoring and pretty much anything that you would like. The important thing is not to miss use this option, use only when necessary and the goal cannot be achieved using standard ODI capabilities.
It seems, that ODI 11g has a little more integrated support for such a use of the repositories. One indication for this is the “WORKREP” parameter that can be passed to odiRef.getJDBCConnection() to retrieve the work repository JDBC connection object and use it to query the work repository directly. This is different from 10g, because there you would need manually to set up the physical and logical schemes, just like any other source or destination, since the odiRef.getJDBCConnection() accepts only two parameters: “SRC” and “DEST”.
Of course, the way to use such a connection is in read-only mode, the repositories are part of the application and used internally. There should be no modification made to the data manually, if someone decides to alter the data on their own anyway, he might be facing serious problems later on. If still you are facing an issue that requires to alter the data at the repository manually and you cannot find another way around it, then I would advise to get a code review from the official support.
Another important thing about the repositories is to know where each object is stored. Here is a table describing how ODI stores the objects behind the scenes:
Master | Work | Work sub-type | |
Designer | Versions and solutions | Everything except versions and solutions | Development only |
Operator | Versions and solutions | Everything except versions and solutions | Development and Execution |
Topology | All | N/A | — |
Security manager | All | N/A | — |
For conclusion, the repositories store all the information used by ODI, getting familiar with the structure and the information which can be extracted from there is a must for anyone, who uses Oracle Data Integrator for a little more, than just mapping a few columns. This applies especially, for 10g users.
An example of when knowledge about the repositories may come into play.
Hi Sergey,
I used to work at Oracle and we may know a few people in common. I’m very interested in this new getJDBCConnection() function. Can we talk about this today?
Sincerely,
Chris Rothermel
Hi Sergey,
Here’s more detail on my question.
https://forums.oracle.com/forums/message.jspa?messageID=10440481#10440481
Thanks so much for blogging about this.
-Chris
Hi Chris,
I am glad you find this post interesting, thank you.
The getJDBCConnection(), in my opinion, is intended to allow custom code to use the same connection as ODI does. This way, the connection and session management remains at the ODI level (Topology).
As far as I am aware, ODI Variables are internal, there is no native way to modify them at run time outside of ODI.
I too query the repository, there are two ways I consider doing it are fine:
Each solution may be better suited for you, depending on your requirements and technology. The later was my recent choice for one of the things I have worked on lately.
Hope I have helped,
Sergey.
Hi Sergey,
Thanks for writing back. I have been using the logical and physical schemas but there are limitations and it becomes overly complex.
Thanks to your post on the new functionality of pointing out the new ‘WORKREP’ parameter. With it I’m now able to write some Jython code to pull in the values from ‘WORKREP’ regardless of topology settings. Once that is done I write it to the temporary memory DB and then read the values into my ODI variables.
Your blog and specifically the ‘WORKREP’ parameter is the key to this solution. It took me most of the day, but I got it working.
And thanks for writing back so quickly!
-Chris
Hi Chris,
Thank you for the kind words and I am really glad that my post has helped you.
I think it would be better to avoid using Jython with In-Memory Engine in this case.
If the DB Link does not work for you as well and you are writing code, then just integrate Jython and the ODI set variable step using the < @ @> tags.
Hi,
I have a work repository of type “development”. Is there any way to change it “Execution” Type.
Hello Naveen,
I am not familiar with such ability built in.
But this can be easily achieved by creating a new execution work repository, exporting all scenarios from dev repository and importing them into the new exec repository. Be sure to create the new repository with different ID.
Hope this helps.
Hi Bahchi,
I found your blog more interesting and providing a sigh-of-releif. I was wondering whether the ODi code is being stored in the database (to which the ODI is connected). Your blog made it clear that it is.
Further, I am still strugglingg to get the exact entity (table-probably some of the SNP table) where the codebase is stores.
It would be great if you can help me with that.
Thanks and Regards,
Sanket Udare
Hello Sanket,
Really sorry for the late response, was very busy lately and missed the comment.
The code is stored in the DB, in the snp_exp_txt table, along side with other texts.
Hope this helps, even if comes late.
Sergey.
Sergey,
Your comment “Be sure to create the new repository with different ID.” Can you explain the reasoning behind this requirement?
Hi Tom,
The main reason is that it will keep the export/import process between repositories conflict free. Otherwise, you might be replacing not wanted elements of the project or getting constraint violated issues while importing or even later when just working in the environment.
It is an importnat point, you can read more about it here: http://docs.oracle.com/cd/E17904_01/integrate.1111/e12643/export_import.htm
Thanks.
Hi Sergey,
it’s very useful blog can you help me how i will query the data of the work repository using Sql
queries.
which are important tables to access the job information.
Thanks and Regards,
Narendra Belkar