Tags: compare, database, datadifference, db2, mysql, oracle, sql, tables, tool, udb, wehave, windows

how to compare two tables difference

On Database » DB2

6,785 words with 9 Comments; publish: Sun, 25 May 2008 09:47:00 GMT; (25062.50, « »)

we are using DB2 udb v8.1 on windows, i would like to know whether we

have tool or way to compare two tables on same database for data

difference. thanks. (same ddl, different data, try to compare whether

the test output run with right data)

All Comments

Leave a comment...

  • 9 Comments
    • "xixi" <dai_xi.db2.todaysummary.com.yahoo.com> wrote in message

      news:c0f33a17.0406301339.42c5db4d.db2.todaysummary.com.posting.google.com...

      > we are using DB2 udb v8.1 on windows, i would like to know whether we

      > have tool or way to compare two tables on same database for data

      > difference. thanks. (same ddl, different data, try to compare whether

      > the test output run with right data)

      Do the tables have a primary key or unique index (with no null values)?

      #1; Sun, 25 May 2008 09:48:00 GMT
    • On Wed, 30 Jun 2004 21:39:08 UTC, dai_xi.db2.todaysummary.com.yahoo.com (xixi) wrote:

      > we are using DB2 udb v8.1 on windows, i would like to know whether we

      > have tool or way to compare two tables on same database for data

      > difference. thanks. (same ddl, different data, try to compare whether

      > the test output run with right data)

      how about something like

      select

      *

      from table_test a

      where not exists (

      select 1 from original_table b where a.col1 = b.col1 and a.col2 =

      b.col2 and a.col3 = b.col3)

      Lorne Sunley

      #2; Sun, 25 May 2008 09:49:00 GMT
    • dai_xi.db2.todaysummary.com.yahoo.com (xixi) wrote in message news:<c0f33a17.0406301339.42c5db4d.db2.todaysummary.com.posting.google.c

      om>...

      > we are using DB2 udb v8.1 on windows, i would like to know whether we

      > have tool or way to compare two tables on same database for data

      > difference. thanks. (same ddl, different data, try to compare whether

      > the test output run with right data)

      You can use EXCEPT operator.

      #3; Sun, 25 May 2008 09:50:00 GMT
    • "Serge Rielau" <srielau.db2.todaysummary.com.ca.eye-be-em.com> wrote in message

      news:cc0ekp$80v$2.db2.todaysummary.com.hanover.torolab.ibm.com...

      > I usually do:

      > T1 EXCEPT T2

      > UNION ALL

      > T2 EXCEXT T1

      EXCEPT ALL is better if your tables have no keys.

      and don't forget the brackets otherwise you will be doing

      (((T1 EXCEPT ALL T2) UNION ALL T2) EXCEPT ALL T1) !

      with a(a) as (values (1), (1),(2))

      , b(b) as (values(1),(2),(2))

      (select * from a

      except ALL

      select * from b)

      union all

      (select * from b

      except ALL

      select * from a)

      ;

      1

      --

      2

      1

      2 record(s) selected.

      with a(a) as (values (1), (1), (2))

      , b(b) as (values(2),(2),(1))

      (select * from a

      except

      select * from b)

      union all

      (select * from b

      except

      select * from a)

      ;

      1

      --

      0 record(s) selected.

      Regards

      Paul Vernon

      Business Intelligence, IBM Global Services

      #4; Sun, 25 May 2008 09:51:00 GMT
    • Hello,

      Can BLOB field of DB2 store TEXT string data ?

      Thanks inadvacne !

      Shum

      www.mingyik.com

      "Paul Vernon" <paul.vernon.db2.todaysummary.com.ukk.ibmm.comm> bl

      news:cc18er$1r0k$1.db2.todaysummary.com.gazette.almaden.ibm.com g...

      > "Serge Rielau" <srielau.db2.todaysummary.com.ca.eye-be-em.com> wrote in message

      > news:cc0ekp$80v$2.db2.todaysummary.com.hanover.torolab.ibm.com...

      > EXCEPT ALL is better if your tables have no keys.

      > and don't forget the brackets otherwise you will be doing

      > (((T1 EXCEPT ALL T2) UNION ALL T2) EXCEPT ALL T1) !

      >

      > with a(a) as (values (1), (1),(2))

      > , b(b) as (values(1),(2),(2))

      > (select * from a

      > except ALL

      > select * from b)

      > union all

      > (select * from b

      > except ALL

      > select * from a)

      > ;

      > 1

      > --

      > 2

      > 1

      > 2 record(s) selected.

      > with a(a) as (values (1), (1), (2))

      > , b(b) as (values(2),(2),(1))

      > (select * from a

      > except

      > select * from b)

      > union all

      > (select * from b

      > except

      > select * from a)

      > ;

      > 1

      > --

      > 0 record(s) selected.

      > Regards

      > Paul Vernon

      > Business Intelligence, IBM Global Services

      >

      #5; Sun, 25 May 2008 09:52:00 GMT
    • yes , we do have unqiue index on one of the columns.

      "Mark A" <nobody.db2.todaysummary.com.nowhere.com> wrote in message news:<cuGEc.140$B%4.32996.db2.todaysummary.com.news.uswest.net>...

      > "xixi" <dai_xi.db2.todaysummary.com.yahoo.com> wrote in message

      > news:c0f33a17.0406301339.42c5db4d.db2.todaysummary.com.posting.google.com...

      > Do the tables have a primary key or unique index (with no null values)?

      #6; Sun, 25 May 2008 09:53:00 GMT
    • if i have null value in one of the table column, what should i do?
      #7; Sun, 25 May 2008 09:55:00 GMT
    • if i have null value in one of the table column and don't want to

      compare it, what should i do?

      "Paul Vernon" <paul.vernon.db2.todaysummary.com.ukk.ibmm.comm> wrote in message news:<cc18er$1r0k$1.db2.todaysummary.com.gazette.almad

      en.ibm.com>...

      > "Serge Rielau" <srielau.db2.todaysummary.com.ca.eye-be-em.com> wrote in message

      > news:cc0ekp$80v$2.db2.todaysummary.com.hanover.torolab.ibm.com...

      > EXCEPT ALL is better if your tables have no keys.

      > and don't forget the brackets otherwise you will be doing

      > (((T1 EXCEPT ALL T2) UNION ALL T2) EXCEPT ALL T1) !

      >

      > with a(a) as (values (1), (1),(2))

      > , b(b) as (values(1),(2),(2))

      > (select * from a

      > except ALL

      > select * from b)

      > union all

      > (select * from b

      > except ALL

      > select * from a)

      > ;

      > 1

      > --

      > 2

      > 1

      > 2 record(s) selected.

      > with a(a) as (values (1), (1), (2))

      > , b(b) as (values(2),(2),(1))

      > (select * from a

      > except

      > select * from b)

      > union all

      > (select * from b

      > except

      > select * from a)

      > ;

      > 1

      > --

      > 0 record(s) selected.

      > Regards

      > Paul Vernon

      > Business Intelligence, IBM Global Services

      #8; Sun, 25 May 2008 09:56:00 GMT
    • Shum [MingYik] wrote:

      > Hello,

      > Can BLOB field of DB2 store TEXT string data ?

      Yes, it can. But DB2 will treat it like binary data.

      Knut Stolze

      Information Integration

      IBM Germany / university of Jena

      #9; Sun, 25 May 2008 09:57:00 GMT