reconciliation of object librarian and central objects

This is a sneaky AS/400 SQL statement that will reconcile central objects F987* tables with F9861.  So this essentially gets a massive list of distinct object names from CO and see’s if there is a F9861 record for that object.  So this is a nice way of determining if you have orphan central objects – or missing OL records.

select distinct object_name from (
SELECT distinct THOBNM as object_name FROM COUA920.F98710 union   
SELECT distinct TDOBNM as object_name FROM COUA920.F98711 union   
SELECT distinct TPOBNM as object_name FROM COUA920.F98712 union   
SELECT distinct TLOBNM as object_name FROM COUA920.F98713 union   
SELECT distinct BVOBNM as object_name FROM COUA920.F98720 union   
SELECT distinct ELOBNM as object_name FROM COUA920.F98740 union   
SELECT distinct DTOBNM as object_name FROM COUA920.F98743 union   
SELECT distinct SFOBNM as object_name FROM COUA920.F98745 union   
SELECT distinct FTOBNM as object_name FROM COUA920.F98750 union   
SELECT distinct FSOBNM as object_name FROM COUA920.F98751 union   
SELECT distinct AHOBNM as object_name FROM COUA920.F98752 union   
SELECT distinct ADOBNM as object_name FROM COUA920.F98753 union   
SELECT distinct RTOBNM as object_name FROM COUA920.F98760 union   
SELECT distinct RSOBNM as object_name FROM COUA920.F98761 union   
SELECT distinct BCOBNM as object_name FROM COUA920.F98762) as tbl
where not exists (
select 1 from ol920.f9861
where sipathcd = 'UA920'
and siobnm = object_name);