467,923 Members | 1,845 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

Compare two databases in different subsystem


I have two database which reside on two different subsystem respectively. I would like to generate a list of matching and non matching tables list, that present in these two databases. Do we have any sql to generate this report from SYSIBM tables. Thank you in advance.
Aug 1 '07 #1
  • viewed: 2497
2 Replies
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):

Expand|Select|Wrap|Line Numbers
  1. create table db1_tables like syscat.tables;
  2. create table db2_tables like syscat.tables;
  4. insert into db1_tables select * from syscat.tables where tabname like 'EB%';
  5. insert into db2_tables select * from syscat.tables where tabname like 'EB%';
  7. update db1_tables set tabname = 'EB_ETLNEW' where tabname = 'EB_ETL'; 
  9. select rtrim(db1.tabschema) || '.' || db1.tabname as db1_name,
  10.        rtrim(db2.tabschema) || '.' || db2.tabname as db2_name
  11.   from            db1_tables db1
  12.   full outer join db2_tables db2
  13.     on db1.tabschema = db2.tabschema
  14.    and db1.tabname   = db2.tabname
  15.  where db1.tabname is null
  16.     or db2.tabname is null; 
Similar approach can be used for other objects joining on the primary key of the tables:
Aug 2 '07 #2
i am intrested to find differences between 2 identical tables in 2 different subsystem.even i already setup the federation setup.

can i have the SQLs to compare the columns,views,triggers,keys,indexes ..your help is really required.

THnaks kanaka
Sep 13 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Jason | last post: by
1 post views Thread by Prakash RudraRaju | last post: by
8 posts views Thread by Vincent | last post: by
1 post views Thread by Stephen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.