DB2 SQL Error SQLCODE 407 SQLSTATE 23502 | Explained

DB2 SQL Error SQLCODE 407 SQLSTATE 23502 means a column object specified as NOT NULL is receiving a NULL value. It usually occurs when trying to perform an INSERT, UPDATE, or SET operation on an object. However, the ALTER statement can sometimes trigger the same issue.

What is SQLCODE =- 407 in DB2?

The SQLCODE = -407 in DB2 corresponds to an error caused by either of the following:

1. While performing INSERT or UPDATE, a NULL value got specified for a column that has the definition as NOT NULL.
2. A SET transition statement specified a NULL value for the column declared NOT NULL. But the WITH DEFAULT clause was missing from the table definition.
3. An UPDATE or INSERT statement used default value, but the definition of the object columns was NOT NULL without using the WITH DEFAULT clause. As a result, the column will not accept a default NULL value through the insert or update operation.
4. If a column gets specified as NOT NULL but does not have WITH DEFAULT in the base table definition, it gets eliminated by the view for INSERT.
5. A column declared NOT NULL and not having the WITH DEFAULT definition gets omitted by the column name list for INSERT.
6. The provided value for the ROWID column is NULL.

By analyzing the error message, we can better understand the issue. Depending on the syntax of the SQL statement related to the error, the ‘column-name’ could potentially be returned in the SQLCA. You can check the object table definition to find the affected attribute and fix the corresponding SQL statement.

What Is Error Code 23502 in DB2?

SQLSTATE 23502 in DB2 represents the same error as SQLCODE -407, the insert or update value being NULL. However, the problem might be due to an ALTER statement if your query looks like this:

  ALTER TABLE BLP.COUNTER_EXTRACTION_REQUEST 
  ALTER COLUMN SCHEME_ID SET NOT NULL

In this case, the meaning of the error code is that the column has NULL values already, for which you cannot add the NOT NULL statement right away. If you want to impose the NOT NULL constraint, eliminate the existing NULL values.

You can find additional information in the CSV log file’s error description.

How to Resolve SQLCODE 407?

There are two simple ways to resolve the problem that resulted in the SQLCODE 407 error with SQLSTATE 23503. 

Solution 1

Step 1: Drop the column where you want to insert or update the NULL value. 

  ALTER TABLE employees DROP COLUMN salary;
  INSERT INTO employees (id, name, age, address) VALUES (1, 'John Doe', 30, '123 Main St');
-- or
  UPDATE employees SET salary = NULL WHERE id = 1;

Step 2: Recreate the column using the NOT NULL specification.

  ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2) NOT NULL;
  DESCRIBE employees;

Solution 2

Update the column = ‘default value’ with column date being set to NULL and then change to NULL.

  SELECT * FROM data_table;
  UPDATE data_table SET column_date = 'default value';
  UPDATE data_table SET column_date = NULL;
-- Just to demonstrate changing it to NULL, though it's already NULL:
  UPDATE data_table SET column_date = NULL;
  SELECT * FROM data_table;

Conclusion

The SQLCODE 407 SQLSTATE 23502 results from DB2 trying to insert a NULL value into a non-nullable column. Generally, the error description will also show all non-nullable columns causing the issue. So, you can go ahead and fix those columns to get rid of this error.