Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only
Back to discussions
Expand all | Collapse all

DB2 for i - VARCHAR(100) ALLOCATE(60)

  • 1.  DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/18/25 06:19 AM

    I'm trying to find some information about the number of bytes that are used to store data in my table when I have a column defined with VARCHAR(100) ALLOCATE(60) but I'm not having much luck and I'm not convinced on the wording in the answer ChatGPT is giving me.

    If I have a column defined with VARCHAR(100) ALLOCATE(60) then how many bytes would be used with the following data?

    1. Data that is 60 characters long. Does this take 62 bytes?
      • 60 characters stored in the fixed area
      • 2 bytes for the length
    2. Data that is 80 characters long. I think this takes 90 bytes.
      • 60 characters stored in the fixed area
      • 2 bytes for the length
      • 20 characters in the overflow area
      • 8 bytes for the pointer to the overflow

    The bit that I'm not sure about is in 1. above.

    Will each row in my table require space for the pointer to the overflow even if the length of the data does not exceed the ALLOCATE length?

    The reason I'm asking is that I need to extend an existing CHAR(60) column and I'm planning to convert this to a VARCHAR but wanted to understand roughly how much additional storage the data would be using.



    ------------------------------
    Richard Moulton
    ------------------------------


  • 2.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/18/25 07:00 AM
    Edited by Daniel Gross 09/18/25 07:00 AM

    Hi Richard,

    to your questions:

    1. Yes - a short VARCHAR needs 2 additional bytes to store the length information

    2. AFAIK the system stores the data 

      - if the length is shorter or equal to ALLOCATE
        => completely inside the physical table structure

      - if the length is greater than ALLOCATE
        => completely inside the overflow area
        => only the pointer is stored in the table structure


    It's not 100% clear from what I read in the docs and in this support page:

    https://www.ibm.com/support/pages/varchar-data-type-and-how-allocate-parameter-impacts-performance-and-storage

    I hope this helps.

    Kind regards,
    Daniel



    ------------------------------
    Daniel Gross
    #IBMChampion
    Senior Core Developer, Geis Group
    Pegnitz, Germany
    https://blog.qpgmr.de/
    ------------------------------



  • 3.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/18/25 03:09 PM

    Thanks Daniel.

    I'd found the same support page but it didn't fully answer my question.



    ------------------------------
    Richard Moulton
    ------------------------------

    Original Message


  • 4.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/21/25 05:14 AM
    1. Yes - a short VARCHAR needs 2 additional bytes to store the length information

    That is how RPG stores variable length fields... but are you sure DB2 is handling this the same way (I wouldn't be sure about that) ?



    ------------------------------
    Paul Nicolay
    ------------------------------

    Original Message


  • 5.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/21/25 05:19 AM
    Hi Paul,

    I'm quite sure it's stores like that - if you look at the database table with DSPPFM you will see exactly this.

    Regards,
    Daniel


    Original Message


  • 6.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/21/25 05:26 AM

    I wouldn't make those conclusions neither... the overflow pointers are also present but you don't see them neither via DSPPFM I guess.

    Another example are timestamps... via DSPPFM you see them as 26 bytes but I doubt that DB2 stores them internally like that (I guess more compact).

    What you see is not always what it is... guess only IBM can answer this.



    ------------------------------
    Paul Nicolay
    ------------------------------

    Original Message


  • 7.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/22/25 08:54 AM

    DSPPFM shows the application view of the row definition, NOT the Db2 internal storage.



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 8.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/19/25 11:04 AM

    Only rows that exceed the allocated length go into the overflow area. 

    Here is a document that gives some examples on the amount of data and overall size of the file using varchar. Also includes some Tips/Pointers that you may want to be aware of
    Tips for using VARCHAR and VARGRAPHIC data types in databases - Documentación de IBM



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 9.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/20/25 05:55 AM

    Thanks Rich.



    ------------------------------
    Richard Moulton
    ------------------------------

    Original Message


  • 10.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)
    Best Answer

    Posted 09/19/25 11:13 AM

    Regarding #1 - the 8 bytes for the pointer to the overflow is always allocated even when overflow area is not used.

    #2 is not correct, data is not split between the two it's either stored in the fixed or overflow area.

    If column value is 80 characters long, this requires 150 bytes for this row.

      • Fixed area: 60 allocated bytes (which are empty) + 2 byte length + 8 byte pointer
      • Overflow area: 80 characters

    That's why recommendation is for an allocate value where 90-95% of the column values can be stored in the fixed area.



    ------------------------------
    Kent Milligan
    ------------------------------



  • 11.  RE: DB2 for i - VARCHAR(100) ALLOCATE(60)

    Posted 09/20/25 06:12 AM

    Thanks Kent, that's the info I was trying to find.



    ------------------------------
    Richard Moulton
    ------------------------------

    Original Message


Related Content