You are probably interested in more than just tables (e.g. views, functions, triggers etc), but th basic approach is the same.
You shoudl also be using the syscat views rather than the sysibm tables but both will still work.
First up, you ned to get access to both of the syscat.tables. If you have federated support or can enable it, this would be the smartest way as you can then join the two tables directly. Otherwise you should consider exporting and loading one of the syscat.tables.
Following query will then list the table differences (you will only need the select statement):
- create table db1_tables like syscat.tables;
-
create table db2_tables like syscat.tables;
-
-
insert into db1_tables select * from syscat.tables where tabname like 'EB%';
-
insert into db2_tables select * from syscat.tables where tabname like 'EB%';
-
-
update db1_tables set tabname = 'EB_ETLNEW' where tabname = 'EB_ETL';
-
-
select rtrim(db1.tabschema) || '.' || db1.tabname as db1_name,
-
rtrim(db2.tabschema) || '.' || db2.tabname as db2_name
-
from db1_tables db1
-
full outer join db2_tables db2
-
on db1.tabschema = db2.tabschema
-
and db1.tabname = db2.tabname
-
where db1.tabname is null
-
or db2.tabname is null;
Similar approach can be used for other objects joining on the primary key of the tables: