× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.