1. Registered User
Join Date
Oct 2007
Posts
246

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. Registered User
Join Date
Jul 2006
Location
Pune , India
Posts
433
The avg. for a table column can be gather by avgcollen in syscat.column

3. Registered User
Join Date
May 2009
Posts
509
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. Registered User
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. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## sysibm.syscolumns

This one will not work ?

Code:
```select    sum(max(ifnull(length, 0), ifnull(length2,0)))
from   sysibm.syscolumns
where tbname like 'TCDS&#37;'```
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
•