Win a copy of Getting started with Java programming language this week in the Beginning Java forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

DB2 Default value  RSS feed

 
thejwal pavithran
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I have a field called an Obsolete change date in a DB2 table which stores the date when a record was marked as Obsolete. I'm not able to figure out how to define this in the create table statement.

If i give default not null, it will enter a default value which is the current date which i dont want to be populated because if it got populated, it would indicate the record was made obsolete on the current date.

Also, I dont want to populate with NULL. So how can i handle this situation?
 
Dana Ucaed
Ranch Hand
Posts: 197
5
Netbeans IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At which moment did you complete Obsolete change date?

Do you have a formulas for Obsolete change date?



 
Dave Tolls
Ranch Hand
Posts: 2414
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You say this column "stores the date when a record was marked as Obsolete".  So, if the record has not been marked as Obsolete, why do you want to put a value in there?
Surely NULL is the correct answer?
 
Tim Holloway
Bartender
Posts: 18504
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm going with Dave here.

NULL is something that's widely abused in databases, often used to mean 0 or empty/blank strings (which are not the same thing!).

However, NULL is precisely what you should use for values that are missing, unknown, or not otherwise available.

The alternative for a use case like this would be to always put a date (for example, the epoch date) in the date_obsoleted field and add a second column to hold a boolean indication of whether the record is obsolete or not.

That adds extra complexity and overhead to the logic and that's even before you get to the annoying problem that not all DBMS's even have a true boolean native column type and thus require some sort of kludge values such as 'T', 'F', 'Y', 'N', '0', '1', or even 0 and 1 binary (or -1 for ones complement fans).

So it's much easier to define the column as "DEFAULT NULL" and look for obsolete date values with clauses such as "WHERE date_obsoleted > reference_table.some_date" (unless I'm mistaken, NULL values are ignored in such expressions, otherwise add "AND date_obsoleted IS NOT NULL" to it).

One thing that NULL is not happy with is when it's used as a unique index such as a primary key, because NULL is not a real value. But in the case of dates, dates should also not be used as unique keys, since their precise values, like floating-point values, are often not as precise as you'd like. Or, to be more accurate, there's often an "impedence mismatch" between Java and DBMS datetime values, since Java date (java.util.Date and java.sql.Date) is internally measured in microseconds, but DBMS's are often storing datetime values in milliseconds, hundredths or seconds or days, depending on what brand of DBMS and what date column primitive type you defined the schema with.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!