Re: Variable Length fields

"
Hello Jon,

You probably know this but ....

Also note that each increment in variable space used for a particular record 
can cause 
fragmentation of the record.

For example, with DDS like;

A       FIELD   32740A  VARLEN(20)

and two records like:
The quick brown fox jumped over the dog.
The rain in Spain stays mainly on the plain.

We get a database that looks like:

        The quick brown fox 
        The rain in Spain st

with the extra stuff in a shadow area that looks like:

        jumped over the dog.
        ays mainly in the plain.

If we then update the first record to correct the phrase so it reads "lazy dog' 
we get 
fragmentation.  The shadow area now looks like:

        jumped over the lazy
        ays mainly in the plain.
         dog.

Continue this process with many expansions to the data in the overflow area and 
you can 
see that fragmentation will impact performance on all I/O.  I don't believe the 
system 
has changed its behaviour to do something sensible like trash the existing 
overflow area 
and create a new one which would give us:

        --------------------
        ays mainly on the plain.
        jumped over the lazy dog.

The system could build a map of the available overflow areas (marked with 
dashes above) 
and reuse them in a manner similar to deleted records but I'm fairly sure that 
doesn't 
happen.  The current behaviour is done that way for performance reasons 
although with the 
speed of newer hardware perhaps that should be reexamined.

The system uses about 25 bytes per overflow to track the variable pieces.  
There is a 
flag associated with the fixed length part and each overflow part indicating 
whether 
overflow has occurred and the linkage data is stored with the overflowed part.  
If many 
records are fragmented then a reorg is required to fix the fragmentation which 
will also 
improve performance.

Also note that the maximum length of a VARCJAR field is 32740 (not the full 
32K) due to 
the overhead of the linkage bytes.  32766 - 1 - 25 = 32740.  The 1-byte is the 
overflow 
flag, the 25-bytes are the linkage data.

Don't do dumb things like have all the variable length data in the overflow 
(i.e., 
VARLEN(0) ).  Make sure the allocated length is sufficent for 90% of the data 
-- use the 
overflow for the exceptions rather than the rule.  Note that VARLEN fields are 
provided 
for convenience, there is always a perfomance overhead associated with using 
them.

Regards,
Simon Coulter.

«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
«» FlyByNight Software         AS/400 Technical Specialists       «»
«» Eclipse the competition - run your business on an IBM AS/400.  «»
«»                                                                «»
«» Phone: +61 3 9419 0175      Mobile: +61 0411 091 400           «»
«» Fax:   +61 3 9419 0175      mailto: shc@flybynight.com.au      «»
«»                                                                «»
«» Windoze should not be open at Warp speed.                      «»
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
//--- forwarded letter -------------------------------------------------------
> Date: Wed, 05 Apr 2000 22:21:07 -0400
> From: Jon.Paris@halinfo.it
> To: MIDRANGE-L@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: Variable Length fields

> 
> 
> 
> With variable length fields you specify the maximum length (32K in your 
>example)
> and an optional length to keep with the main record.  When you set the data in
> the field any length up to the "keep with the record" value will simply cause 
>a
> regular record to be written.  If the field length is longer, then the
> additional data is written to a separate area on the DASD.  By selecting an
> appropriate value for the "keep with" value you can minimize additional disk
> hits while reducing DASD usage.
> 
> The reason many people think that the whole length is used is the same as the
> reason that they think dates occupy 8 or 10 bytes (they don't - they occupy 4
> bytes) - you can never "see" anything but the expanded versi"n of the record.
> Even with DSPPFM you see the expanded version - just as you do with dates.

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---