DB2 VARCHAR Data Type use in COBOL-DB2

DB2 VARCHAR data type is used to store a string with variable-length characters but a maximum of the string length is specified. If the length of the string is less than set or fixed-length then it will store as it is without padded with extra blank spaces. The storage size of the VARCHAR data type is equal to the actual length of the entered string in bytes. We should use this data type when we expect the data values in a column are of variable length. In the VARCHAR data type, there are two options, if the string can’t exceed maximum length then we can use the normal VARCHAR keyword but if we need to store long length string then we use the VARBINARY keyword.

Syntax:

VARCHAR(n) – In this syntax, n defines the string length that ranges from 1 to 8,000. If you don’t specify n, its default value is 1.

VARCHAR(max) – max defines the maximum storage size which is 231-1 bytes (2 GB).

VARCHAR fields have variable-length characters, it is an uncertain length string information type. It can hold numbers, letters, and unique characters. DB2 VARCHAR ordinarily holds 1 byte for every character and 2 additional bytes for the length data. It is prescribed to utilize VARCHAR as the information type when segments have variable lengths and the real information is the path not exactly the given limit.

Rules for DB2 VARCHAR

1. First, we need to specify the size of VARCHAR data type.
2. We need to insert strings that are less than the specified size of the VARCHAR data type.
3. We cannot insert a longer length string than defined.
4. If we need to insert a long-length string then we need to specify the varbinary keyword at that time of table creation or we alter the table.

Do’s

• Specify the size n in VARCHAR[(n)] even if it’s optional, otherwise, string truncation will occur when the length exceeds the field definition. Another consideration is storage and performance as there are 2 additional bytes in VARCHAR.
• Use VARCHAR If String Size Varies Considerably.
• Use VARCHAR as Index Key Instead of CHAR when values vary in size. VARCHAR index keys are better than CHAR index keys when the key has varying sizes.

Don’ts

• Don’t use VARCHAR when string size is fixed and non-nullable, use CHAR Instead. The general rule of thumb when a fixed-sized string is required is to use CHAR. Use CHAR columns only if the columns will be non-nullable because the size in bytes of CHAR columns when NULL is equal to the defined size of the column. Yet VARCHAR when NULL has a size of 1 no matter how much the defined size is.
• Don’t Use VARCHAR(n) If n Will Exceed 8000 Bytes. Use VARCHAR(MAX) Instead.

Handling DB2 VARCHAR in COBOL-DB2 Program

In COBOL, you need level 49 to receive VARCHAR data. 

01 CUST-NAME.   
   49 CUST-NAME-LENGTH  PIC S9(4) COMP.   
   49 CUST-NAME-TEXT    PIC X(100).

CUST-NAME-LENGTH contains the length of CUST-NAME-TEXT. You can see this when displaying the CUST-NAME field.

When the data in CUST-NAME_TEXT is less than 100 bytes trailing spaces will be added. To use the CUST-NAME_TEXT field in your COBOL program, first, you must remove padded spaces.

To insert/update a record from COBOL 49 level to DB2, you must take care of padded blanks. Else, these padded Blanks will pass to the VARCHAR column of the DB2 Table.

In COBOL, for instance, you try to move data to the Alpha-Numeric field, the PIC(X(n)) field padded with spaces when data length is less than the field size.

Removing padded SPACES

Assign each character of the value to an array, count the number of trailing blanks, and subtract the value from the total length of the string.

Use UNSTRING to get Length. 

UNSTRING CUST-NAME-TEXT 
DELIMITED BY ' ' 
COUNT IN CUST-NAME-LENGTH.

Use scalar function (POSSTR) to determine the position in a string of another string:

EXEC SQL 
SET :CUST-NAME-LENGTH = POSSTR('SAM KURIAN, ', ') - 1  
END-EXEC.

The host-variable CUST-NAME-LENGTH contains 10 (since there are 10 bytes in SAM KURIAN before trailing blanks)

While using UNSTRING and POSSTR functions since they ignore blank character-string. For instance, in the ‘SAMbbKURIAN’, you’ll lose the ‘KURIAN.’

Explanation

CUST-NAME-TEXT should be the same length as that of Table column size (VARCHAR(100)).

Before moving data to CUST-NAME-TEXT, you must move spaces to it. Otherwise, junk data is mixed up with actual value.

For instance, If the CUST-NAME column contains Blanks, the CUST-NAME-LENGTH will be 95, the first five bytes of CUST-NAME-TEXT will contain Blank, and the remaining 95 bytes will have the same data before the moving column to the host variable.

DB2 VARCHAR vs CHAR

VARCHAR data type saves disk space but it brings about a 2-byte overhead expense for each specified value. Utilizing VARCHAR likewise requires extra handling for differing length rows. Subsequently, utilizing CHAR is desirable over VARCHAR, except if the space that you save by utilizing VARCHAR is critical. The reserved space is not huge if the most extreme section length is little or if the lengths of the qualities don’t have a critical variety.

Example: 

Consider the Query:  
CREATE TABLE Student(Name VARCHAR(20), Gender CHAR(6));
INSERT into Student VALUES('Herry', 'Male');
INSERT into Student VALUES('Mahi', 'Female');
SELECT LENGTH(Name) FROM Student;

OUTPUT:  
LENGTH(Name)
5
4 

It is a datatype in SQL which is used to store character strings of the fixed-length specified. If the length of the string is less than set or fixed-length then it is padded with extra blank spaces so that its length became equal to the set length. The storage size of the CHAR datatype is n bytes(set length). We should use this data type when we expect the data values in a column are of the same length. 

Example:

Consider the Query:  
CREATE TABLE Student(Name VARCHAR(30), Gender CHAR(6));
INSERT into Student VALUES('Herry', 'Male');
INSERT into Student VALUES('Mahi', 'Female');
SELECT LENGTH(Gender) FROM Student;

OUTPUT:  
LENGTH(Gender)
6
6

CHAR vs VARCHAR

VARCHAR saves space when there is variation in the length of values, but CHAR might be performance-wise better.

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here