Results 1 to 4 of 4

Thread: null values

  1. #1
    Join Date
    Nov 2007
    Posts
    72

    Unanswered: null values

    hi,

    Could you please tell me whether null values consume space in db2 .I am on db2V9.5 on aix ...

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    consumes space? NULL values?

    don't confuse null with vacuum.

    and 'null values' is an improper use of the terms.
    null indicates that there is no value (data) associated with this column in this row.

    In fact, a nullable column carries within the file structure supporting db2 an additional
    field to indicate if the column is null or not null.

    null or not null tells db2 whether to return a value for a column.

    so actually, a nullable column whether null or not null, consumes more space that a 'not null column'.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Aside from the fact that NULL is not a value (because it is an inherent property that you can compare one value with another value of the same domain, which you can't do with NULLs), each nullable value requires an additional byte to be stored, i.e. the null indicator. So the answer is "yes", it consumes space.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hopefully I am not being redundant, but:

    If a column is defined in the create table as NOT NULL, it always takes up one less byte of storage compared to a column that is nullable. This is true regardless of whether a particular row has a null value or not null value for that column.

    Along similar lines, a VARCHAR column requires 2 extra bytes of storage to store the length of the actual value being stored on any given row for that column value. This is true whether the value is null or not null.

    So a VARCHAR column that is nullable always takes up 3 extra bytes compared to a CHAR column defined as NOT NULL (although obviously a VARCHAR column may require less storage than CHAR for the actual data if the entire length is not used).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •