How to calculate maximum length of VARCHAR type in DB2?

Question

According to this:

VARCHAR(n) Varying-length character strings with a maximum length of n bytes. n must be greater than 0 and less than a number that depends on the page size of the table space. The maximum length is 32704.

If I correctly understand this definition, maximum length can be anything less then 32704, according to the configuration of database.

I wonder, how to calculate the current maximum length of VARCHAR type for a specific DB2-database using SQL commands?

Please note: the solution must include automatic determination of page size; this value can not be a parameter of suggested code.

Thanks in advance!

Answer 1

You can determine the page size of your tablespace from sysibm.systablespace:

select pgsize from sysibm.systablespace where name='FOO'

The create table documentation gives information on how the maximum varchar size relates to the page file size (search for maximum record size). I don't want to give a definitive answer, because it is rather complex and I don't have DB2 on z/OS to test it. But you should be able to determine the maximum lengths for each page size from a combination of that information and trial-and-error.

Note: this answer is for DB2 on z/OS (assumed because that was the source of the documentation quote above). It is slightly different on LUW, but can be found if you go to the equivalent documentation pages.