Working with Varying-Length Database Fields
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.
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.
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.
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