DB2 duplicate, gap and island patterns

Start by defining the question

The SQL is usually shorter than the definition. A duplicate is not simply “two rows that look similar.” You must name the columns that represent the same business fact. Two payment rows might be duplicates when customer, invoice, amount, and payment date match, even though their generated payment IDs differ. In another system, two payments for the same invoice are legitimate. The database cannot invent that rule.

Gaps and islands also need a rule. Are sequence numbers expected to increase by exactly one? Do weekends count as date gaps? May two service periods touch, overlap, or sit one day apart and still belong to the same island? Write that rule before writing SQL. Throughout the examples, imagine these simplified tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE PAYMENT_EVENT
 (EVENT_ID      BIGINT       NOT NULL,
  CUSTOMER_ID   INTEGER      NOT NULL,
  INVOICE_NO    CHAR(12)     NOT NULL,
  AMOUNT        DECIMAL(11,2) NOT NULL,
  EVENT_TS      TIMESTAMP    NOT NULL,
  LOAD_TS       TIMESTAMP    NOT NULL,
  PRIMARY KEY (EVENT_ID));
 
CREATE TABLE ACCOUNT_EVENT
 (ACCOUNT_ID    INTEGER      NOT NULL,
  EVENT_SEQ     INTEGER      NOT NULL,
  EVENT_DATE    DATE         NOT NULL,
  EVENT_ID      BIGINT       NOT NULL,
  PRIMARY KEY (EVENT_ID));

Detect duplicate business keys with GROUP BY

The most direct duplicate report groups rows by the business-key columns. COUNT counts the rows in each group, and HAVING filters after grouping. A WHERE predicate cannot contain this group count because WHERE filters input rows before groups exist.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT CUSTOMER_ID,
       INVOICE_NO,
       AMOUNT,
       DATE(EVENT_TS) AS EVENT_DATE,
       COUNT(*)       AS OCCURRENCES
FROM PAYMENT_EVENT
WHERE EVENT_TS >= TIMESTAMP('2026-08-01-00.00.00')
  AND EVENT_TS <  TIMESTAMP('2026-09-01-00.00.00')
GROUP BY CUSTOMER_ID,
         INVOICE_NO,
         AMOUNT,
         DATE(EVENT_TS)
HAVING COUNT(*) > 1
ORDER BY OCCURRENCES DESC,
         CUSTOMER_ID,
         INVOICE_NO;

This returns one summary row per duplicated key, not the original rows. Notice that the date range is in WHERE. It reduces the rows before grouping and can influence access path selection. IBM documents that HAVING predicates are not used for access path selection, so put ordinary row-level restrictions in WHERE whenever that preserves the intended result.

Nulls and duplicate definitions

GROUP BY places null values into groups, so multiple rows with null in a grouped column can be reported together. Decide whether “unknown equals unknown” is correct for the audit. Do not casually wrap indexed columns in COALESCE to change the rule; an expression can alter both semantics and access-path choices. If null should disqualify a row, say so with an explicit WHERE predicate.

Label every duplicate with ROW_NUMBER

A summary count tells you that duplicates exist. Deduplication needs the individual row identifiers and a rule for the survivor. ROW_NUMBER starts at one inside each PARTITION and follows the window ORDER BY. In this example, the earliest loaded row survives. EVENT_ID is the final unique tie-breaker, so two rows with the same LOAD_TS still produce a repeatable answer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH RANKED_PAYMENT AS
(
  SELECT EVENT_ID,
         CUSTOMER_ID,
         INVOICE_NO,
         AMOUNT,
         EVENT_TS,
         LOAD_TS,
         ROW_NUMBER() OVER
         (
           PARTITION BY CUSTOMER_ID,
                        INVOICE_NO,
                        AMOUNT,
                        DATE(EVENT_TS)
           ORDER BY LOAD_TS,
                    EVENT_ID
         ) AS DUPLICATE_NUMBER
  FROM PAYMENT_EVENT
)
SELECT EVENT_ID,
       CUSTOMER_ID,
       INVOICE_NO,
       AMOUNT,
       EVENT_TS,
       DUPLICATE_NUMBER
FROM RANKED_PAYMENT
WHERE DUPLICATE_NUMBER > 1
ORDER BY CUSTOMER_ID,
         INVOICE_NO,
         DUPLICATE_NUMBER;

Db2 does not allow an OLAP specification directly in WHERE. The common table expression computes ROW_NUMBER first; the outer query can then filter its result. IBM uses the same nested-table principle in its ranking examples. Also remember that ORDER BY inside OVER controls the calculation, not the final presentation. The outer ORDER BY controls the order returned to the application.

Choose a survivor deliberately

• Keep the earliest accepted row with ORDER BY LOAD_TS, EVENT_ID.
• Keep the latest correction with ORDER BY CHANGE_TS DESC, EVENT_ID DESC.
• Prefer a trusted source using a CASE expression, then timestamp and unique ID.
• Never rely on physical table order. A relational table has no promised first row.

If every ORDER BY value ties, the row-number assignment among those tied rows is not deterministic. That is dangerous in cleanup code: separate runs could nominate different survivors. Finish the ordering with a stable unique value. If the table has no key that identifies one physical row, fix that design or stage the candidates with a generated identifier before attempting removal.

Remove duplicates safely

Treat duplicate removal as a controlled data-change process, not as a clever one-line DELETE. First run the ranked SELECT and preserve its output. Confirm that the chosen survivor matches the business rule. Store candidate EVENT_ID values in an approved staging or temporary table, record an audit count, and delete by those keys in a recoverable unit of work. For a large cleanup, commit in restartable business batches rather than holding locks for the entire table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Step 1: populate an approved work table from the ranked query.
-- Its primary key prevents the same candidate from being processed twice.
INSERT INTO SESSION.DUP_PAYMENT_DELETE (EVENT_ID)
SELECT EVENT_ID
FROM
(
  SELECT EVENT_ID,
         ROW_NUMBER() OVER
         (
           PARTITION BY CUSTOMER_ID, INVOICE_NO,
                        AMOUNT, DATE(EVENT_TS)
           ORDER BY LOAD_TS, EVENT_ID
         ) AS RN
  FROM PAYMENT_EVENT
) AS D
WHERE RN > 1;
 
-- Step 2: inspect and reconcile SESSION.DUP_PAYMENT_DELETE.
-- Step 3: delete only the reviewed identifiers.
DELETE FROM PAYMENT_EVENT AS P
WHERE EXISTS
(
  SELECT 1
  FROM SESSION.DUP_PAYMENT_DELETE AS D
  WHERE D.EVENT_ID = P.EVENT_ID
);

A declared global temporary table must already be declared for the session, and its commit behavior must match the process. Production shops often use a permanent work table instead because it supports restart and audit requirements. Before running any destructive statement, test rollback, foreign-key effects, triggers, logging volume, lock escalation risk, and the application outage or concurrency plan.

Prevention is better than periodic cleanup. If the business key truly must be unique, enforce it with a UNIQUE constraint or unique index after existing data is repaired. If uniqueness is conditional or time-dependent, enforce the rule in the ingestion design and retain an idempotency key supplied by the source.

Detect gaps with LAG and LEAD

Conceptually, LAG reads a value from a preceding row and LEAD reads one from a following row in the window order. That makes a gap test easy: compare the current sequence with the previous sequence plus one, or compare the next sequence with the current sequence plus one.

Db2 for z/OS accuracy note: current IBM Db2 13 documentation marks LAG and LEAD as passthrough-only expressions. They cannot run natively in Db2 for z/OS without acceleration. The following statement is appropriate only when your environment routes and supports that accelerated expression. Do not copy a Db2 LUW example into a native z/OS package and assume the function is available.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Requires an acceleration path that supports LAG.
WITH PREVIOUS_VALUE AS
(
  SELECT ACCOUNT_ID,
         EVENT_SEQ,
         LAG(EVENT_SEQ, 1) OVER
           (PARTITION BY ACCOUNT_ID
            ORDER BY EVENT_SEQ) AS PREVIOUS_SEQ
  FROM ACCOUNT_EVENT
)
SELECT ACCOUNT_ID,
       PREVIOUS_SEQ + 1 AS GAP_START,
       EVENT_SEQ - 1    AS GAP_END,
       EVENT_SEQ - PREVIOUS_SEQ - 1 AS MISSING_COUNT
FROM PREVIOUS_VALUE
WHERE PREVIOUS_SEQ IS NOT NULL
  AND EVENT_SEQ > PREVIOUS_SEQ + 1
ORDER BY ACCOUNT_ID, GAP_START;

LEAD expresses the same boundary from the other side. Replace the LAG expression withLEAD(EVENT_SEQ, 1), then report a gap when NEXT_SEQ is greater than EVENT_SEQ plus one. LAG and LEAD accept an offset; an omitted offset means one. When an offset runs beyond its partition, the result is null unless a default is supplied. Always partition by the entity whose sequence is independent.

Native gap detection with ROW_NUMBER and a self-join

When no accelerator is available, number the rows natively and join each row to the preceding row number. This takes two logical stages because an OLAP result cannot be referenced inside another expression at the same query level. DISTINCT removes repeated sequence values first so duplicates do not create false pairings.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH DISTINCT_EVENT AS
(
  SELECT DISTINCT ACCOUNT_ID, EVENT_SEQ
  FROM ACCOUNT_EVENT
),
NUMBERED_EVENT AS
(
  SELECT ACCOUNT_ID,
         EVENT_SEQ,
         ROW_NUMBER() OVER
           (PARTITION BY ACCOUNT_ID
            ORDER BY EVENT_SEQ) AS RN
  FROM DISTINCT_EVENT
),
PAIRED_EVENT AS
(
  SELECT CURR.ACCOUNT_ID,
         PREV.EVENT_SEQ AS PREVIOUS_SEQ,
         CURR.EVENT_SEQ AS CURRENT_SEQ
  FROM NUMBERED_EVENT AS CURR
  LEFT JOIN NUMBERED_EVENT AS PREV
    ON PREV.ACCOUNT_ID = CURR.ACCOUNT_ID
   AND PREV.RN = CURR.RN - 1
)
SELECT ACCOUNT_ID,
       PREVIOUS_SEQ + 1 AS GAP_START,
       CURRENT_SEQ - 1  AS GAP_END,
       CURRENT_SEQ - PREVIOUS_SEQ - 1 AS MISSING_COUNT
FROM PAIRED_EVENT
WHERE PREVIOUS_SEQ IS NOT NULL
  AND CURRENT_SEQ > PREVIOUS_SEQ + 1
ORDER BY ACCOUNT_ID, GAP_START;

This reports internal gaps only. It cannot know that values are missing before the first stored row or after the last stored row unless you provide expected boundaries. To find all missing calendar dates, join against an approved calendar table. A calendar table correctly represents weekends, holidays, accounting periods, and business-day rules; subtracting one calendar day is not the same as finding a missing business day.

Build islands with the row-number difference

An island is a maximal consecutive run. For integer sequences, subtract ROW_NUMBER from the sequence value. Inside a run, both numbers increase by one, so the difference stays constant. After a gap, the sequence jumps while ROW_NUMBER moves only one step, creating a new group key.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH DISTINCT_EVENT AS
(
  SELECT DISTINCT ACCOUNT_ID, EVENT_SEQ
  FROM ACCOUNT_EVENT
),
ISLAND_KEY AS
(
  SELECT ACCOUNT_ID,
         EVENT_SEQ,
         EVENT_SEQ -
           ROW_NUMBER() OVER
             (PARTITION BY ACCOUNT_ID
              ORDER BY EVENT_SEQ) AS ISLAND_GROUP
  FROM DISTINCT_EVENT
)
SELECT ACCOUNT_ID,
       MIN(EVENT_SEQ) AS ISLAND_START,
       MAX(EVENT_SEQ) AS ISLAND_END,
       COUNT(*)       AS VALUE_COUNT
FROM ISLAND_KEY
GROUP BY ACCOUNT_ID, ISLAND_GROUP
ORDER BY ACCOUNT_ID, ISLAND_START;

Suppose the stored values are 10, 11, 12, 16, and 17. Their row numbers are 1 through 5. The differences are 9, 9, 9, 12, and 12, which produces two islands: 10–12 and 16–17. The group key is an implementation detail, not a permanent business identifier. Insert an earlier row and the row numbers can change, so expose island start and end instead.

Where the difference pattern does not fit

The subtraction pattern assumes a fixed one-unit step. It does not directly solve irregular timestamps, overlapping effective-date periods, tolerated pauses, or business calendars. It also needs duplicate values removed or separately classified. For those cases, define a break flag and cumulatively add the flags.

Build islands with cumulative break flags

The flexible method has three stages. First, obtain the previous value. Second, mark the first row and every row that starts after a break with 1; mark continuing rows with 0. Third, calculate a running SUM of that flag. The running total becomes the island number.

The example below stays native to Db2 for z/OS by using MAX over a window frame containing exactly one preceding row. Since DISTINCT_EVENT gives one row per date, that frame represents the previous date. Separate common table expressions are required: IBM does not permit one OLAP specification to be an argument of another.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
WITH DISTINCT_EVENT AS
(
  SELECT DISTINCT ACCOUNT_ID, EVENT_DATE
  FROM ACCOUNT_EVENT
),
PREVIOUS_DATE AS
(
  SELECT ACCOUNT_ID,
         EVENT_DATE,
         MAX(EVENT_DATE) OVER
         (
           PARTITION BY ACCOUNT_ID
           ORDER BY EVENT_DATE
           ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
         ) AS PREVIOUS_EVENT_DATE
  FROM DISTINCT_EVENT
),
BREAK_FLAG AS
(
  SELECT ACCOUNT_ID,
         EVENT_DATE,
         CASE
           WHEN PREVIOUS_EVENT_DATE IS NULL THEN 1
           WHEN EVENT_DATE > PREVIOUS_EVENT_DATE + 1 DAY THEN 1
           ELSE 0
         END AS STARTS_NEW_ISLAND
  FROM PREVIOUS_DATE
),
ISLAND_NUMBER AS
(
  SELECT ACCOUNT_ID,
         EVENT_DATE,
         SUM(STARTS_NEW_ISLAND) OVER
         (
           PARTITION BY ACCOUNT_ID
           ORDER BY EVENT_DATE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS ISLAND_NO
  FROM BREAK_FLAG
)
SELECT ACCOUNT_ID,
       ISLAND_NO,
       MIN(EVENT_DATE) AS ISLAND_START,
       MAX(EVENT_DATE) AS ISLAND_END,
       COUNT(*)        AS ACTIVE_DAYS
FROM ISLAND_NUMBER
GROUP BY ACCOUNT_ID, ISLAND_NO
ORDER BY ACCOUNT_ID, ISLAND_START;

Change the CASE rule to change the meaning of an island. For example, use PREVIOUS_EVENT_DATE plus three days to tolerate a two-day pause. For overlapping date ranges, the correct previous boundary is often the greatest ending date seen so far, not simply the end date on the preceding row. That advanced version uses a running MAX ending at one row preceding, then compares the current start to that boundary.

The explicit ROWS frame matters. With duplicate ordering values, the default RANGE behavior can treat peers as a group. Removing duplicate dates and specifying ROWS makes the intended row-by-row accumulation clear. A unique tie-breaker is still required whenever distinct physical rows must be processed in a defined order.

Deterministic ordering checklist

• Put independent entities in PARTITION BY, such as ACCOUNT_ID or CUSTOMER_ID.
• Put the logical sequence in the window ORDER BY, such as EVENT_SEQ or EVENT_DATE.
• Add a stable unique final key when tied rows must be distinguished.
• State NULLS FIRST or NULLS LAST when nullable sort keys are meaningful.
• Add an outer ORDER BY when the application requires ordered output.

A primary key does not automatically break ties unless it appears in the window ORDER BY. Likewise, an outer ORDER BY does not change row numbers that were already computed. Treat calculation order and display order as separate requirements.

Performance and index caveats

These patterns can scan, sort, and group many rows. Reduce input early with valid WHERE predicates, especially date or partition-range predicates. Keep catalog statistics current with the site's RUNSTATS strategy, then use EXPLAIN to inspect the chosen access path. PLAN_TABLE columns such as SORTN_GROUPBY, SORTN_ORDERBY, SORTC_GROUPBY, and SORTC_ORDERBY help identify sorts, but the complete plan and runtime measurements matter.

An index beginning with frequently filtered and grouped columns can help. For example, an index on ACCOUNT_EVENT (ACCOUNT_ID, EVENT_SEQ) aligns with partitioning and sequence access. A duplicate audit might benefit from leading business-key columns followed by the survivor-order columns. However, a wide index increases storage, logging, utility work, and the cost of INSERT, UPDATE, and DELETE. It also does not guarantee that Db2 will avoid a sort; the optimizer may choose a different, cheaper path.

• Filter before grouping when the business question permits it.
• Avoid functions on predicate columns when a direct range predicate is equivalent.
• Expect large DISTINCT and window operations to use the work file database.
• Test skewed partitions; one very large account can dominate elapsed time.
• Process cleanup in restartable units to control locks, logs, and recovery exposure.
• Verify accelerator eligibility before choosing a passthrough-only expression.

Be careful with DATE(timestamp-column) in a business key. It may be exactly the required duplicate definition, but it is still an expression that can affect access. Use direct timestamp range predicates in WHERE for selection, and keep the expression only where the grouping rule needs it. For frequently executed workloads, consider whether the data model should store the business date explicitly.

Explain it like I'm five

Imagine numbered toy cars lined up on a shelf. If two cars have the same owner, color, and ticket number, they might be duplicates. GROUP BY puts matching cars in the same box, and COUNT tells you when a box has more than one car. ROW_NUMBER puts “keep me” on the first car and “extra” on the rest, but you must give a clear rule for which car is first.

A gap is an empty spot: cars 1, 2, 3, 7, 8 are missing 4 through 6. An island is one unbroken group: 1–3 is one island and 7–8 is another. SQL looks at neighbors or gives each new group a flag. Adding the flags is like counting how many times you had to jump over an empty shelf space.

Exercises

• Define the business key for duplicate payments in your own words. Explain why EVENT_ID should not be part of that GROUP BY.
• Modify the ranked duplicate query to retain the latest LOAD_TS. Add a deterministic tie-breaker and explain it.
• Given sequence values 2, 3, 4, 9, 10, 15, predict GAP_START, GAP_END, and each island. Then verify the result with the SQL patterns.
• Change the cumulative-flag example so dates separated by at most two missing days remain in the same island.
• Write an expected-boundary table for sequence 1 through 20 and report leading, internal, and trailing gaps.
• EXPLAIN a representative query and inspect grouping and ordering sort indicators. Record whether an aligned index changes the plan and elapsed time.

Frequently asked questions