Varying Length fields in DDS and IBM i SQL

IBM i

Sep 13
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? Dont 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

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

(0 votes) 0/10
Share
Tweet
Share
Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.

Really Related Posts

Copying iSeries fields from numeric to Alpha – aka using SQL to change column data type

Use MSGCON to display a MSGID from a MSGF

Adding bar codes to good old fashioned spool reports | AS400 RPG BARCODE OVERLAY

Modernizing AS400 Green Screens

Interview Question and Answer – AS400 things you should know

IBM i SQL vs DDS – Is SQL faster than CPYF Native IO?

Controlling DSPF color without using indicators

IBM i DDS Edit Codes

Developerworks Connections Sunset – How to Extend RDi