Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

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=7606, COLNO=0" is not allowed.  SQLSTATE=23502

So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o   The update or insert value was DEFAULT, but the object column
   was declared as NOT NULL without WITH DEFAULT in the table
   definition.  Consequently:

   -   A default value of NULL cannot be inserted into that
       column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.

Bernard Dhooghe