Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Compare two tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to Compare two tables
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    Post subject: Re: How to Compare two tables
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    Post subject: Re: How to Compare two tables
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    Post subject:
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    Post subject: Re: How to Compare two tables
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: 3
Location: pune

PostPosted: Sat Jun 17, 2006 6:52 pm    Post subject: db2
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: 1228
Location: Israel

PostPosted: Mon Jun 19, 2006 1:19 am    Post subject:
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    Post subject: Re: How to Compare two tables
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: 456
Location: Belgium

PostPosted: Fri Jun 23, 2006 1:18 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts member list compare jzhardy TSO/ISPF 6 Fri May 12, 2017 3:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts fuzzy compare Martylin JCL & VSAM 9 Thu Mar 30, 2017 2:45 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us