Assignment of a NULL value to a NOT NULL column (know what is NULL in JPA query) - sqlcode: -407 sqlstate: 23502

Hi,
This is a best and a simple way to know which all columns in your
table contain Null values.
It helped many of us, hope it might help you as well

Correct the SQL statement after examining the object table definition to determine which columns of the table have the NOT NULL attribute and do not have the WITH DEFAULT attribute.

If the value for name is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", you can determine the table name and column name using the following query:

SELECT C.TABSCHEMA, C.TABNAME,
       C.COLNAME
   FROM SYSCAT.TABLES AS T,
        SYSCAT.COLUMNS AS C
   WHERE T.TBSPACEID = n1
   AND T.TABLEID = n2
   AND C.COLNO = n3
   AND C.TABSCHEMA = T.TABSCHEMA
   AND C.TABNAME = T.TABNAME 

Explanation:

One of the following occurred:

• The update or insert value was NULL, but the object column was declared as NOT NULL in the table definition. Consequently:
  • NULL values cannot be inserted into that column.
  • An update cannot set values in that column to NULL.
  • A SET transition-variable statement in a trigger cannot set values in that column to NULL.
• The update or insert value was DEFAULT, but the object column was declared as NOT NULL without WITH DEFAULT in the table definition. Consequently:
  • A default value of NULL cannot be inserted into that column.
  • An update cannot set default values of NULL in that column.
  • A SET transition-variable statement in a trigger cannot set default values of NULL in that column.
• The column name list for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the table definition.
• The view for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the base table definition.

If the value for name is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", then the column name from the SQL statement was not available when the error was issued. The values provided identify the tablespace, table, and column number of the base table that does not allow NULL value.

Federated system users: this situation can be detected by the federated server or by the data source. Some data sources do not provide the appropriate values for name. In these cases the message token will have the following format: ":UNKNOWN", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.

Note:

Under some circumstances, the token name may not be filled in (sqlerrmc field of the SQLCA not filled in).

User Response:

Correct the SQL statement after examining the object table definition to determine which columns of the table have the NOT NULL attribute and do not have the WITH DEFAULT attribute.

If the value for name is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", you can determine the table name and column name using the following query:

   SELECT C.TABSCHEMA, C.TABNAME,
       C.COLNAME
   FROM SYSCAT.TABLES AS T,
        SYSCAT.COLUMNS AS C
   WHERE T.TBSPACEID = n1
   AND T.TABLEID = n2
   AND C.COLNO = n3
   AND C.TABSCHEMA = T.TABSCHEMA
   AND C.TABNAME = T.TABNAME 

The table and column identified by this query may be the base table of a view for which the SQL statement failed.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request (see the problem determination guide for procedures to follow to identify the failing data source) and examine the object definition for that data source. Remember that the defaults (NULL and NOT NULL) are not necessarily the same between data sources.

sqlcode: -407

sqlstate: 23502