How to solve DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502 [closed]

2015-08-28 18:12:19.047-Fri Aug 28 18:12:19 IST 2015DB2 Connection established -->com.ibm.db2.jcc.t4.b@c26e4a
2015-08-28 18:12:20.030-Execution failed ======> alter table BLP.COUNTER_EXTRACTION_REQUEST alter column SCHEME_ID set not null
2015-08-28 18:12:20.032-Error code for failure ======> DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=SCHEME_ID, DRIVER=3.63.123
2015-08-28 18:12:20.044-Execution failed ======> alter table BLP.COUNTER_EXTRACTION_REQUEST alter column SCHEME_TYPE set not null
2015-08-28 18:12:20.044-Error code for failure ======> DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=SCHEME_TYPE, DRIVER=3.63.123

2 Answers

According to this page, DB2 SQL error code -407 means:

-407 - AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES

The State Code is saying the same thing:

23502 - An insert or update value is null, but the column cannot contain null values.

But you are apparently getting this error for this ALTER statement:

alter table BLP.COUNTER_EXTRACTION_REQUEST alter column SCHEME_ID set not null

So I suspect that in this context the error code means that the column currently contains null values, and you can't add a "not null" constraint to the column until you have eliminated them.

two ways:

First one: Drop the column from the table. Re-create with not null.

second one: update the column=' default value' where column date = null and change to null.