Varying Length fields in DDS and IBM i SQL

Back in the olden days – files were filled with numbers and long string of data. Words like SAUSAGES might have been stored as a 30 ALPHA field in the format “SAUSAGES______________________” which, even tho its padded with *blanks – is just wasting a lot of space. Nowadays, most systems use variable length fields, shorter, more efficient and quicker to handle. So, why are so many so-called modern IBM i Databases not using variable length fields?

Scared of Change? Don’t be – variable length fields are easy peasy.

I was going to write a blog about this, but then I found this terrific article over at IT Jungle. It’s an oldie but a goldie and really says everything I had in mind:

Working with Varying-Length Database Fields

Question:

We use a utility to replicate data files between our iSeries system and several SQL Server databases running on Windows 2000. When the original file definition comes from SQL Server, this utility creates varying-length character fields on our iSeries. This caused a problem in an RPG program when I forgot to set the length of the field when I changed its value. Do you know how much space we save when a field is varying-length? Also, what is the easiest way to determine and set the length of data in a varying-length field after changing the field’s value? Our programs are ILE RPG, and we are on V5R1.

Answer:

To answer your first question, you first need to understand how varying-length fields are defined and stored on the iSeries. When you define a varying-length field, you specify a minimum allocated length and a maximum length. Varying-length fields are most useful for fields that contain values that vary greatly in their length. I have seen dramatic performance improvements on the iSeries when long fixed-length fields were converted to varying-length.

A varying-length field is stored in one to three parts. The first part is a 2-byte length. The second part follows immediately after the 2-byte length and is present if a minimum allocated length is specified. The third part is also optional and is present if the length of a varying-length field’s value is greater than the minimum allocated length. This third part is not part of the record and is stored in a separate area of the file. Accessing this separate storage area adds some additional overhead, so keep this in mind when defining varying-length fields, and use an allocated length that fits the majority of your field values if the length of those values is relatively consistent.

To define a field that has an allocated length of 0 and a maximum length of 20, use this DDS:

A      VARYING          20A   VARLEN

To define a field with an allocated length of 5 and a maximum length of 20, use the following statement:

A      ALLOCATE5      20A  VARLEN(5)

You can also use SQL to define those same fields:

CREATE TABLE MyTable  (Varying VARCHAR (20), Allocate5 VARCHAR (20) ALLOCATE(5))

To answer your second question, the easiest way to determine the length of data in a character field is to use the %len built-in function. If you are converting between fixed-length character fields and varying-length character fields, the %trim built-in function can also come in handy. The following example illustrates the use of %lenand %trim with fixed- and varying-length character fields:

dcl-s Fixed char(20) inz('String');
dcl-s Varying char(20) inz('String') VARYING;
dcl-s Length zoned(10:0);

 // Displays 20
 Length = %LEN(Fixed);
 dsply %char(Length);

 // Displays 6
 Length = %LEN(%TRIM(Fixed));
 dsply %char(Length);

 // Displays 6
 Length = %LEN(Varying);
 dsply %char(Length);

In most cases, you should not need to set the length of varying length fields. Unlike the CAT, MOVE, MOVEL, SUBST, and XLATE op codes, the EVAL op code sets the length of varying fields for you. Just remember that in most cases you need to use the %trim built-in function when using EVAL to set a varying-length field value from a fixed-length field.

D Fixed         S 20A INZ('String')
D Varying      S 20A VARYING
C EVAL Varying = %TRIM(Fixed)

This code set the value of the Varying field to “string” the value of Fixed, less any leading or trailing spaces, and also set its length to 6. Without the %trim built-in function, the value would be “String ” and the length would be 20.

Varying-length fields are the norm in most other databases and are the exception on the iSeries. For character fields containing values that vary greatly in length, you should consider varying-length fields to save file space and improve performance. Working with varying-length character fields can be confusing, but the EVAL op code, combined with the %trim built-in function make it much easier to work with varying-length fields.