The quickest and the easiest way I could think was to do the following:
1. Take the table offline and rename it
db2 lock table EMP in exclusive mode
db2 rename table EMP to EMP_ORIG
2. Create a new table EMP and existing indexes exactly same as EMP_ORIG with no data (db2look)
3. Create the new unique index on EMP
4. LOAD data into EMP from EMP_ORIG
db2 “declare EMPCUR of cursor for select * from EMP_ORIG”
db2 “load from EMPCUR of cursor insert into EMP nonrecoverable”
This LOAD operation will only insert those rows from EMP_ORIG into EMP which satisfy the unique indexes defined on the EMP table and will reject any duplicate rows.
An important point to note here is that there is no control on which rows are rejected by the LOAD and which ones are inserted in the EMP table. If you want to control which rows get selected for insertion, you will have to insert a suitable WHERE clause with the SELECT while declaring the CURSOR for LOAD.
If your requirement is to obtain unique rows for all columns in a table, you can simply use the DISTINCT clause to pull out the unique rows:
db2 "select distinct * from EMP"
Here is another easy way of removing duplicates using "OLAP Style SQL". This tip comes from Fred Sobotka. Thanks Fred!
Let's assume columns c1 and c2 are the ones you want to promote to become a unique index on table t1, and that you have a way of picking which row among each set of duplicates you wish to preserve (such as whichever row has the highest value of c3)
DELETE FROM ( SELECT ROWNUMBER() OVER (PARTITION BY c1, c2 ORDER BY c3 DESC) AS rnum FROM t1) WHERE rnum > 1 ;
| Comments |
|
|
|||||||||||
|
|||||||||||
Powered by !JoomlaComment 3.25
