Maximo - Query DB for Key Table Differences

From ExxtremeWiki
Jump to: navigation, search

Maximo; Query DB for Key Table Differences

Query to resolve differences between Key Maximo tables on two different databases

/*
 
        Need to test for and remove commas at the end of the s_script string before
        appending them to the "group by" and "order by" clauses...
        Still need a way to remove the ', ,' entries other than search and replace in the output file.
        Note: use "set header on" for the outputs "output" to file so the tables are easier to understand
        can see if we can use dbms.output to build the header and footer information so it can be automated.
        Copyright 2009 (c) Exxtreme Consulting, Inc
        Author: Will Hampton, Exxtreme Consulting, Inc 978-337-9099
        Select EDIT to see this unformatted by wiki and be able to copy out for use...
    / 
 
set serveroutput on size 1000000
 
set heading off
 
SPOOL c:\dbcomparison090520.sql
 
declare
 
s_script varchar2(32767) := ; s_fieldlist varchar2(32767) :=; s_old_tablename varchar2(50) := 'y'; s_replace varchar2(50) := ; s_table_name varchar2(50); s_column_name varchar2(50); max_a_id number;
 
cursor c1 is
 
select column_name, table_name from dba_tab_columns where owner = 'MAXIMO' and lower(table_name) in ('action', 'actiongroup', 'alndomain', 'appdoctype', 'applicationauth', 'autokey', 'commtemplate', 'commtmpltsendto', 'contractdefault', 'contractproperty', 'crontaskdef','crontaskinstance', 'crontaskparam', 'crossoverdomain', 'defaultquery', 'doctypes', 'dummy_table', 'escalation', 'escnotification', 'escrefpoint', 'excludedactions','fsnclass','fsnclassqual', 'fsnclassrelation', 'fsnmethod', 'fsnmethodparam', 'fsnmthdparamqual', 'fsnmthdqual', 'fsnobject', 'fsnobjproperty', 'fsnproperty', 'fsnpropertyqual', 'fsnprovider', 'fsnqualifier', 'fsnrefproperty', 'fsnschema', 'fsnsqlcolumn', 'fsnsqltable', 'fsntablequal', 'groupuser', 'language', 'maxapps','maxattribute', 'maxattributecfg', 'maxconddetail', 'maxcontrolvalue', 'maxdomain', 'maxendpoint', 'maxendpointdtl', 'maxextifacein', 'maxextifaceout', 'maxextlistval', 'maxextsyscontrol', 'maxextsystem', 'maxgroup', 'maxhandler', 'maxiface', 'maxifacecond', 'maxifacecontrol', 'maxifacein', 'maxifaceout', 'maxifaceproc', 'maxifacetype','maxifacetypeprop', 'maxintmsgtype', 'maxintobjcols', 'maxintobjdetail', 'maxintobject', 'maxintpoint', 'maxintwsprops', 'maxlabels', 'maxlookupmap', 'maxmenu','maxmessages', 'maxmodules', 'maxobject', 'maxobjectcfg', 'maxqueue', 'maxrelationship', 'maxreplaceproc', 'maxrole', 'maxsequence', 'maxservice', 'maxsysindexes','maxsyskeys', 'maxtable', 'maxtablecfg', 'maxtabledomain', 'maxuser', 'maxvars', 'maxvartype', 'maxview', 'maxviewcfg', 'maxviewcolumn', 'maxviewcolumncfg', 'mea_dummy_table','mxcollab', 'mxcollabref', 'numericdomain', 'paletteitem', 'portlet', 'propertyassoc', 'query', 'report', 'reportlabel', 'reportlookup', 'resultsetcols', 'sigoption','synonymdomain', 'wfaction', 'wfapptoolbar', 'wfassignment', 'wfcondition', 'wfnode', 'wfnotification', 'wfprocess', 'wfrevision', 'wfstart', 'wfstop', 'wftask') order by table_name, column_name; begin
 
   open c1;
 
while c1%isopen loop fetch c1 into s_column_name, s_table_name; exit when c1%notfound; if (s_old_tablename is null or s_old_tablename = 'y') then s_old_tablename := s_table_name; end if; -- s_fieldlist := s_column_name; while s_old_tablename = s_table_name loop if s_column_name != 'ROWSTAMP' then s_fieldlist := s_fieldlist||', '||s_column_name; end if; fetch c1 into s_column_name, s_table_name; exit when c1%notfound or s_old_tablename != s_table_name;
 
   		end loop;
 
/*
 
        search for UID if found remove it, if not search and remove ID 
    / 
 
       s_replace := s_old_tablename||'UID';
 
-- dbms_output.put_line('Searching for: '); -- PORTLETUID, -- dbms_output.put_line(s_replace);
 
       if (instr(s_fieldlist, 'UID') != 0)
       then
 
-- dbms_output.put_line('Replacing: '); -- , , -- dbms_output.put_line(s_replace);
 
       	s_fieldlist := replace(s_fieldlist, s_replace);
 
-- s_fieldlist := trim(s_fieldlist, ', ,', ','); -- dbms_output.put_line('Found a "UID" field');
 
  		else
  			if (instr(s_fieldlist, 'ID') != 0) then
 
s_replace := s_old_tablename||'ID'; -- dbms_output.put_line('Replacing: '); -- , , -- dbms_output.put_line(s_replace);
 
       	s_fieldlist := replace(s_fieldlist, s_replace);
 
-- s_fieldlist := trim(s_fieldlist, ', ,', ','); -- dbms_output.put_line('Found an "ID" field');
 
  			end if;
       end if;
 
/*
 
        continue processing 
    / 
 
s_script := 'select decode(count(src1), 1, ||s_old_tablename||', ||s_old_tablename||'_test) as TableName, ' ||ltrim(s_fieldlist, ','); s_script := s_script || ', count(src1) CNT1, count(src2) CNT2';
 
       s_script := s_script || '
  from
    ( select a.*,
           1 src1,
           to_number(null) src2
        from maximo.'||s_old_tablename||' a
       union all
     select b.*,
          to_number(null) src1,
          2 src2
       from maximo.'||s_old_tablename||'@QKMXIT.WORLD b
    )
   group by '||ltrim(s_fieldlist, ',')||'
  having count(src1) <> count(src2)
  order by '||ltrim(s_fieldlist, ',')||'
 
/
 
'; if (s_old_tablename != s_table_name or s_old_tablename = 'y') then
 
		 	s_old_tablename := s_table_name;
 
dbms_output.put_line((s_script)); s_script := ;
 
	        s_fieldlist := s_column_name;
 
end if;
 
end loop;
 
dbms_output.put_line((s_script)); -- exception
 
 --   	when no_data_found then
--    dbms_output.put_line('Display a string literal');
 
close c1; end; /
 
spool off

Categories: Maximo 6.x, Oracle