SQLCODE -840: Too many items in a select or insert list

What it means

Db2 for z/OS caps the number of items one statement can return or insert at 750. The count covers every column and expression in the SELECT list, every column in an INSERT list, the items in a MERGE, and the columns an UNNEST call returns. The check happens when Db2 prepares or binds the statement, so no rows are read.

The limit is easy to hit by accident with SELECT *. A join of three tables returns every column of all three, so three wide tables can pass 750 together even though none of them does on its own.

The limit differs by platform. Db2 for LUW uses SQL0840N with the same SQLSTATE and allows 1,012 items. Db2 for i allows 8,000.

Common causes

• SELECT * over a join of several wide tables.
• A view that is itself built on SELECT * of a join, queried with SELECT * again.
• A reporting or ETL tool, or an ORM, generating a statement that lists every column of every table involved.
• An INSERT ... SELECT that copies a very wide row set into a staging table.
• Code that builds a column list dynamically and repeats columns by mistake.

How to find the problem

1. Get the full statement text. For static SQL, look in the program or in SYSIBM.SYSPACKSTMT. For dynamic SQL, take it from the application or tool log.
2. Count the columns of each table or view in the FROM clause with SELECT TBNAME, COUNT(*) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'PAYROLL' AND TBNAME IN ('EMPLOYEE','PAYHIST') GROUP BY TBNAME.
3. Add the expressions in the SELECT list to the total. If the sum passes 750, that's the statement Db2 rejected.

How to fix it

• List only the columns the program uses instead of SELECT *.
• Split the query into two statements, each returning part of the columns, joined on the key in the program if needed.
• Change the tool or ORM mapping so it doesn't select every column of every joined table.
• For a wide view, create a narrower view with the columns that reports actually use.

What it looks like

DSNT408I SQLCODE = -840, ERROR:  TOO MANY ITEMS RETURNED IN A SELECT, INSERT LIST, FROM
         MERGE, OR FROM UNNEST
DSNT418I SQLSTATE   = 54004 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.