If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

select * from <tab_name> where <index_col> in (select <index_col> from <tab_name> group by <index_col> having count(*) > 1)

For example:

test@p6db2serv /home/test > db2 "select * from t1"

C1 C2
----------- -----------
1 1
1 2
2 1
2 2
3 1
4 1

6 record(s) selected.

test@p6db2serv /home/test > db2 "create unique index i1 on t1 (c1)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0603N A unique index cannot be created because the table contains data
that would result in duplicate index entries. SQLSTATE=23515

test@p6db2serv /home/test > db2 "select * from t1 where c1 in (select c1 from t1 group by c1 having count(*) > 1)"

I think the issue with load / exception table is that db2 will load the first duplicate record and send the rest of duplicates to the exception table. So, the exception table will contain all duplicates expect for the one db2 was able to load.

I modified my example to create a unique index on two columns and display all duplicate records.

Example:

test@p6db2serv /home/test > db2 "select * from t1"

C1 C2 C3
----------- ----------- -----------
1 1 1
1 1 1
1 2 2

3 record(s) selected.

test@p6db2serv /home/test > db2 "create unique index i1 on t1 (c1,c2)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0603N A unique index cannot be created because the table contains data
that would result in duplicate index entries. SQLSTATE=23515

Maybe we are getting lost in understanding exactly what rdba needs. in my experience you create structure first and then load data. I guess it is a the biggest dilema of them all. What came first Chicken or the egg