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
------------------------------

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/

Original Message:
Sent: Thu September 18, 2025 06:18 AM
From: Richard Moulton
Subject: DB2 for i - VARCHAR(100) ALLOCATE(60)

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
------------------------------

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/

Original Message:
Sent: Thu September 18, 2025 06:18 AM
From: Richard Moulton
Subject: DB2 for i - VARCHAR(100) ALLOCATE(60)

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
------------------------------

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) ?

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/

Original Message:
Sent: Thu September 18, 2025 06:18 AM
From: Richard Moulton
Subject: DB2 for i - VARCHAR(100) ALLOCATE(60)

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
------------------------------

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) ?

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/

Original Message:
Sent: Thu September 18, 2025 06:18 AM
From: Richard Moulton
Subject: DB2 for i - VARCHAR(100) ALLOCATE(60)

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
------------------------------

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.

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) ?

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/

Original Message:
Sent: Thu September 18, 2025 06:18 AM
From: Richard Moulton
Subject: DB2 for i - VARCHAR(100) ALLOCATE(60)

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
------------------------------

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.

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

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.

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.

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.

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.