ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Estimating Size

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Estimating Size

    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?

  • #2
    Re: Estimating Size

    Hi Abercrombieande:

    Isn't that the column size (in bytes) * number of records in the file?
    GLS
    The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

    Comment


    • #3
      Re: Estimating Size

      What about nulls and varchar? Doesn't varchar store the length in the first two positions?

      Comment


      • #4
        Re: Estimating Size

        Originally posted by abercrombieande View Post
        What about nulls and varchar? Doesn't varchar store the length in the first two positions?
        For VarChar or VarGraphic columns the allocated length is always stored in the table (independend if all bytes are filled or not!). Column contents that are longer as the allocated length are stored in the so called Overflow Area. The address of the overflow area is stored in the record. If the keyword ALLOCATE was not specified in the CREATE TABLE statement or if VARLEN without length was specified in the DDS definition, the column is defined with 16 Byte (address of the overflow area) and all content is stored in the overflow area. For performance issued the allocated size should be defined so that > 80% of data can be stored directly in the column. VarChar or VarGraphic columns shorter than 32 Bytes are not stored in the overflow area (even if ALLOCATE was not specified).
        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


        • #5
          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

          Working...
          X