Defaulting ALL database columns in ALL tables - not allowing any NULL values...???

[DaFrizzler]

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.

[Mark A]

"DaFrizzler" <Da********@gmail.comwrote in message
news:11*********************@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?

[DaFrizzler]

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" <Da********@gmail.comwrote in message
news:11*********************@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?

[Serge Rielau]

Mark A wrote:

"DaFrizzler" <Da********@gmail.comwrote in message
news:11*********************@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

[Knut Stolze]

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

[jefftyzzer]

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

[Mark A]

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]

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

[Knut Stolze]

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