Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

ODI 11g: Unlocking Objects Without Being a Supervisor

DZone's Guide to

ODI 11g: Unlocking Objects Without Being a Supervisor

Although Oracle Data Integrator is one of the best and common ETL tools in the market, it can cause concurrency working problems caused by developer negligence.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

:Oracle Data Integrator is one of the most widely used ETL tools on the market, but from time to time, it may cause headaches when developers try to work together on the same project and try to modify shared objects. One of the problems is that objects that are forgotten get locked. Unless you have Supervisor grants, you will not be able to unlock the object. In this article, I will try to explain how to handle this problem without granting Supervisor to everyone.

Disclaimer: Use at your own risk. The method described in this post manipulates some records in Oracle Data Integrator's repository. Directly manipulating Oracle Data Integrator's repository tables may lead to unexpected behavior or crash of the software. The writer of the post can not be held liable because of the unexpected behaviors or crashes.

Best Practices for Grants in ODI

ODI has some default grant levels with detailed options to manipulate the roles. And there is an accepted way to distinguish roles in ODI.

  • Supervisor: Supervisor users are able to do anything in ODI interface; they do not have limitations.

  • Security admin: Security admins are responsible for adding new users and altering user privileges.

  • Topology admin: Topology admins are responsible for adding/manipulating new data servers or connections, contexts, repositories, languages, or functions.

  • Developer: Developers are responsible for creating net ETL flows, so they are able to manipulate code objects.

  • Analyst/guest: In some organizations, there are employees who need to inspect the code elements to create analysis or new business rules.

The Problem

This is where the problem occurs. A developer can lock any code object while working on it, but it can not modify or unlock an object locked by other developers. They need a Supervisor or the developer who locked the object to get the object unlocked. It also occurs that a developer left a few objects locked, and also left the job. So, you can't request release from the developer.

The Solution

Since I am one of the developers with the Supervisor grant in our organization, sometimes, it can be too time-consuming to unlock objects for the other developers so they can do their job.

That's why I've created a procedure solely to unlock objects: a procedure that takes the object name as input and modifies its lock situation in the background to release it. The procedure directly works on repository tables, and it can be dangerous for the people who are not experienced enough to manipulate these tables. Use at your own risk!

How does it work?

  • The developer writes the input in the third line.

  • Procedure searches for the name in ODI Interfaces and Packages tables to find the ID.

  • Procedure deletes the record with ID from locked objects table.

DECLARE
---------------
v_object_to_unlock VARCHAR2(255) := 'OBJECT_NAME_TO_UNLOCK'; -- CHANGE THIS LINE ONLY
---------------
v_object_id NUMBER;
v_instance_id NUMBER;
BEGIN

SELECT I_OBJECT, I_INSTANCE
INTO v_object_id, v_instance_id
FROM (
SELECT I_POP I_INSTANCE, POP_NAME OBJECT_NAME, '3100' AS I_OBJECT
FROM ODIWD_TRAINING_11G.SNP_POP
UNION ALL
SELECT I_PACKAGE, PACK_NAME, '3200' FROM ODIWD_TRAINING_11G.SNP_PACKAGE
)
WHERE OBJECT_NAME = v_object_to_unlock;

dbms_output.put_line(v_instance_id);
dbms_output.put_line(v_object_id);

DELETE FROM ODIWD_TRAINING_11G.SNP_LOCKED_OBJECT
WHERE I_OBJECT = v_object_id
AND I_INSTANCE = v_instance_id;

END;

Conclusion

Although Oracle Data Integrator is one of the best and common ETL tools in the market, it can cause concurrency working problems caused by developer negligence. Using the method above, I offer a solution to a specific problem. Hope it helps you and your organization.

For more information on the ODI 11g Repository, you can check these nice diagrams.

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:
oracle ,data integration ,database ,etl ,locked objects

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}