How do I import NULL columns into DB2 timestamp field?
Posted on 2006-07-14
Hi All - I am trying to port data from a mysql DB to a DB2 DB(express 8.2). I am very new with DB2 and am having trouble importing rows where one of the timestamp columns is null. For a simple example, if I use;
'select * into outfile '/home/jentest.txt' from jentest
on mysql I get a file /home/jentest.txt with;
2 2006-06-26 09:21:16
Now I try to import the file into DB2, the table definition in DB2 has column2 defined simply as timestamp which should allow nulls as that is the default. The table def is:
create table jentest (id INTERGER NOT NULL DEFAULT 0, time TIMESTAMP);
The import command I have used, both with and without timestampformat:
import from '/home/jentest.txt' of del modified by timestampformat="yyyy-mm-dd hh:mm:ss" COLDEL0x09 INSERT INTO jentest;
I get an error for the first row and the second row works fine, the result being that row 1 is not imported but row 2 is;
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
I have tried changing the /N to NULL. I have removed timestampformat on the import. I am not sure if there are any options, how do I get DB2 to recognize the time column allows NULLs, by definition it should? The nulls work for other field types defined similiarly, such as INTEGER.
Please let me know if you have any ideas, thanks!