473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Compare two databases in different subsystem

Hi,

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
2 2680
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;
  3.  
  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%';
  6.  
  7. update db1_tables set tabname = 'EB_ETLNEW' where tabname = 'EB_ETL'; 
  8.  
  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
kanaka
4
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

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

Similar topics

4
by: Jason | last post by:
I realize that FK cannot be created across databases. The question I have is what are the alternatives? In my thinking I have two choices. 1) First choice (which I read in a different post) is...
1
by: Prem K Mehrotra | last post by:
I come from Oracle background. I am trying to find a script (job) which creates a db2 subsystem (create all system catalog tables etc), create necessary "DBA" logins,so I can create databases and...
0
by: xixi | last post by:
hi, we are using db2 udb v8.1 on win 64 bit with fp3 with type 4 db2jcc.jar driver. when i execute this query , select id, arno01, arcd01, arno16, artx01, armo09, ardy09, arcc09, aryr09,...
1
by: Prakash RudraRaju | last post by:
Hi, I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test successful migration I want to compare all tables. I have linked MySQL tables through ODBC connection. I want to...
8
by: Vincent | last post by:
has any one seen a program to compare mdbs'. I have ran into a few of them, but none seem to really do that job. Basically what I need to do is, take 2 access mdb's and check the differences...
1
by: Stephen | last post by:
I am trying to compare the tables in two similar databases using the SQLDMO object. I am able to use this object to access different SQL servers and choose two different databases. The versions of...
17
by: Mark A | last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are...
13
by: Charlotte | last post by:
Hi, i've googeled to find a asp-script that can compare all the records in two different access databases the mdb's have exactly the same tables what i want is that (the output) all the...
3
by: alter.fire | last post by:
Hey im trying to do the following in PHP: Connect to 2 different databases, one named complete and one incomplete. My goal is to find whats missing in the "incomplete" database. The fields...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.