Results 1 to 5 of 5

Thread: length of row

  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: length of row

    hi

    to find the length of row in table in bytes

    select sum(length) from syscat.columns where tabname = 'tbname'

    or

    for example
    char (4) = 4
    date = 4
    decimal (7,2) = 5

    sum would be 4+4+5 = 13
    kindly suggest
    regds
    Paul

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    The avg. for a table column can be gather by avgcollen in syscat.column
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Mathew_paul, close but not quite correct. The Decimal value is not right. To calculate a Decimal, start with the Precision (the first number), Add 1, Divide by 2 and if there is any remainder, go up to the next highest integer value. Ex:

    (7 + 1) / 2 = 4
    (8 + 1) / 2 = 4.5 = 5
    (9 + 1) / 2 = 5

    Note: if you are just looking at the space used, this is why most all Decimals are defined as odd numbers. If you were going to define a Decimal(8,2), you might as well use Decimal(9,2) as they will use the same amount of space. However there are other reasons to define even number Decimals.

    Here are some other row sizing information:

    CHAR What you define
    VARCHAR What you define Plus 2 for the Length indicator
    TIME 3
    DATE 4
    TIMESTAMP 10
    SMALLINT 2
    INTEGER 4

    In addition add 1 for each column that allows a NULL. There is also an 8 byte overhead for each row.

    However, if this is an existing table, I would agree with rahul_s80 and suggest looking up the information in the catalog tables.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To calculate a Decimal, start with the Precision (the first number), Add 1, Divide by 2 and if there is any remainder, go up to the next highest integer
    If you used integer calculations on DB2, "Add 2, Divide by 2" will be better(no need to consider reminder).
    For example:
    (7 + 2) / 2 = 9 / 2 (= 4.5) = 4
    (8 + 2) / 2 = 10 / 2 = 5
    (9 + 2) / 2 = 11 / 2 (= 5.5) = 5

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question sysibm.syscolumns

    This one will not work ?

    Code:
    select    sum(max(ifnull(length, 0), ifnull(length2,0)))
       from   sysibm.syscolumns
       where tbname like 'TCDS%'
    Lenny
    Last edited by Lenny77; 10-30-09 at 16:03.

Posting Permissions

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