[DB2 LUW] Single multiple key records

Goal

Say you have a table without primary key imposed but there are records sporting the same value over a select number of attributes and want to only one record of those to remain within the table, you cannot use a function like ROWID in Oracle because DB2 LUW does not expose such a function/(pseudo) column (see how-to-delete-multiple-records-of-key)

Solution

Use sort of subquery, i.e.

delete from (select rownumber() over (partition by <list of attributes over which uniqueness is supposed to be>) as RN
               from <table>) as T
      where T.RN > 1;

Example

delete from (select rownumber() over (partition by TABLE_NAME,
                                                   ROW_SCAN_NUMBER,
                                                   DWH_REG_TIME) as RN
               from ADW_ETL.ETL_OGG_ROW_SCAN) as T
      where T.RN > 1;