Re: Database design question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Sort of, Nathan. You describe the behavior, generally, from a 
high-level perspective, although not the underlying process. If you 
specify VARLEN(1000), then every record has 1000 bytes allocated for 
the field, plus 2 bytes for its length. Even if the field is longer 
than that 1000, it is still taken up in the record.

On every database record, at the beginning, are some things we never 
see, unless we like to fish around in SST. The first byte is what is 
called the DENT byte - Delete Entry - it is what marks the record as 
deleted. I forget exactly, but I think you change the left nybble to 
C for deleted, 8 means a live record - anyhow, one bit controls this.

After this is an area called AXENT - auxiliary entry - it contains a 
null map (if there are null-capable fields) and pointers to auxiliary 
spaces (if there are variable length fields), where the data for 
variable length fields and LOBs are kept when longer than the 
allocated length - 1000 in this example. Spaces are MI objects that 
can be up to just under 16MB. Remember the size limit on user spaces? 
Same thing.

Birgitta said pretty much the same thing in a reply - variable length 
fields can require 2 IOs for the data, compared to 1 for fixed 
length. So therein comes IBM's recommendation to fit 90% of the 
records within the allocated length, for best balance of space and 
performance. Allocated length = 0 gives best space usage but very 
often requires 2 hits to get the data for each field. Fixed length 
gives best performance, usually, but can waste lots of space.

If you do a DMPOBJ on a physical file, you get an idea of some of 
this - lots of pointers to various odd things called cursors and 
formats and members and other things - PFs are compound objects, 
built up from several kinds of spaces, a basic building block on the 
iSeries. I used to know how to follow the rabbit trail (using SST) to 
the actual data in the auxiliary space(s) but have not done so in a 
long time (was in the performance lab testing the new query engine at 
Rochester in 2001)

Later - vacation next week - too much rambling!!
Vern

At 12:16 PM 8/9/2007, you wrote:

> Thanks for the tip, Jon.  If I understand correctly VARLEN(1000) 
> allocates 1000 bytes for storage for the NOTE field initially, which 
> the OS will automatically extend up to 32000 bytes, but only if needed.
>
> Nathan.
>
>
> ----- Original Message ----
> From: Jon Paris <Jon.Paris@xxxxxxxxxxxxxx>
> To: rpg400-l@xxxxxxxxxxxx
> Sent: Thursday, August 9, 2007 10:32:31 AM
> Subject: Re: Database design question
>
>  >>   NOTE       32000A         COLHDG('Note') VARLEN
>
> I would change this line to say (for example) VARLEN(1000) Nathan.  That
> assumes that 1,000 characters encompasses the majority of cases but sometimes
> you need to go to 32K.
>
> This gives you a better balance between performance and storage usage.
>
> Jon Paris
> Partner400
> www.Partner400.com
>
> --
> This is the RPG programming on the AS400 / iSeries (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.
>
>
>
>
>
>
>
>
>
> ____________________________________________________________________________________
> Looking for a deal? Find great prices on flights and hotels with 
> Yahoo! FareChase.
> http://farechase.yahoo.com/
> --
> This is the RPG programming on the AS400 / iSeries (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.