I have been trying to create a test database in DB2 V7 environment. In one table, am finding this error "The field value in a row X and column Y is missing, but the target column is not nullable." where row X and column Y correspond to a data value that is an empty space. I tried recreating the table with the NOT NULL clause in column Y removed and it loaded all rows. My problem is that the rows with no values for column Y should be recognized as spaces and not nulls, in that when I run
select count(*) in tableZ where columnY =''
I should get the number of all records with no values for column Y.
There should be no nulls in any character field in my data model so that when I run the same query as below:
select count(*) in tableZ where columnY is null
I sholud get 0.

How can I achieve this so that I load my database appropriately?

Thanks,
Franka

create the table with not null and load at least 1 space in this column - as value is required
this should resolve the problem
select * from table where col=' ' (one or more spaces should return the same result)

That doesn't make much sense to me. The empty string is a value. So if you want to indicate that no value is there, you use NULL in SQL.

Anyway, what I think you want to do is to declare the column with DEFAULT '' to tell DB2 that whenever no value is supplied for this column in an INSERT statement, you will use the empty string as value. Since this may not cover all scenarios (IMPORT will try to insert the NULL if you don't exclude the column to begin with), add a trigger that maps a NULL to the empty string.