Db2 pivot, cleanse and upsert patterns

Conditional aggregation: the practical Db2 pivot

A pivot rotates category values from rows into named columns. For a fixed list of categories, Db2 does not need special pivot syntax. Put a searched CASE expression inside an aggregate. The CASE chooses which rows contribute to a column, the aggregate combines them, and GROUP BY chooses the grain of each result row.

1
2
3
4
5
6
7
8
9
SELECT REGION,
       SUM(CASE WHEN SALES_QTR = 1 THEN SALES_AMT ELSE 0 END) AS Q1_SALES,
       SUM(CASE WHEN SALES_QTR = 2 THEN SALES_AMT ELSE 0 END) AS Q2_SALES,
       SUM(CASE WHEN SALES_QTR = 3 THEN SALES_AMT ELSE 0 END) AS Q3_SALES,
       SUM(CASE WHEN SALES_QTR = 4 THEN SALES_AMT ELSE 0 END) AS Q4_SALES,
       SUM(SALES_AMT)                                        AS YEAR_SALES
  FROM SALES_DETAIL
 GROUP BY REGION
 ORDER BY REGION;

The output has one row per REGION because REGION is the grouping key. Every input row is tested four times, once for each output quarter. A quarter that has rows with amounts totaling zero and a quarter that has no rows both display zero in this version because ELSE 0 contributes a numeric zero for nonmatches.

Choose SUM, COUNT, or MAX by meaning

The aggregate is not decoration; it states what should happen if several source rows land in the same output cell.

• SUM adds measures such as sales, hours, or quantities.
• COUNT counts matching rows. Use COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN 1 ELSE 0 END).
• MAX or MIN selects one value when the business rule permits only one non-null candidate, or when highest/lowest is the intended rule.

1
2
3
4
5
6
SELECT WORKDEPT,
       COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END) AS MANAGER_COUNT,
       SUM(CASE WHEN JOB = 'DESIGNER' THEN 1 ELSE 0 END) AS DESIGNER_COUNT,
       MAX(CASE WHEN JOB = 'PRES' THEN LASTNAME END) AS PRESIDENT_NAME
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

Do not write COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 ELSE 0 END). COUNT ignores NULL but counts zero, so that expression counts every row. IBM documents that aggregate functions operate on values after null values are eliminated; COUNT(*) is different because it counts rows.

Static and dynamic pivots

The previous queries are static pivots: Q1 through Q4 and the job names are known when the SQL is prepared. That is usually a strength because applications receive a stable result shape. If categories come from data and change over time, the number and names of SELECT columns must also change. Build that statement in controlled application code, validate category names, use parameter markers for values, and prepare it as dynamic SQL. Never paste untrusted source text directly into identifiers or SQL.

Sometimes rows are the better interface. A result containing REGION, SALES_QTR, and SUM(SALES_AMT) can be consumed by a reporting tool without generating SQL. Pivot only when fixed columns make the consumer simpler.

NULL behavior in pivots and calculations

NULL means unknown or absent, not zero and not an empty string. CASE treats an unknown WHEN condition as not true. SUM ignores null arguments and returns NULL for an empty set of values. These rules make two common pivot forms intentionally different.

1
2
3
4
5
6
7
8
SELECT REGION,
       SUM(CASE WHEN SALES_QTR = 1 THEN SALES_AMT END) AS Q1_UNKNOWN_IF_NONE,
       COALESCE(
         SUM(CASE WHEN SALES_QTR = 1 THEN SALES_AMT END),
         DECIMAL(0, 15, 2)
       ) AS Q1_ZERO_IF_NONE
  FROM SALES_DETAIL
 GROUP BY REGION;

The first expression preserves “no qualifying value” as NULL. The second presents it as zero. Use the first when absence matters, such as an unreported quarter. Use the second when the domain defines no sales rows as zero sales. Match the fallback type to the measure explicitly so precision and scale remain predictable.

NULL also affects arithmetic. SALES_AMT + TAX_AMT is NULL when either operand is NULL. COALESCE(TAX_AMT, 0) is appropriate only if a missing tax truly means no tax. A default can make a report look complete while hiding a source-data defect, so decide the rule before writing COALESCE.

Build a visible data-cleansing stage

Cleansing standardizes equivalent representations; it should not invent facts. Typical safe changes include trimming surrounding spaces, normalizing case for a code, and turning a documented blank sentinel into NULL. A common table expression gives each stage a name and lets you inspect the transformed rows before any target data changes.

1
2
3
4
5
6
7
8
9
10
11
12
WITH CLEAN AS
(
  SELECT LOAD_ID,
         NULLIF(TRIM(CUSTOMER_ID), '')       AS CUSTOMER_ID,
         NULLIF(TRIM(CUSTOMER_NAME), '')     AS CUSTOMER_NAME,
         UPPER(NULLIF(TRIM(STATUS_CODE), '')) AS STATUS_CODE,
         NULLIF(TRIM(EMAIL_ADDRESS), '')     AS EMAIL_ADDRESS,
         CHANGE_TS
    FROM CUSTOMER_STAGE
)
SELECT *
  FROM CLEAN;

IBM defines NULLIF(a, b) as NULL when the two compatible arguments are equal, otherwise a. COALESCE returns the first non-null argument. Combining TRIM and NULLIF therefore changes a value containing only spaces into one consistent missing value. UPPER makes code comparisons stable, but do not uppercase a person's display name or other case-sensitive content without a business requirement.

Map known variants with CASE

CASE is useful when the input contract lists accepted synonyms. Keep an ELSE branch that preserves an invalid marker or produces NULL for later rejection; silently mapping every unknown value to a real status corrupts meaning.

1
2
3
4
5
6
7
CASE UPPER(TRIM(STATUS_CODE))
  WHEN 'A'      THEN 'ACTIVE'
  WHEN 'ACTIVE' THEN 'ACTIVE'
  WHEN 'I'      THEN 'INACTIVE'
  WHEN 'INACT'  THEN 'INACTIVE'
  ELSE NULL
END AS CLEAN_STATUS

Explicit conversion belongs after lexical validation. If an inbound amount is already a numeric column, keep it numeric. If it is text, route malformed values to rejects before applying DECIMAL. Depending on subsystem capabilities and application design, validation can happen in an ingest program, a staging utility, or SQL. The important point is that bad text must not be allowed to abort a production MERGE halfway through an opaque expression.

Validate before changing the target

Validation asks whether a cleaned row is allowed, not merely whether SQL can represent it. Check required keys, domain codes, numeric ranges, dates, and cross-column rules. Produce a reject reason that operators can act on.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH CLEAN AS
(
  SELECT LOAD_ID,
         NULLIF(TRIM(CUSTOMER_ID), '') AS CUSTOMER_ID,
         NULLIF(TRIM(CUSTOMER_NAME), '') AS CUSTOMER_NAME,
         UPPER(NULLIF(TRIM(STATUS_CODE), '')) AS STATUS_CODE,
         CHANGE_TS
    FROM CUSTOMER_STAGE
),
CHECKED AS
(
  SELECT C.*,
         CASE
           WHEN CUSTOMER_ID IS NULL THEN 'MISSING CUSTOMER ID'
           WHEN CUSTOMER_NAME IS NULL THEN 'MISSING CUSTOMER NAME'
           WHEN STATUS_CODE NOT IN ('ACTIVE', 'INACTIVE') THEN 'INVALID STATUS'
           WHEN CHANGE_TS IS NULL THEN 'MISSING CHANGE TIMESTAMP'
           ELSE NULL
         END AS REJECT_REASON
    FROM CLEAN C
)
SELECT LOAD_ID, CUSTOMER_ID, REJECT_REASON
  FROM CHECKED
 WHERE REJECT_REASON IS NOT NULL;

Run and reconcile this query before the upsert. The valid set is the same CHECKED result filtered with REJECT_REASON IS NULL. In a production design, persist rejects with the load identifier and original source record. Counts should balance: received equals valid plus rejected, and MERGE effects should reconcile to the valid count according to your update policy.

Validate source key uniqueness

A MERGE source must have a clear relationship to the target. If two source rows carry the same CUSTOMER_ID, “last one wins” is not a rule until you define what last means. Detect duplicates first.

1
2
3
4
SELECT CUSTOMER_ID, COUNT(*) AS SOURCE_ROWS
  FROM CUSTOMER_STAGE
 GROUP BY CUSTOMER_ID
HAVING COUNT(*) > 1;

Reject duplicates when they indicate a broken feed. If duplicates are expected, either aggregate them by the business key or select one deterministically. IBM's documentation for SQLCODE -788 specifically recommends GROUP BY, ROW_NUMBER, or a corrected search condition when multiple source rows identify one target row.

Deduplicate with a deterministic rule

ROW_NUMBER can select the newest record per business key. Include a tie-breaker that is unique and stable; ordering only by a timestamp is nondeterministic when two events share that timestamp.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH RANKED AS
(
  SELECT S.*,
         ROW_NUMBER() OVER
         (
           PARTITION BY CUSTOMER_ID
           ORDER BY CHANGE_TS DESC, LOAD_SEQUENCE DESC
         ) AS RN
    FROM CUSTOMER_STAGE S
   WHERE REJECT_REASON IS NULL
)
SELECT *
  FROM RANKED
 WHERE RN = 1;

Do not use ROW_NUMBER merely to conceal unexpected duplicates. First decide whether the feed promises snapshots, corrections, or independent events. Snapshot rows might use newest-wins. Events might need SUM or another aggregate. Conflicting corrections might need rejection and manual review.

MERGE: one-statement Db2 upsert

An upsert updates a target row when its key already exists and inserts it when the key is new. MERGE expresses both outcomes in one Db2 statement. The ON clause is identity: keep it limited to stable business-key equality. Put change tests in WHEN MATCHED AND, not in ON, because an extra ON filter can make an existing key appear unmatched and send it to INSERT.

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
MERGE INTO CUSTOMER_DIM AS T
USING
(
  SELECT CUSTOMER_ID,
         CUSTOMER_NAME,
         STATUS_CODE,
         CHANGE_TS
    FROM VALID_DEDUPED_CUSTOMERS
) AS S
   ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED
 AND (T.CUSTOMER_NAME <> S.CUSTOMER_NAME
      OR T.STATUS_CODE <> S.STATUS_CODE
      OR T.CUSTOMER_NAME IS NULL AND S.CUSTOMER_NAME IS NOT NULL
      OR T.CUSTOMER_NAME IS NOT NULL AND S.CUSTOMER_NAME IS NULL
      OR T.STATUS_CODE IS NULL AND S.STATUS_CODE IS NOT NULL
      OR T.STATUS_CODE IS NOT NULL AND S.STATUS_CODE IS NULL)
THEN UPDATE
        SET CUSTOMER_NAME = S.CUSTOMER_NAME,
            STATUS_CODE   = S.STATUS_CODE,
            UPDATED_TS    = CURRENT TIMESTAMP
WHEN NOT MATCHED
THEN INSERT
       (CUSTOMER_ID, CUSTOMER_NAME, STATUS_CODE, CREATED_TS, UPDATED_TS)
     VALUES
       (S.CUSTOMER_ID, S.CUSTOMER_NAME, S.STATUS_CODE,
        CURRENT TIMESTAMP, CURRENT TIMESTAMP);

The null-aware comparisons matter because NULL <> value is unknown, not true. If your Db2 function level and standards provide a preferred null-safe comparison, use it; the expanded predicates above make the three-valued logic visible. Avoiding unchanged updates can reduce logging, trigger activity, and unnecessary downstream change capture.

Db2 treats MERGE as one statement, but one source row must not identify the same target row as another source row for a change. SQLCODE -788 is a source-cardinality defect, not a signal to retry. Fix or deduplicate the source.

MERGE safety under concurrency

MERGE closes the application-level gap created by “SELECT to see whether the row exists, then INSERT or UPDATE.” It does not remove every concurrency concern. Two units of work can still attempt the same new key, wait on locks, encounter a deadlock or timeout, or meet a uniqueness violation depending on timing and the chosen isolation and locking design.

• Enforce the target key. Create a unique constraint or unique index on CUSTOMER_ID. It is the final invariant when concurrent work races.
• Make the source unique. One source row per target key prevents -788 and makes the intended update unambiguous.
• Keep ON stable. Match on immutable identity columns, not a status, timestamp, or value that the UPDATE changes.
• Keep transactions bounded. Process a sensible batch, check SQL diagnostics, and COMMIT at a deliberate unit-of-work boundary.
• Handle expected conflicts. Retry the complete unit of work after a retryable deadlock or timeout according to application policy; do not retry -788 without correcting its data cause.
• Test trigger and RI effects. MERGE performs INSERT or UPDATE actions, so target triggers, referential constraints, temporal behavior, and logging still apply.

Stronger isolation can prevent some anomalies but holds more locks and can reduce concurrency. Choose isolation from the complete transaction's consistency needs, not from the hope that one clause makes an upsert magically race-free. A unique key, deterministic source, and correct error handling remain necessary.

Slowly changing dimension choices

A simple MERGE that overwrites descriptive columns implements a type 1 change: the target keeps only the latest value. If history must be retained, do not squeeze type 2 behavior into an unclear upsert. A type 2 process usually closes the current row by setting its end timestamp and current indicator, then inserts a new version with a new effective range. Db2 system-period temporal tables may also be appropriate when system-time history matches the requirement.

Define whether unchanged source rows should update timestamps. If every match updates UPDATED_TS, the column measures load time rather than business change time. The WHEN MATCHED AND comparison in the example preserves that distinction.

A repeatable production checklist

• Land source records in a staging table with a load ID and sequence.
• Normalize only documented representation differences.
• Classify every row as valid or rejected with an actionable reason.
• Prove one deterministic source row per MERGE target key.
• Preview pivot totals and valid/reject counts before changing data.
• Enforce the target business key with a unique constraint or index.
• Run MERGE in a deliberate unit of work and inspect SQL diagnostics.
• Reconcile source, reject, insert, update, and unchanged counts.

Explain It Like I'm Five

Imagine a box of messy library cards. Pivoting turns a tall pile labeled January, February, and March into one neat card with three boxes. Cleansing erases extra spaces and writes the same status spelling on every card. Validation sends cards with no book number to a “please fix” tray. Deduplication chooses one card when the same book arrived twice, but only after the librarian decides the rule. MERGE looks for the book number: if the book already has a card, it updates it; if not, it adds one. The unique key is the rule that says the catalog can never keep two cards for the same book.

Exercises

• Pivot an ORDER_DETAIL table into one row per CUSTOMER_ID with separate PAID, PENDING, and CANCELLED amount columns. Decide whether missing categories show NULL or zero and explain why.
• Write two correct conditional counts: one with COUNT and one with SUM. Then explain why COUNT with ELSE 0 gives the wrong result.
• Build a CLEAN CTE for product codes that trims spaces, converts codes to uppercase, and changes blank descriptions to NULL.
• Add validation for a required product key, a positive price, and a status restricted to ACTIVE or RETIRED. Return one reject reason per invalid row.
• Use ROW_NUMBER to select the newest source row per product. Add a unique sequence as the final tie-breaker.
• Write a MERGE whose ON clause contains only PRODUCT_ID. Add a null-aware change test, and describe which unique index the target needs.
• Explain why a deadlock might be retried but SQLCODE -788 requires corrected source cardinality.