Varying Length fields in DDS and IBM i SQL

Working with Varying-Length Database Fields

Hey, David:

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.

–Rob

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:

DFixed S 20A INZ('String')
DVarying S 20A INZ('String') VARYING
DLen S 10I 0

* Displays 20
C EVAL Len = %LEN(Fixed)
C Len DSPLY

* Displays 6
C EVAL Len = %LEN(%TRIM(Fixed))
C Len DSPLY

* Displays 6
C EVAL Len = %LEN(Varying)
C Len DSPLY

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.

DFixed S 20A INZ('String')
DVarying 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.

–David