Monday, August 30, 2010

ODI Object versioning

Versioning in ODI is a less covered topic.
Lets look today at how ODI handles object versioning. If you recall from the ODI repository architecture diagrams, ODI stores Topology, Security and Versioning information in the Master Repository.
Each time an ODI object (Project, Model, Datastore, etc) is versioned, it is saved in the Master Repository.

Eg. If we version the package - PKG_ODI_PROCESS as follows:


Then, in ODI_MASTER_REPOSITORY, we can see that there are 2 new records added to the SNP_VERSION table. The following query can be used:

SELECT i_instance, ext_version, i_data, instance_name
FROM snp_version
WHERE 1 = 1
and instance_name LIKE 'PKG_ODI_PROCESS'

In here,
I_INSTANCE is the Internal ID of the object in the work repository
EXT_VERSION is the 1.0.0.1 notation version assigned to the object
I_DATA is the FK into SNP_DATA table that stores the versioned object
INSTANCE_NAME is the name of the object itself at the time of being versioned

SNP_VERSION works alongwith SNP_DATA to store the actual versioned object. We can get that information using the following query:
SELECT sv.i_instance,
sv.i_objects,
sv.ext_version,
sv.i_data,
sv.instance_name,
sd.data_contents
FROM snp_version sv, snp_data sd
WHERE 1 = 1
and sv.i_data = sd.i_data
and
instance_name LIKE 'PKG_ODI_PROCESS'

The DATA_CONTENTS column is of type LONG_RAW and stores the versioned object in a compressed LZW compression form (Unix compress command)

We can save the contents of SNP_DATA.DATA_CONTENTS into an Operating System file named as pkg_odi_process.Z, though we can name it whatever we wish with a .Z extension.
Uncompress this file using Unix uncompress or Windows Winzip, WinRar.
The file that you get by uncompressing, should be given a .xml extension.
The resulting pkg_odi_process.xml file is the same file as if we would had exported the package PKG_ODI_PROCESS using Export command in the Designer module into an XML file.

This file can be used to import back to the Work Repository.