Re: Database design question

I appreciate the in-depth explanation, Vern.

----- Original Message ----
From: Vernon Hamberg <vhamberg@xxxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, August 9, 2007 7:57:12 PM
Subject: Re: Database design question

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.