Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    11

    Unanswered: Want to know size in bytes taken by each dataType db2 supports.

    Hello ,

    I would like to know the size in bytes required internally for each datatype db2 supports.

    For eg.
    I think for BIGINT its 8 bytes

    Would like to know the size in bytes for
    SMALLINT, BIGINT, DOUBLE, VARCHAR, BLOB, TIMESTAMP, DECIMAL, INTEGER

    Thanks..

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Want to know size in bytes taken by each dataType db2 supports.

    SQL Refernece should have this info ...

    Cheers
    Sathyaram

    Originally posted by sangramd
    Hello ,

    I would like to know the size in bytes required internally for each datatype db2 supports.

    For eg.
    I think for BIGINT its 8 bytes

    Would like to know the size in bytes for
    SMALLINT, BIGINT, DOUBLE, VARCHAR, BLOB, TIMESTAMP, DECIMAL, INTEGER

    Thanks..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Posts
    11

    Re: Want to know size in bytes taken by each dataType db2 supports.

    Thanks Sathyaram,

    Yes SQL reference doc do have this info.
    But its little bit confusing.

    For eg.
    For TimeStamp it talks about internal representation as a String of 10 bytes.
    And it also talks about
    length of timeStamp column , as described in SQLDA , is 26 bytes.

    In this case what is the actual bytes used for storing timestamp ?

    Also for Decimal how much is the byte size ? its not given in doc .



    Originally posted by sathyaram_s
    SQL Refernece should have this info ...

    Cheers
    Sathyaram

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Decimal is stored like mainframe packed-decimal (comp-3). I know that does not help most people here. Basically 2 numbers fit in one byte, except that the last half byte must contain the sign (+ or -). But it is always rounded up to the next highest byte if only a half byte is used. Decimal places are not stored in the data itself, they are known to the column definition, so they take up no space.

    Consider the following examples of decimal:

    4 digits - 3 bytes of storage (last half byte in byte 3 is not used)
    5 digits - 3 bytes of storage (3rd byte has the 5th digit plus the sign)
    6 digits - 4 bytes of storage
    7 digits - 4 bytes of storage

    Date, Time, and Timestamp are stored internally somewhat like decimals (two digits per byte), except there is no need to store a sign in the last half byte. Also, the separator characters such as "-", "/", ".", etc are not stored internally in DB2 in the actual table.

    Therefore, since Date has 8 digits in it (YYYYMMDD), it only uses 4 bytes internally stored on the row. When Date is returned to an application program in a string or the SQLDA, one byte is used for each digit and the separator characters are put in, so Date has 10 bytes (8 digits plus 2 separator characters).

    Similar concept with Time (3 bytes internally) and Timestamp (10 bytes internally).
    Last edited by Marcus_A; 02-24-04 at 20:59.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just in case anyone read my post above, I made some errors in the decimal length that I have corrected.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2010
    Posts
    2

    Sizes on DB2 9.7 LUW

    I am glad I found your post after looking around for size documentation on Decimals. Will your information also be true for decimals and their sizes on DB2 v9.7 for Windows (Linux and unix)?

    The actual question I am debating is smallint vs decimal for smaller numeric values ... (from 1 to 32767). It seems that the only time you would want to select a Decimal over a smallint would be if it is single digits- A packed decimal would consume only one byte (half byte for the number, half byte for the sign), vs. the smallint consuming two bytes.

    Do Decimals have any other advantages from performance standpoint? (less I/O time or conversion times?)

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There has been no change in the information I provided above.

    In most cases, a smallint/int/bigint works faster with mathematical calcuations than decimal, but the difference is probably not even measureable unless you are doing a lot of math. The main advantage of decimal is that it handles numbers to the right of the decimal point. Also, some developers insist that some numeric columns only can be a certain number of digits in length without them having to validate the value to make sure the max length is not exceeded and that it will fit on a form/report/webpage correctly.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A View Post
    smallint/int/bigint works faster with mathematical calcuations than decimal, but the difference is probably not even measureable
    You may also want to consider comparisons (as in integer vs. decimal index key values) and hash calculations in a partitioned database. In all such cases integers are more efficient than decimals.

Posting Permissions

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