Page Comparison

...

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:

Code Block

language	sql

/* 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;

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

Code Block

language	sql
title	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_%';

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.

...

Versions Compared

Old Version 14

New Version Current

Key