How to extract source and target attributes from migration center database

You can extract source or target attributes to a CSV file from migration-center client directly, by using the export feature in the source/target/error objects grids in the Scanners and Migration Sets sections. 

If you need a more sophisticated selection criteria, migration-center has build in PL/SQL functions you can use in SQL queries in the migration-center database. You can execute these queries in SQL Developer (or any other similar Oracle tool) connected as user FMEMC. You need to use these functions for each source or target attribute in the Select section of your query.

Extract one or multiple source attributes

FUNCTION get_source_attributes(i_source_attr_coll ty_fme_attr_varray,
                               i_attribute_names VARCHAR2,
                               i_values_delimiter VARCHAR2 DEFAULT ';',
                               i_attr_delimiter VARCHAR2 DEFAULT '|')

Purpose:  Extract values of one or many source attribute
Input:    ty_fme_attr_varray - array of source attributes
          i_attribute_names - attribute names, comma delimited
          i_values_delimiter - repeating values delimiter (default is ;)
          i_attr_delimiter   - delimiter between values of attributes 
                               In case many attributes are used (default is |)
Output: The concatenated values of the all given attributes

Extract one or multiple target attributes

 FUNCTION get_target_attributes(i_target_attr_coll ty_fme_target_attr_varray,
                                i_attribute_names VARCHAR2,
                                i_values_delimiter VARCHAR2 DEFAULT ';',
                                i_attr_delimiter VARCHAR2 DEFAULT '|')

Purpose:  Extract values of one or many source attribute
Input:    ty_fme_target_attr_varray - array of target attributes
                 i_attribute_names - attribute names, comma delimited
                 i_values_delimiter - repeating values delimiter (default is ;)
                 i_attr_delimiter   - delimiter between values of attributes
                                      (in case many attributes are used)
Output: The concatenated values of all given attributes.

Query samples

Select internal id, id in source system, id in target system and the target attribute Name for all objects in a specific scanner run, linked under a specific target folder path:

select so.id, so.id_in_source_system, so.id_in_target_system, 
   mc_util.get_target_attributes(so.target_attributes, 'Name') as name
from source_objects so
where so.adaptor_job_run_id = 1278
  and mc_util.get_target_attributes(so.target_attributes, 'ParentFolder') like '/Parent_folder%';

Extract the mapping between source object type and target object type for all imported objects in a migset

select so.id, so.id_in_source_system, so.id_in_target_system, 
 mc_util.get_source_attributes(so.source_attributes, 'r_object_type') as source_type,
 mc_util.get_target_attributes(so.target_attributes, 'r_object_type') as target_type
from source_objects so
where so.migset_id = 1278
 and status_id = 4;

Calculate the content size of documents scanned from Documentum that were imported by a specific importer:

select sum(to_number(nvl(mc_util.get_source_attribute(source_attributes, 'r_content_size'), 0)))/(1024*1024) as content_size_in_mb 
from source_objects 
where last_importer_run_id = 1000;

Objects columns that can be used in queries

id - the internal unique identifier of the object

adaptor_job_run_id - the id of the scanner run that scanned the object

last_importer_run_id - the id of the last importer run that imported the object. Empty if the is not imported.

migset_id - the id of the migset to which the object was assigned. It's empty (null) if the object is not assigned to a migset. 

status_id - the status of the object. 

0 - Scanned
1 - Unprocessed
2 - Transformed
3 - Validated
4 - Imported
5 - Transformation error
6 - Validation error
7 - Import error
8 - Imported partially

is_update - 0 - if the object is not an update. 1- if the object is an update of another objects (scanned during delta process)

source_type - the source type of the object

content_location - the location where the content was exported during the scan. Empty if the object has not content

id_in_source_system - the unique identifier of the object in the source repository

id_in_target_system  - the unique identifier of the object in the target repository. It set during the import so it's empty for the objects that haven't been imported.

parent_version_object_id - the internal id of the object that is the parent version of the current object. Used internally by migration-center for handling the versions.

level_in_versin_tree - the rank of the object in the version tree. It's 0 for the root version. Used internally by migration-center for handling the versions.

content_hash - the checksum of the content calculated during the scan. It's only set if the object has a content and the scanner was configured to calculate the content checksum

scanned_date - the date and time when the object was scanned

imported_date - the date and time when the object was imported

processing_error - the error message that may occurs during the transformation, validation or import.

Notes:

• The target attributes can only be obtained by name for the objects in status validated, imported, imported partially or import error. The attribute name should be the value of the "target_attribute" from second column in the association tab.
• The source attributes can be obtained regardless of the status of the object.
• Using the function in the where clause on large volume of data is slow so it's recommended to always add an additional condition that filter the database based on migset id or scanner run id.