SQLCODE -407: Null value in a NOT NULL column

What it means

A NOT NULL column must always hold a value. When a statement would put a null in it, Db2 stops and makes no change. The message usually gives the column name. IBM notes that, depending on the statement, the name may not be returned.

The null doesn't have to be written as the word NULL. It can come from a negative indicator variable, from an INSERT that leaves out a column with no default, from a subquery that found no row, or from a function or outer join that returned null. Any of these looks the same to Db2.

Db2 for LUW often reports the column differently. Its SQL0407N message can show TBSPACEID=n, TABLEID=n, COLNO=n instead of a name, and you have to look those numbers up in the catalog.

Common causes

• An indicator variable is negative. Either the program set it to -1 on purpose, or it was never initialized and holds a leftover negative value.
• An INSERT leaves out a NOT NULL column that has no default value.
• An INSERT or UPDATE uses the DEFAULT keyword for a NOT NULL column that has no WITH DEFAULT, or an INSERT through a view leaves out such a column of the base table.
• An UPDATE uses a scalar subquery, such as SET REGION = (SELECT ...), and the subquery found no row, so the result is null.
• An INSERT ... SELECT reads from an outer join or uses SUM, MAX or a CASE with no ELSE, and one of those produced a null.
• A Java or .NET program passed a null object, or called setNull, for a parameter that maps to a NOT NULL column.
• An INSERT supplies a null for a ROWID column.

How to find the problem

1. Take the column name from the message. Check its definition in SYSIBM.SYSCOLUMNS. NULLS = 'N' means NOT NULL, and the DEFAULT column shows whether it has a default.
2. If the value comes from a host variable, display its indicator variable just before the statement. Any negative value means null.
3. If the value comes from a subquery, join or function, run that part on its own as a SELECT and look for nulls.
4. On Db2 for LUW, turn the IDs into names with SELECT T.TABSCHEMA, T.TABNAME, C.COLNAME FROM SYSCAT.TABLES T JOIN SYSCAT.COLUMNS C ON C.TABSCHEMA = T.TABSCHEMA AND C.TABNAME = T.TABNAME WHERE T.TBSPACEID = 2 AND T.TABLEID = 7 AND C.COLNO = 3. COLNO counts from 0.

How to fix it

• Set every indicator variable to 0 before the statement, and to -1 only when you really want a null.
• Replace possible nulls in SQL with COALESCE, for example SET REGION = COALESCE((SELECT ...), 'NONE').
• Supply a value for every NOT NULL column in each INSERT, or give the column a default if a fixed value makes sense.
• Fix the input data if a required field arrived empty.
• If the column really should allow nulls, change the table design. Every program that reads it will then need an indicator variable.

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.