View previous topic :: View next topic
|
Author |
Message |
GANAPATH
New User
Joined: 06 Mar 2006 Posts: 35
|
|
|
|
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 |
|
|
varun_sharma
New User
Joined: 08 Jun 2005 Posts: 19 Location: new delhi
|
|
|
|
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 |
|
|
GANAPATH
New User
Joined: 06 Mar 2006 Posts: 35
|
|
|
|
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 |
|
|
anamikak
New User
Joined: 10 May 2006 Posts: 64 Location: Singapore
|
|
|
|
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 |
|
|
varun_sharma
New User
Joined: 08 Jun 2005 Posts: 19 Location: new delhi
|
|
|
|
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 |
|
|
amittandon09 Currently Banned New User
Joined: 12 Apr 2006 Posts: 2 Location: pune
|
|
|
|
is it possible data copy from ksds to table or table to ksds
how plz send me coding of that proig |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
mahsug
New User
Joined: 01 Jul 2003 Posts: 15 Location: Phoenix , AZ
|
|
|
|
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 |
|
|
PeD
Active User
Joined: 26 Nov 2005 Posts: 459 Location: Belgium
|
|
|
|
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 |
|
|
|