How much storage space to VARCHAR columns occupy?

Document ID : KB000016181
Show Technical Document Details
Introduction:

In an SQL-defined table, columns holding character data can be defined as either CHARACTER or VARCHAR.

Question:

In an SQL-defined table, how much storage do VARCHAR columns occupy?
Do they take up only the amount of significant data in each individual occurrence of the column, or do they occupy the maximum amount as defined in the table?

Environment:
CA-IDMS SQL Option, all supported releases.

VARCHAR columns occupy the maximum amount as defined in the table, regardless of the actual length of a particular column instance.

For example, consider this table definition and row data:-

CREATE TABLE CHARTEST
( CALCKEY                          UNSIGNED NUMERIC(4) NOT NULL,
VARCHARCOL                       VARCHAR(1024) NOT NULL,
CHARCOL                          CHARACTER(4) NOT NULL,
ENDCOL                           CHARACTER(6) NOT NULL
)
;

INSERT INTO CHARTEST VALUES (1,'VARCHAR','CHAR','ENDCOL');

Look at the row data as it appears in the output of a PRINT PAGE:

*+ 000230   404040AB F0F0F0F1 0007E5C1 D9C3C8C1    *   .0001..VARCHA*
*+ 000240   D9000000 00000000 00000000 00000000    *R...............*
*+ 000250   00000000 00000000 00000000 00000000    *................*
*+ 000260   --SAME--
*+ 000630   00000000 00000000 0000C3C8 C1D9C5D5    *..........CHAREN*
*+ 000640   C4C3D6D3 03D47E00 03D47E02 03D48002    *DCOL.M=..M=..M..*

Column VARCHARCOL starts at offset x’238’ with the length halfword value of 7.
Then the data starts and runs from offset x’23A’ to x’63A’ where the column CHAR starts.
This is a length of x’400’, or 1024 as defined in the column definition.

Note also that the CHAR column only occupies 4 bytes and there is no length halfword.
This is the only difference between CHAR and VARCHAR in terms of data storage.

CA-IDMS Presspack can be used to compress SQL tables in general.