How much storage space to VARCHAR columns occupy?

book

Article ID: 16181

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

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



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.

Resolution

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.

Additional Information

For more information, see the following CA IDMS DocOps pages:

(SQL) Data Types

PRINT PAGE

Using Presspack