-- At DB2 V8 FP7, tables in fifth normal form receive replication apply errors if they're updated, 
-- unless the SQL updates are converted to paired delete/insert statements.
-- This script reports all replication source tables in fifth normal form and updates the REGISTER control table.
-- When time permits, rewrite as one of those new-fangled positioned updates from a table expression 
-- that don't use cursors. 

-- run as db2 -t +c -vf 5NF.sql

-- Alex Levy Sustainable Software Ltd http://www.sustainablesoftware.net
-- June 2006.

safey copy!
export to f:\alex\asn.ibmsnap_register.del of del select * from asn.ibmsnap_register
;

-- report1: identify 5NF candidates, exclude system and data capture tables.
with temp (tabschema, tabname, nr_cols)
as        (select tabschema, tabname, count(*)
           from  syscat.columns
           where tabschema not like 'SYS%'
           and   tabschema <>  'ASN'
           and   tabname not like 'CD%'
           group by tabschema, tabname)
select char(rtrim(i.tabschema)||'.'||i.tabname,40), i.unique_colcount,t.nr_cols
from   syscat.indexes i
       INNER JOIN
       temp           t
       on i.tabschema = t.tabschema
       and i.tabname = t.tabname
where  i.unique_colcount = t.nr_cols
order by 1
for fetch only
;

declare global temporary table temp 
(tabschema varchar(128) not null,
 tabname   varchar(128) not null,
 nr_cols   smallint not null    )
with replace
on commit delete rows
in usertemp4k
;

insert into session.temp
select tabschema, tabname, count(*)
from   syscat.columns
where  tabschema not like 'SYS%'
and    tabschema <>  'ASN'
and    tabname not like 'CD%'
group by tabschema, tabname
order by 1,2         
;

declare global temporary table candidates 
(tabschema varchar(128) not null,
 tabname   varchar(128) not null)
with replace
on commit delete rows
in usertemp4k
;

-- a table can have more than 1 unique index over all columns of the table, so select distinct ...
insert into session.candidates
select distinct i.tabschema, i.tabname
from   syscat.indexes i
       INNER JOIN
       session.temp   t
       on i.tabschema = t.tabschema
       and i.tabname  = t.tabname
where  i.unique_colcount = t.nr_cols
order by 1,2
;

-- report 2: should tally with report 1 
select * from session.candidates
order by 1,2
;  

update asn.ibmsnap_register
set  chg_upd_to_del_ins = 'Y'
where char(rtrim(source_owner)||'.'||source_table,40) in
    (select char(rtrim(tabschema)||'.'||tabname,40)
     from   session.candidates
    )
;

-- report 3: should tally with report 2 and 1 
select source_owner,source_table 
from asn.ibmsnap_register
where chg_upd_to_del_ins = 'Y'
order by 1,2
;