Technical Blog Post
Abstract
Weekly Tips from DB2 Experts: "SQL0407N Assignment of a NULL value to a NOT NULL column" error when inserting an empty string.
Body
Getting "SQL0407N Assignment of a NULL value to a NOT NULL column" error when inserting an empty string.
An empty string is not a NULL value in DB2.
The root cause of the issue is due to the following db cfg parameter.
Varchar2 compatibility = ON
The VARCHAR2 and NVARCHAR2 data types are introduced to support applications that use the Oracle VARCHAR2 and NVARCHAR2 data type.
If DB2_COMPATIBILITY_VECTOR=ORA set in your instance environment and then when you create a database, the value of the
Varchar2 compatibility will be set to ON by default.
If the Varchar2 compatibility is set, Character string values (other than LOB values) with a length of zero are generally treated as null
values. An assignment or cast of an empty string value to CHAR, NCHAR, VARCHAR, or NVARCHAR produces a null value.
C:> db2set -all
[i] DB2_COMPATIBILITY_VECTOR=ORA
C:>db2 get db cfg for oratest | more
Database Configuration for Database oratest
Database configuration release level = 0x0f00
Database release level = 0x0f00
Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = SYSTEM_1252
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = ON
Varchar2 compatibility = ON
Date compatibility = OFF
Database page size = 4096
C:>db2 "create table t2(col1 varchar(20) not null)"
DB20000I The SQL command completed successfully.
C:>db2 "insert into t2 values('')"
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=5, COLNO=0" is not allowed. SQLSTATE=23502
After a database is created with VARCHAR2 support enabled, it cannot be disabled for that database, even if the
DB2_COMPATIBILITY_VECTOR registry variable is reset.
If you want to remove the functionality, you have to unset DB2_COMPATIBILITY_VECTOR (db2set DB2_COMPATIBILITY_VECTOR = ) and then rebuild the database.
Reference:
VARCHAR2 and NVARCHAR2 data types
IBM DeveloperWorks: About the DB2_COMPATIBILITY_VECTOR
UID
ibm11141288