IM Relationship Resolution Information Center, Version 4.2

Example: Setting up data sources to use with the UMF database conversion utility

The following process demonstrates a sample setup of the process used to extract data from a system wherein the values you want to load consist of the name and DOB. It will construct a name segment and the root segment of the xml.

  1. Create trigger on key fields. In this sample setup, a trigger is created on the entity_name table for add, changes and deletes. Each of the triggers writes a new entity to the sync_log table when the record is processed.
  2. Create the root segment.
    • Open the ACQ_QUERY table and enter the information you want to use:
      Column Value Notes®
      ACQ_NODE Sample Name of the acquisition set
      ACQ_QUERY_SEQ 1 This is the starting query
      SOURCE CUST Has no effect on processing
      DOC_TYPE UMF_ENTITY Creates a umf_entity for loading
      PARENT Y This is the parent query of the entire set
      SELECT_STMT SELECT A.* FROM $$REPLACE(SYNC_LOG) A WHERE SYNC_ID < $$REPLACE(MAX_SYNC_ID) Select statement from sync log. It only queries the sync log because the entity_name table has multiple rows per sync_log entity
      LAST_PROCESSED NULL Not used
      PROCESS_MODE NULL Not used
      LOAD_TO_TEMP_TABLE NULL Not used
      TABLE_NAME NULL Not used
      EXECUTE_ONLY N Not used
  3. Map Fields to XML:
  4. Open the ACQ_NODE_MAP table
    Column Value Notes
    ACQ_NODE SAMPLE Same name as previous
    ACQ_QUERY_SEQ 1 Processing query 1
    XML_SEGMENT ROOT Building the root segment of the xml
    SECTION_NUM 1 First section
    XML_ELEM DSRC_CODE Name of xml element
    TABLE_ELEM `GOLD' Literal value of `GOLD' in this tag
    POSSIBLE_SEGMENT_VALUES   Unused in this example
    REQUIRED_FOR_SEGMENT   Unused in this example
    COMMNT   Unused in this example
    SEQUENCE   Unused in this example
    Column Value Notes
    ACQ_NODE SAMPLE Same name as previous
    ACQ_QUERY_SEQ 1 Processing query 1
    XML_SEGMENT ROOT Building the root segment of the xml
    SECTION_NUM 1 First section
    XML_ELEM DSRC_ACTION Name of xml element
    TABLE_ELEM SYNC_ACTION Use this column value from the select statement
    POSSIBLE_SEGMENT_VALUES   Unused in this example
    REQUIRED_FOR_SEGMENT   Unused in this example
    COMMNT   Unused in this example
    SEQUENCE   Unused in this example
    Column Value Notes
    ACQ_NODE SAMPLE Same name as previous
    ACQ_QUERY_SEQ 1 Processing query 1
    XML_SEGMENT ROOT Building the root segment of the xml
    SECTION_NUM 1 First section
    XML_ELEM DSRC_ACCT Name of xml element
    TABLE_ELEM SYNC_KEY1 Use this column value from the select statement
    POSSIBLE_SEGMENT_VALUES   Unused in this example
    REQUIRED_FOR_SEGMENT   Unused in this example
    COMMNT   Unused in this example
    SEQUENCE   Unused in this example
    Column Value Notes
    ACQ_NODE SAMPLE Same name as previous
    ACQ_QUERY_SEQ 1 Processing query 1
    XML_SEGMENT ROOT Building the root of the xml
    SECTION_NUM 1 First section
    XML_ELEM DSRC_REF Name of xml element
    TABLE_ELEM SYNC_KEY1 Used same value as dsrc_acct
    POSSIBLE_SEGMENT_VALUES   Unused in this example
    REQUIRED_FOR_SEGMENT   Unused in this example
    COMMNT   Unused in this example
    ACQ_NODE SAMPLE Same name as above
  5. Build a second query to extract all the names for this identity:
    • Open the ACQ_QUERY table and enter the information you want to use:
      Column Value Notes
      ACQ_NODE Sample Name of the acquisition set
      ACQ_QUERY_SEQ 2 Now the second query to process
      SOURCE CUST Has no effect on processing
      DOC_TYPE UMF_ENTITY I want to create a umf_entity for loading
      PARENT NULL This is a child and not the parent.
      SELECT_STMT SELECT A.*, LEFT(TYPE,1) TYPE, LAST_NAME, FIRST_NAME, MIDDLE_NAME, GENERATION, DOB_TYPE, DOB, PLACE_OF_BIRTH, GENDER, WEIGHT, HEIGHT, EYE_COLOR, HAIR_COLOR, ETHNIC_ID FROM $$REPLACE(SYNC_LOG) A JOIN ENTITY_NAME ON (SYNC_KEY1) = ENTITY AND TYPE!='ASC' AND SYNC_ID < $$REPLACE(MAX_SYNC_ID) This is the select for the names
      LAST_PROCESSED NULL Not used
      PROCESS_MODE NULL Not used
      LOAD_TO_TEMP_TABLE NULL Not used
      TABLE_NAME NULL Not used
      EXECUTE_ONLY N Not used
  6. Map the Name Fields to XML.
    • Open the ACQ_NODE_MAP table
      Column Value Notes
      ACQ_NODE SAMPLE Same name as above
      ACQ_QUERY_SEQ 2 Processing query 1
      XML_SEGMENT NAME Now building the name segment
      SECTION_NUM 2 Second section
      XML_ELEM FIRST_NAME Name of xml element
      TABLE_ELEM FIRST_NAME Use this column value from the select statement
      POSSIBLE_SEGMENT_VALUES   Unused in this example
      REQUIRED_FOR_SEGMENT   Unused in this example
      COMMNT   Unused in this example
      SEQUENCE   Unused in this example
      Column Value Notes
      ACQ_NODE SAMPLE Same name as above
      ACQ_QUERY_SEQ 2 Processing query 1
      XML_SEGMENT NAME Now building the name segment
      SECTION_NUM 2 First section
      XML_ELEM LAST_NAME Name of xml element
      TABLE_ELEM LAST_NAME Use this column value from the select statement
      POSSIBLE_SEGMENT_VALUES   Unused in this example
      REQUIRED_FOR_SEGMENT   Unused in this example
      COMMNT   Unused in this example
      SEQUENCE   Unused in this example
      Column Value Notes
      ACQ_NODE SAMPLE Same name as above
      ACQ_QUERY_SEQ 2 Processing query 1
      XML_SEGMENT NAME Now building the name segment
      SECTION_NUM 2 First section
      XML_ELEM MID_NAME Name of xml element
      TABLE_ELEM MIDDLE_NAME Use this column value from the select statement
      POSSIBLE_SEGMENT_VALUES   Unused in this example
      REQUIRED_FOR_SEGMENT   Unused in this example
      COMMNT   Unused in this example
      SEQUENCE   Unused in this example
      Column Value Notes
      ACQ_NODE SAMPLE Same name as above
      ACQ_QUERY_SEQ 2 Processing query 1
      XML_SEGMENT NAME Now building the name segment
      SECTION_NUM 2 First section
      XML_ELEM NAME_TYPE Name of xml element
      TABLE_ELEM TYPE Use this column value from the select statement
      POSSIBLE_SEGMENT_VALUES   Unused in this example
      REQUIRED_FOR_SEGMENT   Unused in this example
      COMMNT   Unused in this example
      SEQUENCE   Unused in this example
      Column Value Notes
      ACQ_NODE SAMPLE Same name as above
      ACQ_QUERY_SEQ 2 Processing query 1
      XML_SEGMENT NAME Now building the name segment
      SECTION_NUM 2 First section
      XML_ELEM NAME_GEN Name of xml element
      TABLE_ELEM GENERATION Use this column value from the select statement
      POSSIBLE_SEGMENT_VALUES   Unused in this example
      REQUIRED_FOR_SEGMENT   Unused in this example
      COMMNT   Unused in this example
      SEQUENCE   Unused in this example


Feedback

Last updated: 2009