how to compare two tables difference

[xixi]

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)

[Mark A]

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@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)

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

[Lorne Sunley]

On Wed, 30 Jun 2004 21:39:08 UTC, da****@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

[Serge Rielau]

I usually do:
T1 EXCEPT T2
UNION ALL
T2 EXCEXT T1

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tokunaga T.]

da****@yahoo.com (xixi) wrote in message news:<c0**************************@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)

You can use EXCEPT operator.

[Paul Vernon]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:cc**********@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

[Shum [MingYik]]

Hello,

Can BLOB field of DB2 store TEXT string data ?

Thanks inadvacne !

Shum
www.mingyik.com
"Paul Vernon" <pa*********@ukk.ibmm.comm> ¦b¶l¥ó
news:cc***********@gazette.almaden.ibm.com ¤¤¼¶¼g...

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:cc**********@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

[xixi]

yes , we do have unqiue index on one of the columns.

"Mark A" <no****@nowhere.com> wrote in message news:<cu*****************@news.uswest.net>...

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@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)

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

[db2group88]

if i have null value in one of the table column, what should i do?

[db2group88]

if i have null value in one of the table column and don't want to
compare it, what should i do?

"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<cc***********@gazette.almaden.ibm.com>...

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:cc**********@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

[Knut Stolze]

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