size of numeric data

[Frank Swarbrick]

My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database disk than
a column defined as DECIMAL(7,2) and containing a value of 1.00.

I am honestly having a hard time believing this, but I don't know where I
can find the answer.
Help?

Thanks,
Frank

[Dave Hughes]

Frank Swarbrick wrote:

My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database
disk than a column defined as DECIMAL(7,2) and containing a value of
1.00.

I am honestly having a hard time believing this, but I don't know
where I can find the answer.
Help?

Thanks,
Frank

Your DBA is indeed mistaken (assuming he's referring to DB2 :-). From
the CREATE TABLE reference in the DB2 9.5 InfoCenter [1] (search for
the "Storage Byte Counts" heading):

"Storage Byte Counts: The following table contains the storage byte
counts of columns by data type for data values. The byte counts depend
on whether or not VALUE COMPRESSION is active. When VALUE COMPRESSION
is not active, the byte counts also depend on whether or not the column
is nullable. The values in the table represent the amount of storage
(in bytes) that is used to store the value.

....

DECIMAL (when VALUE COMPRESSION is not active and the column is not
nullable): The integral part of (p/2)+1, where p is the precision"

So a DECIMAL(11,2) NOT NULL column without VALUE COMPRESSION requires 6
bytes, while a DECIMAL(7,2) column with the same attributes requires 4
bytes per row, regardless of the value stored within the row.

[1]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db
2.luw.sql.ref.doc/doc/r0000927.html
Cheers,

Dave.

[Mark A]

"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...

My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database disk
than
a column defined as DECIMAL(7,2) and containing a value of 1.00.

I am honestly having a hard time believing this, but I don't know where I
can find the answer.
Help?

Thanks,
Frank

That is true with some databases, but not with DB2 unless you are
specifically using compression.

[Frank Swarbrick]

>>On 5/13/2008 at 12:25 PM, in message
<la******************************@posted.plusnet >, Dave
Hughes<da**@waveform.plus.comwrote:

Frank Swarbrick wrote:

>My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database
disk than a column defined as DECIMAL(7,2) and containing a value of
1.00.

I am honestly having a hard time believing this, but I don't know
where I can find the answer.
Help?

Thanks,
Frank

Your DBA is indeed mistaken (assuming he's referring to DB2 :-). From
the CREATE TABLE reference in the DB2 9.5 InfoCenter [1] (search for
the "Storage Byte Counts" heading):

"Storage Byte Counts: The following table contains the storage byte
counts of columns by data type for data values. The byte counts depend
on whether or not VALUE COMPRESSION is active. When VALUE COMPRESSION
is not active, the byte counts also depend on whether or not the column
is nullable. The values in the table represent the amount of storage
(in bytes) that is used to store the value.

...

DECIMAL (when VALUE COMPRESSION is not active and the column is not
nullable): The integral part of (p/2)+1, where p is the precision"

So a DECIMAL(11,2) NOT NULL column without VALUE COMPRESSION requires 6
bytes, while a DECIMAL(7,2) column with the same attributes requires 4
bytes per row, regardless of the value stored within the row.

[1]http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db
2.luw.sql.ref.doc/doc/r0000927.html

Thanks for the link. Looks like exactly what I need.

Frank

[Frank Swarbrick]

>>On 5/13/2008 at 5:24 PM, in message
<7q*********************@bignews6.bellsouth.net> , Mark
A<no****@nowhere.comwrote:

"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...

>My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database disk
than
a column defined as DECIMAL(7,2) and containing a value of 1.00.

I am honestly having a hard time believing this, but I don't know where

I

>can find the answer.
Help?

That is true with some databases, but not with DB2 unless you are
specifically using compression.

Do you by chance know if Oracle is one of the "some databases"? The DBA is
an Oracle guy who we also have working on DB2 now.

Thanks,
Frank

[Mark A]

"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...

Do you by chance know if Oracle is one of the "some databases"? The DBA
is
an Oracle guy who we also have working on DB2 now.

Thanks,
Frank

I believe that Oracle is one of the databases that automatically compresses
some data types.

[Serge Rielau]

Frank Swarbrick wrote:

>>>On 5/13/2008 at 5:24 PM, in message

<7q*********************@bignews6.bellsouth.net> , Mark
A<no****@nowhere.comwrote:

>"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...

>>My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database disk
than
a column defined as DECIMAL(7,2) and containing a value of 1.00.

I am honestly having a hard time believing this, but I don't know where

I

>>can find the answer.
Help?

That is true with some databases, but not with DB2 unless you are
specifically using compression.

Do you by chance know if Oracle is one of the "some databases"? The DBA is
an Oracle guy who we also have working on DB2 now.

NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.
That is the storage depends on the number of digits, where trailing 0's
are removed.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 5/13/2008 at 7:48 PM, in message
<68*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

Frank Swarbrick wrote:

>>>>On 5/13/2008 at 5:24 PM, in message

<7q*********************@bignews6.bellsouth.net >, Mark
A<no****@nowhere.comwrote:

>>"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:48******************@efirstbank.com...
My DBA says that a column defined, for instance, as DECIMAL(11,2) and
containing a value of 1.00 takes up no more space on the database disk

>>>than
a column defined as DECIMAL(7,2) and containing a value of 1.00.

I am honestly having a hard time believing this, but I don't know where

>>I
can find the answer.
Help?
That is true with some databases, but not with DB2 unless you are
specifically using compression.

Do you by chance know if Oracle is one of the "some databases"? The DBA

is

>an Oracle guy who we also have working on DB2 now.

NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.

Really! Interesting.

That is the storage depends on the number of digits, where trailing 0's
are removed.

Leading zeroes as well?

Any idea where I can find more information on this? I want to know all I
can before bring it do my DBA.

Thanks!
Frank

[Frank Swarbrick]

>>On 5/14/2008 at 4:54 PM, in message
<48******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>>>On 5/13/2008 at 7:48 PM, in message

<68*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

>NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.

>

Really! Interesting.

>That is the storage depends on the number of digits, where trailing 0's
are removed.

Leading zeroes as well?

Any idea where I can find more information on this? I want to know all
I
can before bring it do my DBA.

Found what I was looking for:

http://download.oracle.com/docs/cd/B.../datatype.htm#
i16209

"Internal Numeric Format
Oracle Database stores numeric data in variable-length format. Each value is
stored in scientific notation, with 1 byte used to store the exponent and up
to 20 bytes to store the mantissa. The resulting value is limited to 38
digits of precision. Oracle Database does not store leading and trailing
zeros. For example, the number 412 is stored in a format similar to 4.12 x
102, with 1 byte used to store the exponent(2) and 2 bytes used to store the
three significant digits of the mantissa(4,1,2). Negative numbers include
the sign in their length.

Taking this into account, the column size in bytes for a particular numeric
data value NUMBER(p), where p is the precision of a given value, can be
calculated using the following formula:

ROUND((length(p)+s)/2))+1
where s equals zero if the number is positive, and s equals 1 if the number
is negative.

Zero and positive and negative infinity (only generated on import from
Oracle Database, Version 5) are stored using unique representations. Zero
and negative infinity each require 1 byte; positive infinity requires 2
bytes."

The DB2 way is more familiar to me, being a Cobol programmer. Cobol stores
numeric data in a very similar fashion.

I am sure there are both good and bad points to both the DB2 way and the
Oracle way. The one thing that seems nice about the Oracle way is you don't
really have to worry ahead of time about the maximum value that would ever
need to be stored in a particular column. As long as it's not more that 38
digits of precision you're good to go!

We had a problem just the other day where we had a Cobol field defined as
PIC S9(9)V99 (which can hold a maximum value of 999,999,999.99) and we ended
up generating (or trying to generate, I should say) a transaction for over a
billion dollars. Well the most significant digit was truncated, so we were
'out of balance' by $1 billion. Don't worry! No customer was affected!!
<g Still, not good. Obviously whoever designed the data store at the time
couldn't imagine a single transaction of this type that would ever be this
large.

Frank