Results 1 to 3 of 3

Thread: Numeric Sizing

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Numeric Sizing

    Another Newbie question for you DB2'ers out there.

    If I have a column with Numeric(3) and a column Numeric(18) and I only put 3 digit numbers into them does the numeric(18) utilize the same amount of bytes as the Numeric(3).

    Another way of asking this is if I insert 1 million rows into the numeric(3) and then insert the same 1 million rows into the numeric(18) will the table require the same amount of space for both?

    Thanks in advance!
    Mono.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For numeric columns, the length is fixed depending on the definition of the column length in the table. The actual storage used by DB2 is (n+1)/2 rounded up to next highest byte if needed. So a numeric 10 and numeric 11 both use 6 bytes. The last half byte is used for the sign (plus or minus).

    Varchar columns shrink according to actual amount of data in them. But they need 2 extra bytes internally to store the length.
    Last edited by Marcus_A; 03-08-04 at 15:03.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Also consider using the following data types:

    SMALLINT - 2 bytes internal to DB2 - holds up to 32,767 (plus or minus)
    INTEGER - 4 bytes internal to DB2 - holds up to 2,147,483,647 (plus or minus)
    BIGINT - 8 bytes internal to DB2 - holds up to 9,223,372,036,854,775,807 (plus or minus)
    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
  •