Search code examples
db2db2-luw

Why is DB2 treating an empty string as null?

Using DB2 10.5 on Windows x64

UPDATE dbo.datasource_databases
SET HOST = ''
WHERE ID = 1

Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=6, COLNO=1" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=3.67.28

HOST is a VARCHAR(512) NOT NULL

Is this expected behavior? If so, how should I work around this and if not, what could be causing this?

Edit: Along the same lines, SELECT ID, HOST from dbo.datasource_databases WHERE HOST != '' returns 0 rows, where checking for HOST != 'some gibberish' will return rows. To me this makes even less sense than the above behavior (shouldn't it just treat it as HOST NOT NULL?).

Solution

  • That could only happen if Oracle compatibility or at least Oracle's VARCHAR2 data type compatibility is enabled for the database, as described here. Normally DB2, as prescribed by the ANSI SQL standard, treats empty strings as strings of zero length, not NULLs.