IBM Support

Weekly Tips from DB2 Experts: "SQL0407N Assignment of a NULL value to a NOT NULL column" error when inserting an empty string.

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


 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11141288