Ever wanted to compare two environments? Row counts…

I have a client with a funny setup, that is they have two SEPARATE systems.  This includes SY, OL, DD – everything that you would expect.  UA910 runs in one and PD910 in the other.  They wanted some sort of reconciliation between the environments to ensure that PD = UA – reasonable in the circumstances.  We ere finding little differences, version here, object there – but then it became harder with things like text translations and items in the system tables.  So I decided to get out the SQL big brush and so some comparisons.  This is based upon some of the scripts that I’ve previously posted on reconciliation of data between upgrades.  This is oracle syntax.

I create a central table.  I use a database link for the jde_ujde stuff – you’ll see that in the code.  I do some mapping of owners too, UADTA to PRODDTA and UACTL to PRODCTL.  Finally I do UA910 to PD910.

You’ll see that I forgot central objects in the first insert and had to catch it up at the end.

Please also note that often I’m generating SQL that you’ll need to run, so paste the results into the run window.

CREATE TABLE "SY910"."DATACOUNTS"
   (    "TABLE_NAME" NVARCHAR2(20),
        "TABLE_OWNER" NVARCHAR2(20),
        "ORACLE_COUNT_DJDE" NUMBER,
        "ORACLE_COUNT_PJDE" NUMBER,
        "TABLE_DESCRIPTION" NVARCHAR2(256)
   )
      TABLESPACE "SY910T" ;

grant all on SY910.DATACOUNTS to public ;

insert into SY910.datacounts select table_name,owner,0,0,simd 
from all_tables, ol910.f9860 
where owner in ('OL910', 'DD910', 'SY910', 'PRODDTA', 'PRODCTL')
and ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name));

select 'update SY910.dataCounts set ORACLE_COUNT_PJDE = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('OL910', 'DD910', 'SY910', 'PRODDTA', 'PRODCTL')
 order by table_name desc ;
 
select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || table_owner || '.' || table_name || '@jde_djde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('OL910', 'DD910', 'SY910')
 order by table_name desc ;

select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UADTA' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PRODDTA' || ''';' from sy910.datacounts 
 where table_owner in ('PRODDTA')
 order by table_name desc ;

select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UACTL' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PRODCTL' || ''';' from sy910.datacounts 
 where table_owner in ('PRODCTL')
 order by table_name desc ;

insert into SY910.datacounts select table_name,owner,0,0,simd 
from all_tables, ol910.f9860 
where owner in ('PD910')
and ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name));

select 'update SY910.dataCounts set ORACLE_COUNT_PJDE = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('PD910')
 order by table_name desc ;
 
  select 'update SY910.dataCounts set ORACLE_COUNT_DJDE = (select count(1) from ' || 'UA910' || '.' || table_name || '@jde_ujde ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || 'PD910' || ''';' from sy910.datacounts 
 where table_owner in ('PD910')
 order by table_name desc ;
 
 commit ;
 
 select * from sy910.datacounts ;