MainframeKit

-407

SQLSTATE 23502NEGATIVE SQLCODE

The statement tries to put a null into a column defined NOT NULL. Often a null indicator set to -1, or a NOT NULL column left out of an INSERT.

MOST LIKELY

A host variable's null indicator is negative for a NOT NULL column.

FIRST CHECK

Find the column name in the message or SQLCA tokens, if present.

TRY IT ▸

MainframeKit assistant

What it means

A column defined NOT NULL must always hold a value. When an INSERT, UPDATE or SET would leave it null, Db2 rejects the statement with -407 and SQLSTATE 23502. Depending on the statement, the SQLCA may carry the name of the column.

DEFAULT counts too: asking for the default on a NOT NULL column that has no WITH DEFAULT clause gives the same error. A ROWID column also cannot receive a null.

Typical causes

  • A host variable's null indicator is negative for a NOT NULL column.
  • An INSERT column list omits a NOT NULL column that has no default.
  • An INSERT through a view that does not include a NOT NULL, no-default column of the base table.
  • UPDATE ... SET col = NULL, or SET col = DEFAULT on a NOT NULL column without WITH DEFAULT.
  • A trigger assigns null to a NOT NULL transition variable.
  • A subselect in INSERT ... SELECT or UPDATE returns null for that column.

How to fix it

  1. Find the column name in the message or SQLCA tokens, if present.
  2. Check the table definition in SYSIBM.SYSCOLUMNS: NULLS and DEFAULT for each column.
  3. Make sure every NOT NULL column without a default is in the INSERT column list with a real value.
  4. Check null indicators just before the statement; set them to 0 when the value is present.
  5. For a source query that can return nulls, supply a value with COALESCE.
  6. For views, insert through a view that covers the required base-table columns.

Example

Illustrative only; names and values are invented.

DSNT408I SQLCODE = -407, ERROR:  AN UPDATE, INSERT, OR SET VALUE IS NULL,
         BUT THE OBJECT COLUMN CUST_NAME CANNOT CONTAIN NULL VALUES
DSNT418I SQLSTATE   = 23502 SQLSTATE RETURN CODE