Tag Archives: DB2 database

How to quickly locate the problem in DB2-407 error reporting SQLCODE: -407, SQLSTATE: 23502

SQLCODE: -407, SQLSTATE: 23502: The exact description of this error is such that a NULL value cannot be inserted into a column defined as NOT NULL. This is a violation of the integrity constraint exception.

in development, this error is something that we often encounter. The problem is simply, “Columns that cannot be empty are empty.” For a table with a large number of fields, it is difficult to sift. How to accurately

positioning?It’s actually pretty simple.

We can learn more from the error message, which can help us pinpoint the problem. Common error messages are as follows:

com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3
	at com.ibm.db2.jcc.b.sf.d(sf.java:1396)
	at com.ibm.db2.jcc.c.jb.l(jb.java:356)
	at com.ibm.db2.jcc.c.jb.a(jb.java:64)
	at com.ibm.db2.jcc.c.w.a(w.java:48)
	at com.ibm.db2.jcc.c.dc.c(dc.java:312)
	at com.ibm.db2.jcc.b.tf.cb(tf.java:1723)
	at com.ibm.db2.jcc.b.tf.d(tf.java:2315)
	at com.ibm.db2.jcc.b.tf.Z(tf.java:1326)
	at com.ibm.db2.jcc.b.tf.execute(tf.java:1310)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)

Message: Integrity constraint exception (  
--- The error occurred in D:\------- path omitted-------.  
--- The error occurred while applying a parameter map.  
--- Check the aaa.sql-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3)

observe this error message carefully: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3. You can see that the error message has been located very well

is clear. We only need to query in syscat.columns of the system table. The syscat.columns table is a system table that holds detailed information about the COLUMNS of all tables in a DB2 database. We just need the basis

view, table name, column number can be queried to find which column. COLNO=3 in the error message above is the column number for which the error was reported. As follows:

SELECT 
	* 
FROM 
	SYSCAT.COLUMNS 
WHERE 
	TABSCHEMA = 'DB' AND 
	TABNAME = 'TT_PARAM_LOG' AND 
	COLNO = '3'