SEO

Hi, I have received the following email from a colleague, and am quite
frankly baffled by the idea.

I am just wondering if anyone has any advice or suggestions about
this????


=== BEGIN MAIL ===
The DB2 DBA has requested that all columns in the tables be defined as
not null with default to improve storage, performance, and ease of
backup/recovery. Currently there are several columns in each table
that are nullable. By making these columns not null with default,
there would always be a value in the column, space for char/varchar,
and 0 for integer/smallint/decimal. Does this cause any problems with
the Java/Hibernate code? The DBA has indicated that all of the other
Java applications are defined this way.
=== END MAIL ===


Thanks,
John.

"DaFrizzler" <DaFrizzler@gmail.com> wrote in message
news:1169564212.277279.81710@k78g2000cwa.googlegro ups.com...
> Hi, I have received the following email from a colleague, and am quite
> frankly baffled by the idea.
>
> I am just wondering if anyone has any advice or suggestions about
> this????
>
>
> === BEGIN MAIL ===
> The DB2 DBA has requested that all columns in the tables be defined as
> not null with default to improve storage, performance, and ease of
> backup/recovery. Currently there are several columns in each table
> that are nullable. By making these columns not null with default,
> there would always be a value in the column, space for char/varchar,
> and 0 for integer/smallint/decimal. Does this cause any problems with
> the Java/Hibernate code? The DBA has indicated that all of the other
> Java applications are defined this way.
> === END MAIL ===
>
>
> Thanks,
> John.

Each nullable column uses one extra byte for the null indicator, so NOT NULL
WITH DEFAULT would save some storage. I don't know about the other claims
regarding performance and ease of backup/recovery.

What kind of advice or suggestions are you looking for?

Mark,

I am looking for exactly the kind of info/advise you just provided.
That is the first concrete reason I have received as to why this might
be a valid idea.

What is your opinion on using 0 as the default for numeric fields?

As far as I am concerned, 0 is a number in its own right, and there are
bound to be situations where we will want to indicate that there is no
value stored by using null. In a situation where 0 is a valid value,
how do you differentiate between the default value of 0 and a user
inputted value of 0?

The reason I am putting these questions out there is that I have never
come across the idea of defaulting every column on every table in a
database, and was wondering if anyone else has come across this
concept, or even better, worked with an implementation where this was
used.

Thanks,
John.




Mark A wrote:
> "DaFrizzler" <DaFrizzler@gmail.com> wrote in message
> news:1169564212.277279.81710@k78g2000cwa.googlegro ups.com...
> > Hi, I have received the following email from a colleague, and am quite
> > frankly baffled by the idea.
> >
> > I am just wondering if anyone has any advice or suggestions about
> > this????
> >
> >
> > === BEGIN MAIL ===
> > The DB2 DBA has requested that all columns in the tables be defined as
> > not null with default to improve storage, performance, and ease of
> > backup/recovery. Currently there are several columns in each table
> > that are nullable. By making these columns not null with default,
> > there would always be a value in the column, space for char/varchar,
> > and 0 for integer/smallint/decimal. Does this cause any problems with
> > the Java/Hibernate code? The DBA has indicated that all of the other
> > Java applications are defined this way.
> > === END MAIL ===
> >
> >
> > Thanks,
> > John.
>
> Each nullable column uses one extra byte for the null indicator, so NOT NULL
> WITH DEFAULT would save some storage. I don't know about the other claims
> regarding performance and ease of backup/recovery.
>
> What kind of advice or suggestions are you looking for?

Mark A wrote:
> "DaFrizzler" <DaFrizzler@gmail.com> wrote in message
> news:1169564212.277279.81710@k78g2000cwa.googlegro ups.com...
>> Hi, I have received the following email from a colleague, and am quite
>> frankly baffled by the idea.
>>
>> I am just wondering if anyone has any advice or suggestions about
>> this????
>>
>>
>> === BEGIN MAIL ===
>> The DB2 DBA has requested that all columns in the tables be defined as
>> not null with default to improve storage, performance, and ease of
>> backup/recovery. Currently there are several columns in each table
>> that are nullable. By making these columns not null with default,
>> there would always be a value in the column, space for char/varchar,
>> and 0 for integer/smallint/decimal. Does this cause any problems with
>> the Java/Hibernate code? The DBA has indicated that all of the other
>> Java applications are defined this way.
>> === END MAIL ===
>>
>>
>> Thanks,
>> John.
>
> Each nullable column uses one extra byte for the null indicator, so NOT NULL
> WITH DEFAULT would save some storage. I don't know about the other claims
> regarding performance and ease of backup/recovery.
>
> What kind of advice or suggestions are you looking for?
>
>
DB2 has NULL as well as DEFAULT compression. AFAIK that does NOT require
the compression feature. So the benefit of NOT NULL columns may be less
than one might think.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

DaFrizzler wrote:

> Hi, I have received the following email from a colleague, and am quite
> frankly baffled by the idea.
>
> I am just wondering if anyone has any advice or suggestions about
> this????
>
>
> === BEGIN MAIL ===
> The DB2 DBA has requested that all columns in the tables be defined as
> not null with default to improve storage, performance, and ease of
> backup/recovery. Currently there are several columns in each table
> that are nullable. By making these columns not null with default,
> there would always be a value in the column, space for char/varchar,
> and 0 for integer/smallint/decimal. Does this cause any problems with
> the Java/Hibernate code? The DBA has indicated that all of the other
> Java applications are defined this way.
> === END MAIL ===

Mark and Serge already gave some arguments. I think, there are a few more
things to consider:
(1) If you get rid of the null indicators by always using NOT NULL, you save
some space (1 byte per row). However, each row where you would have a
NULL before will now _waste_ space because the actual value has to be
stored. Granted, default compression would reduce that again.
(2) I don't think that decisions like nullable/not-nullable should be left
to the DBA.
It is a design decision during application development. The application
developer are the only ones that know whether a nullable column makes
sense or is mandatory in certain situations.
As you already pointed out, there are sometimes no good defaults. Using
0 (zero) for numeric fields to represent NULL has a high chance of
clashing with the numerical 0.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

DaFrizller:

FWIW, I agree strongly w/ Knut: this is not merely a storage issue but
a design issue. As you may be aware, the whole NULL/2VL/3VL issue is
very prickly, and pros and cons for both allowing and disallowing NULLs
abound (flip over to comp.databases.theory if you'd like to read some
spittle-flecked rants on the subject). The issue is not merely bytes
but also of semantics.

OK--enough soapboxing. You asked about Hibernate. One issue I'm aware
of is that it's not enough to declare a DEFAULT in DB2; you must also
declare the (same) default value in the BO (.java file). If you do not,
you risk overwriting the database-written default with whatever value
is in the instance variable mapped to the database field (often NULL),
when the Java values are persisted.

--Jeff

Knut Stolze wrote:
> DaFrizzler wrote:
>
> > Hi, I have received the following email from a colleague, and am quite
> > frankly baffled by the idea.
> >
> > I am just wondering if anyone has any advice or suggestions about
> > this????
> >
> >
> > === BEGIN MAIL ===
> > The DB2 DBA has requested that all columns in the tables be defined as
> > not null with default to improve storage, performance, and ease of
> > backup/recovery. Currently there are several columns in each table
> > that are nullable. By making these columns not null with default,
> > there would always be a value in the column, space for char/varchar,
> > and 0 for integer/smallint/decimal. Does this cause any problems with
> > the Java/Hibernate code? The DBA has indicated that all of the other
> > Java applications are defined this way.
> > === END MAIL ===
>
> Mark and Serge already gave some arguments. I think, there are a few more
> things to consider:
> (1) If you get rid of the null indicators by always using NOT NULL, you save
> some space (1 byte per row). However, each row where you would have a
> NULL before will now _waste_ space because the actual value has to be
> stored. Granted, default compression would reduce that again.
> (2) I don't think that decisions like nullable/not-nullable should be left
> to the DBA.
> It is a design decision during application development. The application
> developer are the only ones that know whether a nullable column makes
> sense or is mandatory in certain situations.
> As you already pointed out, there are sometimes no good defaults. Using
> 0 (zero) for numeric fields to represent NULL has a high chance of
> clashing with the numerical 0.
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany

Knut Stolze wrote:
> Mark and Serge already gave some arguments. I think, there are a few more
> things to consider:
> (1) If you get rid of the null indicators by always using NOT NULL, you save
> some space (1 byte per row). However, each row where you would have a
> NULL before will now _waste_ space because the actual value has to be
> stored. Granted, default compression would reduce that again.
> (2) I don't think that decisions like nullable/not-nullable should be left
> to the DBA.
> It is a design decision during application development. The application
> developer are the only ones that know whether a nullable column makes
> sense or is mandatory in certain situations.
> As you already pointed out, there are sometimes no good defaults. Using
> 0 (zero) for numeric fields to represent NULL has a high chance of
> clashing with the numerical 0.
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany

Maybe you have not worked as a DBA in a large application development
project, but the design of the database to support the business
requirements is usually defined by the data modeler for the logical
model and the DBA for the physical design (usually these are the same
person). Databases that are designed by developers (usually by
committee) are often times not very good in my experience (even though
I am a former developer).

I did not give any "arguments" in favor of not using nulls, I just said
that using nulls requires an extra byte for each nullable column (I
don't know about any native compression). However, I would find it hard
to believe that these extra bytes would be a real issue unless there is
an extremely large data warehouse being designed. But as noted, if the
requirements are to distinguish a null from a zero, then I would think
using nulls is justified in almost all cases.

Knut Stolze wrote:

> (1) If you get rid of the null indicators by always using NOT NULL, you
> save
> some space (1 byte per row).

That should have been "1 byte per nullable value"

> However, each row where you would have a
> NULL before will now _waste_ space because the actual value has to be
> stored. Granted, default compression would reduce that again.
> (2) I don't think that decisions like nullable/not-nullable should be left
> to the DBA.
> It is a design decision during application development. The
> application developer are the only ones that know whether a nullable
> column makes sense or is mandatory in certain situations.
> As you already pointed out, there are sometimes no good defaults.
> Using 0 (zero) for numeric fields to represent NULL has a high chance
> of clashing with the numerical 0.
>

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Mark A wrote:

> Maybe you have not worked as a DBA in a large application development
> project, but the design of the database to support the business
> requirements is usually defined by the data modeler for the logical
> model and the DBA for the physical design (usually these are the same
> person).

I totally agree. A good data modeler also knows about NULLs, their
semantics, and where they can and should be used and where not.

> Databases that are designed by developers (usually by
> committee) are often times not very good in my experience (even though
> I am a former developer).

I'm not arguing against that. ;-) I prefer to have _one_ _knowledge_
person, who is aware of logical and physical design, responsible for the
overall database design. A general statement like "use NOT NULL everywhere
and all the time" just raises some eyebrows, that's all.

> I did not give any "arguments" in favor of not using nulls,

Right - I omitted the "in favor" or "against" part for a good reason... ;-)

> I just said
> that using nulls requires an extra byte for each nullable column (I
> don't know about any native compression). However, I would find it hard
> to believe that these extra bytes would be a real issue unless there is
> an extremely large data warehouse being designed. But as noted, if the
> requirements are to distinguish a null from a zero, then I would think
> using nulls is justified in almost all cases.

Same thing with NULLs and empty strings: They are logically different things
and sometimes should not be thrown together.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany