What is the use of NULL/NOT NULL in DB2?



IBM's flagship relational database management system

What is the use of NULL/NOT NULL in DB2?

Postby maxcc » Sun May 09, 2010 12:25 am

What is the use of NULL/NOT NULL in DB2? Why do we use it?
maxcc
 
Posts: 28
Joined: Fri Apr 30, 2010 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: NULL

Postby dick scherrer » Sun May 09, 2010 12:54 am

Hello,

If you are not already familiar with the concept of NULL, i'd suggest you invest the time in things that will be more useful to you. None of the systems i work with these days permit NULLs. Everything is NOT NULL.

NULL is the concept of a non-existent value. For example one byte can contain from x'00' to x'FF' for 256 possible values. NONE of these 256 values is NULL. NULL is the absence of a value, so a NULLable 1-byte field requires an additional indicator to say whether the column has a value of not (this is known as the "NULL Indicator"). A NULLable multi-byte column also requires a NULL indicator, but the indicator is for the entire column (not each byte).

For theory, it is fine, but is problematic for most applications. Using NULL typically adds confusion and extra code (that is often incorrectly implemented). . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: NULL

Postby maxcc » Sun May 09, 2010 1:56 pm

That is much clear now. In addition to it my question, What is the advantage overall in a program of using null or not null? or if it was used earlier? I was asked in an interview..
maxcc
 
Posts: 28
Joined: Fri Apr 30, 2010 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: NULL

Postby dick scherrer » Sun May 09, 2010 7:27 pm

Hello,

As i mentioned earlier, NULL brings more problems than solutions.

I see no "advantage" but that is probably not the answer some interviewer is looking for. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: NULL

Postby Robert Sample » Sun May 09, 2010 7:29 pm

There are some cases (survey responses for example) where it can be important to distinguish between having data and not having data. These cases are pretty rare, however, and most of the time NULL will cause more problems than it solves.
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: What is the use of NULL/NOT NULL in DB2?

Postby GuyC » Tue May 11, 2010 3:26 pm

NULL will only cause problems with programmers who are to lazy to learn how to use it. Since all programmers all lazy this would be the case most of the times.

Even when none of your columns are NULLS allowed programmers still have to know how to handle them in case of a left or outer join.
Or has those not allowed either? you might as well go back to sequential file handling then.

Systems that don't allow nulls are very simple ; violate some very basic R.I. rules or have a kind of default row for owner entities : like you can't hire someone until you know in which department he works, what office he's in or what's his internal phonenumber.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: What is the use of NULL/NOT NULL in DB2?

Postby dick scherrer » Wed May 12, 2010 1:01 am

Hello,

NULL will only cause problems with programmers who are to lazy to learn how to use it.
Opinions vary. . . But i do agree, that there are more lazy programmers than ever before :)

Systems that don't allow nulls are very simple ; violate some very basic R.I. rules or have a kind of default row for owner entities :
Again, an opinion not a fact. . . However; if a simple solution provides perfect results, why not use the simple solution. . .

Knowing how to use a thing does not make it a good thing to use. . .

like you can't hire someone until you know in which department he works, what office he's in or
I've only implemented these kinds of "rules" a few thousand times and never needed to use NULL to accomplish it. . . Indeed, we did this quite nicely for many years before there was a NULL . . .

Long ago i was more of a purist. For the last 25 years or more, my clients have preferred implementations that always worked, were easily maintainable (often by beginners), and were conservative of system resources. . .

One of the biggest problem areas for missed communication/information is with the users who neither need nor relate to dealing with NULLs. And more and more of our users generate their own queries.

As Robert mentioned, there are a few cases when using NULL is best, but i have seen very few.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: What is the use of NULL/NOT NULL in DB2?

Postby GuyC » Wed May 12, 2010 7:39 pm

dick scherrer wrote:we did this quite nicely for many years before there was a NULL . . .

You fail to mention how (in a relational database), but every solution (even NULL) has its disadvantages.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post