IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to Compare two tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
GANAPATH

New User


Joined: 06 Mar 2006
Posts: 35

PostPosted: Thu Jun 08, 2006 8:38 pm
Reply with quote

Hi,

i have two tables and i expect them to match 100%
(No. of columns and their names and no.of rows and the data).
Now is there a SQL that i can write to do the comparision.

For data matching,
I know i can do by A.COL1 = B.COL1 so on and so forth but if i have 150
columns, how can i do this in a easier way, any utility or SQL and also
how do we compare no. of columns and their names

Appreciate any Input,
Ganapath
Back to top
View user's profile Send private message
varun_sharma

New User


Joined: 08 Jun 2005
Posts: 19
Location: new delhi

PostPosted: Thu Jun 08, 2006 9:20 pm
Reply with quote

Hi Ganapath,

If you use File Aid for DB2, you can simply extract the table data to a PS and along with that you can also extract the layout and control cards to a PS. Then using ISPF option 3.12 you can compare the two files. You should first compare the layouts/control cards and if they match than only you carry on with comparing the extracted data.

If you need any clarification please let me know.

Cheers
Varun
Back to top
View user's profile Send private message
GANAPATH

New User


Joined: 06 Mar 2006
Posts: 35

PostPosted: Thu Jun 08, 2006 9:25 pm
Reply with quote

Hi,

Thanks for the reply, i forgot to mention that we have a requirement not to compare the unload files or control cards because of other issues but to compare the tables

any suggestions?,

Thanks,
Ganapth
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Tue Jun 13, 2006 4:47 pm
Reply with quote

1. if you have to write SQLs to compare data in column there is no other way than equijoin on columns of table.

2. If you are allowed to use utility, then unload 2 tables into two separate sequential file and compare them using fileaid or comparex utility.

3. if you want to compare column names and types you can query the SYSIBM.SYSCOLUMNs table.

Anamika
Back to top
View user's profile Send private message
varun_sharma

New User


Joined: 08 Jun 2005
Posts: 19
Location: new delhi

PostPosted: Tue Jun 13, 2006 9:04 pm
Reply with quote

Hi Anamika,

I don't think you can compare column names and types by querying the SYSIBM.SYSCOLUMNs table. As far as i know this table contains details about the table columns but how you compare them im not really sure of that. So can you please explain and if possible gve an exmple.

Cheers
varun
Back to top
View user's profile Send private message
amittandon09
Currently Banned

New User


Joined: 12 Apr 2006
Posts: 2
Location: pune

PostPosted: Sat Jun 17, 2006 6:52 pm
Reply with quote

is it possible data copy from ksds to table or table to ksds
how plz send me coding of that proig
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Mon Jun 19, 2006 1:19 am
Reply with quote

Have a look at SYNCSORT.
If I remember well, you can use a SELECT command as "input file". It is an interesting option because it allows you to access the data without actually reading it...
Now, can SYNCSORT be used to compare two files? This I don't know (yet)
Back to top
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Thu Jun 22, 2006 12:00 pm
Reply with quote

Hi Ganapath ,

If you are allowed to make a query on the Db2 catalog tables , you can perform the full outer join on both the tables, which will tell you non-matching rows from both the tables , ie

select * from sysibm.syscolumns a where tbname=<first tablename> outer join (select * from sysibm.syscolumns b where tbname=<second tablename >)
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 459
Location: Belgium

PostPosted: Fri Jun 23, 2006 1:18 am
Reply with quote

EComp can easily compare DB2 tables. And more : from different SSID's, or cross compare an unload versus a current DB2 tables, etc
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Compare two files with a key and writ... SYNCSORT 3
No new posts Compare latest 2 rows of a table usin... DB2 1
No new posts How to compare two rows of same table DB2 11
No new posts Need to fetch data from so many DB2 t... DB2 9
Search our Forums:

Back to Top