Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008

    Question Unanswered: DB2 timestamp problem inserting <NULL> as default value

    create table TABLE_MAIL(
    "TABLE_MAIL_ID" BIGINT generated by default as identity,
    constraint "TABLE_MAIL_PK" primary key ("TABLE_MAIL_ID")

    Pb1. while i insert records, and do not give any value for VALID_END column, it inserts <NULL> value for valid_end of the record in the database

    Pb2. I cannot do
    select * from table_mail where NOT(valid_end = CURRENT TIMESTAMP)
    to fetch all records with <NULL> value in valid_end column.

    How to work with such column or such record where I want to access Valid_end.
    Let me know incase of any clarifiation.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    Problem 1: What else would you expect to happen? If you don't provide a value for a column, DB2 will use the default. If you don't specify a DEFAULT clause (and you didn't do that for VALID_END, then the default is to use NULL. If you want to have another behavior, do the same as for CREATED_ON (just omit the single quotes around CURRENT TIMESTAMP because that is already a syntactically invalid string representation for timestamps.

    Problem 2: The "WHERE NOT ( ... )". Works fine. But you seem to have a basic lack of knowledge about NULLs in relational databases in general. If a value is NULL, then any comparison will always evaluate to "unknown", which is treated in the same way as "false" in predicates in the WHERE clause. To test explicitly for the presence of NULL, you have to use the IS NULL and IS NOT NULL predicates.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2008
    Thanks stolze, I was able to use IS NULL and IS NOT NULL

    I liked your style of answering, if you come across HeadFirst series, your style is very similar to the way they write in books.

  4. #4
    Join Date
    May 2003
    In order to be compatible with other databases, I would recommend you use CURRENT_TIMESTAMP instead of CURRENT TIMESTAMP. DB2 accepts either one, but some other databases only accepts CURRENT_TIMESTAMP.
    Last edited by Marcus_A; 04-28-08 at 22:05.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts