I have a few tables that I would like to estimate the size savings on if I drop a few columns. What is the best way to estimate the savings?
Announcement
Collapse
No announcement yet.
Estimating Size
Collapse
X
-
Re: Estimating Size
Originally posted by abercrombieande View PostWhat about nulls and varchar? Doesn't varchar store the length in the first two positions?
Large Objects are always stored in the overflow area, i.e. only the address for a LOB is stored directly in the table.
For columns that can hold NULL values an extra byte is added to the column. This Byte contains either *On or *Off, i.e NULL value or not.
Row length can be determined out of the catalog view SYSTABLES (in Schema QSYS2).
Column lengths can be determined out of the catalog view SYSCOLUMNS (in Schema QSYS2)
If you really want to reduce storage for a table, I'd suggest to first reorganize the table (and compare the size before and after), so that the deleted but still available in the tables get removed.
Birgitta
Comment
-
Re: Estimating Size
Thanks. When I look at QSYS2/SYSCOLUMNS for one of the tables that has nullable columns the length value always matches the storage value. I thought there was an additional byte?
Code:COLTYPE,LENGTH,STORAGE,IS_NULLABLE CHAR,20,20,Y NUMERIC,9,9,Y DATE,4,4,Y CHAR,4,4,Y CHAR,1,1,Y CHAR,10,10,Y NUMERIC,9,9,Y NUMERIC,5,5,Y CHAR,50,50,Y
Last edited by abercrombieande; September 22, 2011, 05:47 AM.
Comment
Comment