Singletons are not matched after an upgrade to 3.2

Owned by FX Nicolas
Last updated: May 02, 2016 by Matthew Dahlman (Deactivated)
5 min read

Problem

After an upgrade to version 3.2 (3.2.1, 3.2.2 or 3.2.3), singleton golden records (for fuzzy matching entities) already in the hub are not merged with incoming records that match them. 

Solution

This issue is identified as a known bug (MDM-4054) of the 3.2 upgrade, and it is fixed in version 3.2.4 and above. The upgrade does not populate correctly the B_MATCHGRP column for these singletons.

  • Customers not yet on 3.2.x and who are planning for an upgrade should make sure to use a version 3.2.4 and above for their upgrade. 

  • Customers on 3.2.1-3.2.3. should run the SQL script and that's all. Customers who have already upgraded should use the following process to unblock the singleton records from their current situation.
    The 3.2.4 is useless to them as 3.2.4 only fixes the upgrade process that won't be re-executed in their case. The upgrade process is only executed when major or minor version changes not when only the patch version so that patch versions are supposed to be backward and forward compatible with each others.

Fixing the singletons not matched after an upgrade to 3.2 issue

 

  1. Connect to each upgraded data locations using a query tool (SQL Developer, SQL*Plus) and run either Option 1 or Option 2 the following script: 

    /* OPTION 1: Run these queries to generate SQL statements to fix the singletons not matched after an upgrade to 3.2 issue. 
 * Update 'YOUR_SCHEMA_NAME' with your actual Semarchy MDM schema name.
 */
select
 'update ' || t.owner || '.MI_' || SUBSTR(t.table_name,4) || ' set B_MATCHGRP = SEQ_MATCHGRP.nextval where B_MATCHGRP is null'
FROM all_tables t
WHERE t.owner='YOUR_SCHEMA_NAME' AND t.table_name like 'UM_%';

select
  'merge into ' || t.owner || '.MD_' || SUBSTR(table_name,4) ||'  T
            using (
            select
              MD.B_BRANCHID,
              MD.B_FROMEDITION,
              MD.B_PUBID,  
              MD.B_SOURCEID,
              MI.B_MATCHGRP
            from
              ' || t.owner || '.MI_' || SUBSTR(table_name,4) ||' MI
              inner join ' || t.owner || '.MD_' || SUBSTR(table_name,4) ||' MD
              on (
                MD.B_BRANCHID = MI.B_BRANCHID and MD.B_SOURCEID = MI.B_SOURCEID and MD.B_PUBID = MI.B_PUBID
                and MD.B_MATCHGRP is null
              )
          ) S
          on (S.B_BRANCHID = T.B_BRANCHID and S.B_FROMEDITION = T.B_FROMEDITION and S.B_SOURCEID = T.B_SOURCEID and S.B_PUBID = T.B_PUBID )
          when matched then
            update set
              T.B_MATCHGRP = S.B_MATCHGRP'
FROM all_tables t
WHERE t.owner='YOUR_SECHEMA_NAME' AND t.table_name like 'UM_%';

/* OPTION 2: An alternative which automatically executes the statements follows below.
 * This script fixes the "singletons not matched after an upgrade to 3.2" issue.
 * It should be customized depending on the privileges of the user running it.
 */
BEGIN
   FOR t IN (SELECT owner, SUBSTR(table_name,4) physical_name FROM all_tables WHERE owner=:SCHEMA_NAME AND table_name like 'UM_%') LOOP
      EXECUTE IMMEDIATE 'update ' || t.owner || '.MI_' || t.physical_name ||' set B_MATCHGRP = SEQ_MATCHGRP.nextval where B_MATCHGRP is null';
      EXECUTE IMMEDIATE 'merge into ' || t.owner || '.MD_' || t.physical_name ||'  T
            using (
            select
              MD.B_BRANCHID,
              MD.B_FROMEDITION,
              MD.B_PUBID,	
              MD.B_SOURCEID,
              MI.B_MATCHGRP
            from 
              ' || t.owner || '.MI_' || t.physical_name ||' MI
              inner join ' || t.owner || '.MD_' || t.physical_name ||' MD
              on (
                MD.B_BRANCHID = MI.B_BRANCHID and MD.B_SOURCEID = MI.B_SOURCEID and MD.B_PUBID = MI.B_PUBID
                and MD.B_MATCHGRP is null 
              )
          ) S
          on (S.B_BRANCHID = T.B_BRANCHID and S.B_FROMEDITION = T.B_FROMEDITION and S.B_SOURCEID = T.B_SOURCEID and S.B_PUBID = T.B_PUBID )
          when matched then
            update set
              T.B_MATCHGRP = S.B_MATCHGRP';
      dbms_output.Put_line('Fixed: ' || t.physical_name);
      
   END LOOP;
END;

  2. After running the update, then you can check the results.

    Check the results
    /* To check your work, run the following sql statements. 
 * This will generate multiple SQL statements that should be run manually. 
 * If each of those statements return 0 rows, you have fixed the problem. 
 */
select
 'select count(1) from ' || t.owner || '.MD_' || SUBSTR(t.table_name,4) || ' where B_MATCHGRP is null'
FROM all_tables t
WHERE t.owner='YOUR_SCHEMA_NAME' AND t.table_name like 'UM_%';
  3. After this fix, existing singletons will be joined only by new or updated records.
    If you suspect that records have been pushed to the hub after the upgrade and before the fix and should be reconsidered for matching with the singletons, then you should re-load these records.

 

Important

We strongly recommend that you perform a backup of your data location schema before running the SQL script.