c1으로 중복된 데이터를 뺄 경우로 해봤습니다.

db2 "select c1,c2, rownumber() over (partition by c1)  from t1 "

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

db2 "export to t1.ixf of ixf select c1,c2 from (select c1,c2, rownumber() over (partition by c1)rn  from t1)t1 where rn=1 "

db2 "import from t1.ixf of ixf replace_create into t1_new"

db2 rename table t1 to t1_old 
db2 rename table t1_new to t1

t1_old는 확인 후 drop 


row_id로 row를 찾을 수는 없습니다. 행이 모두 일치하는 경우는 위와 같이 olap function을 사용해서 한 row로 export를 받고 중복 데이터를 모두 삭제 후 import를 해주어야 합니다.




 

DELETE FROM (SELECT * FROM Table_Name WHERE ~ FETCH FIRST n ROW(S) ONLY);

test:/home/hena>db2 "select * from var"

AAA           

ABCDEFG       

  3 record(s) selected.

test:/home/hena>db2 "delete from (select * from var where AAA='ABCDEFG' fetch first 1 row only)"

test:/home/hena>db2 "select * from var"                                                        

AAA           

ABCDEFG       

  2 record(s) selected.

test:/home/hena>

fetch first, olap 다 되네요 ^^



db2 "select * from t1 where c1=3 and c2=2"

C1          C2        
----------- -----------
          3           2
          3           2
          3           2
          3           2
          3           2

db2 "delete from (select t1.*,rownumber() over(partition by c1,c2)rn from t1 where c1=3 and c2=2) where rn>1 "