Re: Use of VARYING fields

Hi David

If space is not an issue, and if using varying-length fields is useful, yeah, allocate to the full size of the fields. With varying-length fields on the i, you have 2 ends of the spectrum of performance and space - full allocation gives the best performance, no allocation gives the best use of storage.

Maybe I should say what a "space" is - this is an MI object - low-level object. It is an unstructured object with a maximum size of about 16 MB. A user space (*USRSPC) is made up of just one of these "spaces", hence the 16 MB limit on the size of user spaces.

Physical files (and all kinds of other things) are constructed from several such spaces. Somewhere in the physical file is a table of pointers to the several spaces that make up the main area of the PF, as well as pointers to auxiliary spaces used for overflow data in varying-length fields.

The primary purpose of varying-length fields is to save space. That it is easier to use them in RPGLE is a nice benefit. But mixing fixed-length and varying-length variables in RPGLE is a exercise where you can get errors easily, if you are not careful about it - this is about how fixed-length variables are assigned to varying-length variables - a fixed-length variable has a size of the entire length, and that size is what the varying-length variable uses. So trailing blanks will be included. This is usually NOT what you want when using varying-length - the idea is that you don't have to use the %trim() function.

OK, back to your question - how is storage use reduced? Only the allocated length is reserved in the main PF space - every record has the allocated length there. This is like the fixed-length part of the field. If the data in the field is longer than the allocated length, the whole thing is stored in the auxiliary space. The allocated length is still there in the main space but basically not used.

There is little benefit to using varying-length, if you are allocating the full length - other than reducing the use of %trim() in RPG expressions. That is a pretty significant benefit, but has its issues, as I mentioned above. You are not likely to have only varying-length fields in your database, nor in you existing applications.

If you have allocated length of zero (0), then there is no room taken up in the main PF space for the data - it is all in the auxiliary space, and that is the most efficient for storage use, because only the actual data is stored - no trailing blanks, etc. (There ARE some items that indicate where in the auxiliary space the data is located, and, for strictly reasons of reducing storage space, I sometimes suggest not using varying-length for short fields - like less than 8 characters long or so.)

BTW, when you change the value of a varying-length field in a PF, any space used in the auxiliary area is not reused - at least, used to be. This is also not compressed out when you use CPYF - only RGZPFM will reclaim that space. Reason for this, so far as I can tell, is for performance on updates. Now this was what I saw in 2001 - it may have changed by now, but I doubt it. There are performance implications with finding a no-longer-used portion of the space every time a field is updated.

I used to know how to work my way down to the actual data, using STRSST - it's been too long. Certainly someone on the MI400 list should know, if they are willing to enlighten us. Maybe when I get some time and have nothing to do, I'll burrow down again and see what I can find. ;-)

Time for real work!
Vern

David FOXWELL wrote:

This has turned into a brilliant thread and I thank all the contributers.

I have been living in complete ignorance and did not know anything about how such data would be stored in a PF.

After having read up, I have a couple of questions. If you don't care about space saving, can't you just code the maximum and allocated length as the same?
How is space saved? I mean, if the allocated length is 0 and all the rows are in the overflow space, where is this space?

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : jeudi 10 septembre 2009 15:34
À : RPG programming on the IBM i / System i
Objet : Re: Use of VARYING fields

Varying length fields can save space in your tables. IBM's
guideline is to set the allocated length so that 90-95% of
the rows have data within the allocated length. Default on
allocated length is 0, which puts all the data into what is
called an auxiliary space - this means that it takes 2 read
operations, most of the time, to get what takes 1 read
operation with fixed length. But if the allocated length is
as above, most of the time you'll have only 1 read operation.
Search the following PDF for "Tips for using VARCHAR and
VARGRAPHIC data types in databases"
- this is the section heading in the "Performance and Query
Optimization" manual at InfoCenter -

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rz
ajq/rzajq.pdf

Here is a quote from the "Database Programming" manual that
speaks to this -

You can use the DDS VARLEN keyword to define a character
field as variable length. You can define this field as:
- Variable-length with no allocated length. This allows the
field to be stored using only the number of bytes equal to
the data (plus two bytes per field for the length value and a
few overhead bytes per record).
However, performance might be affected because all data is
stored in the variable portion of the file, which requires
two disk read operations to retrieve.
- Variable-length with an allocated length equal to the most
likely size of the data. This allows most field data to be
stored in the fixed portion of the file and minimizes unused
storage allocations common with fixed-length field
definitions. Only one read operation is required to retrieve
field data with a length less than the allocated field length.
Field data with a length greater than the allocated length is
stored in the variable portion of the file and requires two
read operations to retrieve the data.

I also do not recommend defining short fields as varying -
don't remember the length, but I'd not make anything 20 or
less into a varying length.

One advantage of varying length variables in RPGLE is that
you don't have to use the %trim built-in function. You do
have to know that if you set a varying length variable to the
value of a fixed length variable, without using the trim on
the fixed length one, then the varying one is set to the
length of the fixed one. As an example, say you have a field
CHAR200V defined as 200 A varying - and a field CHAR50F
defined as 50 A fixed.
eval char50f = 'aaaaaaaaaabbbbbbbbbb'

That is 20 characters long, and there are 30 trailing blanks.

eval char200v = char50f

Then char200v will be assigned a length of 50 (first 2 bytes
carry the length). You probably don't want this.

eval char200v = %trim(char50f)

Now char200v has a length of 20. This is what you want. Probably.

This is an issue only when assigning the value of
fixed-length to varying-length - it's all in the ILE RPG
Reference, by the way.

David FOXWELL wrote:

Hi,

I was asked why use this kind of variable. A part from

tidying up the code when concatenating text fields, I wasn't
sure. The problem is that our data dictionary is used in all
our RPG sources and all zones are supposed to be declared
like a field in the dictionary.

Are there other advantages?

Thanks

--
This is the RPG programming on the IBM i / System i
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.