How to extract source and target attributes from migration center database

The source or target attributes can be exported to a CSV file from migration-center client. The option is available in the source/target/error objects grid in the Scanners and Migration Sets sections. 

In case when more sophisticated selection criteria is needed, the data can be exported with some queries directly from migration-center database. The queries can be executed in SQL Developer (or any other Oracle tool like that) connected as user FMEMC. The source and target attributes are stored in a flexible data structure inside the source_objects table so they cannot be queried like normal table columns. For facilitating attribute retrieval, migration center offers some util SQL functions that can be used in the queries:

Extract one or multiple source attributes

FUNCTION get_source_attribute(i_source_attr_coll ty_fme_attr_varray,
i_attribute_name VARCHAR2)
Purpose: Get the first value of a give source attribute or empty string if the attribute cannot be found Input: ty_fme_attr_varray - array of source attributes i_attribute_name - attribute name Output: The value of the source attribute

sa

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 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 maping 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;

Notes:

  • The target attributes can only be obtained by name for the objects in status validated, imported 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.
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.