SQLCODE -811 — more than one row returned

What happened

A program that has worked for years suddenly fails on a SELECT ... INTO, often after a data change, conversion or new business rule.

DSNT408I SQLCODE = -811, ERROR:  THE RESULT OF AN EMBEDDED SELECT
         STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE
         STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF
         A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
DSNT418I SQLSTATE   = 21000 SQLSTATE RETURN CODE

What it means

SELECT ... INTO can only return one row into host variables. A subquery used with =, <, > and similar must return one value. When more than one qualifies, Db2 returns -811 with SQLSTATE 21000. Do not rely on the host variables after a -811.

This code has no useful SQLERRMC tokens naming an object. You identify the statement from the program: its error routine should log the statement number or paragraph name.

Typical causes

• The WHERE clause does not cover the full unique key.
• Data now holds duplicates the design never expected, for example after a conversion or a new product type.
• History or effective-dated rows where the program forgot the date condition.
• A subquery against a child table that now has several rows per parent.
• Programs using -811 deliberately as an "exists" test, which costs more than a proper check.

Symptoms

It fails for particular keys only, every time those keys are read. Different from -803 (a write is rejected) and from +100 (no row found). Some old programs treat -811 as success; check the code before calling it a new problem.

Where to look

• The program's error output: SQLCODE, paragraph, statement number and key values.
• The source or compile listing to read the exact SQL.
• The tables, using SPUFI or a query tool, to count matching rows.
• The catalog to see which unique indexes exist on the table.

How to diagnose

1. Find the failing statement from the program log and listing.
2. Re-run the same SELECT in a query tool with the logged key values, using COUNT(*).
3. Look at the extra rows. Are they valid business data or bad data?
4. Compare the predicate with the table's unique key columns.
5. Ask when and how the extra rows arrived.

How to fix

If the extra rows are wrong, the data owner corrects them and the job is rerun. If they are valid, change the SQL: add the missing predicate, use a cursor and process all rows, or pick one row deliberately with ORDER BY ... FETCH FIRST 1 ROW ONLY. For existence checks use EXISTS or FETCH FIRST 1 ROW ONLY instead of relying on -811.

To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Write singleton SELECTs only against a full unique key.
• Back business uniqueness with a unique index, so bad data fails at insert time instead.
• Include effective-date conditions for history tables.
• Test with realistic data, including multiple child rows.

Production considerations

Interview question

What causes SQLCODE -811 and how would you fix it properly?