Andremoniy Andremoniy - 2 years ago 230
SQL Question

How to calculate maximum length of VARCHAR type in DB2?

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download