Re: Variable length fields question

[CRPence <CRPbottle@xxxxxxxxx>]

  The term /always/ is an absolute and thus surely innaccurate, as 
absolutes /always/ are ;-)  And the definition of "when needed" is just 
as problematic.  The scope of the topic probably needs to be narrowed to 
better discuss.

  Intuitively, the vlen segment need not be visited by the DB, whenever 
the length of a value for the currently-accessed row does not require 
any overflow.  For that reason, the /always/ seems excessive for the 
implication by Alan with regard to accessing the vlen segment, and seems 
likely to be, to what Jon alludes for the /when needed/ comment.  That 
merely reflects my understanding of the quoted replies, but I offer 
also, FWiW:

  In RLA the /always/ is probably fairly accurate.  Any row in which 
the Record Format includes the VarLen field(s), the full data will 
necessarily be retrieved [from the fixed portion and vlen segment] for a 
read; effectively two reads, *whenever* data for the row had been [or 
for a write must be] stored in that vlen overflow area.  The SQL works 
pretty much the same, but the statement defines the record format for 
the data accessed, rather than the declared\accessed /file/ defining the 
record format.  Thus for example the RLA program that accesses a record 
format HasVCflds will for each row that has overflowed data must access 
the vlen\aux segment whereas a SQL program with a request to "SELECT 
nonVCfld01, nonVCfld02 FROM FileWithRcdFmtHasVCflds" will never /need/ 
to visit the vlen segment.  But of course similar to how an SQL 
statement can limit the data accessed, a RLA program can refer to a 
Logical File (LF) that precludes references to any VarLen fields, thus 
similarly eliminating any access of the vlen segment by the underlying 
database support.

  Thus for (2.) [i.e. example assumes most values fit within the 
fixed-portion rather than overflowing to the aux SID], I believe a more 
accurate description is that both the storage and performance 
characteristics are likely to be generally favorable, rather than "only" 
the former.  Or as I stated in my reply to the OP, I would expect "the 
effect could be noticeably positive due to reduced overall storage in 
the dataspace and minimal secondary read\write activity required [to 
access the data in the variable-len segment]."

Regards, Chuck

On 29-Jul-2014 11:33 -0500, Jon Paris wrote:
> Be interested in whether Chuck or anyone else know differently Alan -
> but my understanding has always been that the overflow is only
> accessed as and when needed.
>
> On 2014-07-29, at 12:16 PM, Alan Campin wrote:
>
> > Based on what has been said on the forum before,
> >
> > 1. Whenever you display anything from the database, the database
> > will always present it as if there was one field. The sticking
> > together occurs at the lowest level of the DB.
> > 2. According to what I know, it does not matter. IBM always go to
> > the overflow and reads it in no matter what so I am not sure the
> > VARLEN does you any good. I guess the only thing it would do for
> > you is reduce the amount of disk storage required.
> >
> > On Tue, Jul 29, 2014 at 10:09 AM, John R. Smith, Jr. wrote:
> >
> > > I have some questions about variable length fields in files
> > > created using DDS.
> > >
> > > When I learned about them, I was told that if there is a field
> > > with a max of 100 bytes with a VARLEN(20), the system allocates
> > > 20 bytes in the record and if the value is longer than 20 bytes,
> > > it stores the additionally needed bytes elsewhere. When the
> > > record is read, the OS knows to retrieve the 20 bytes from the
> > > record and concatenates the additional bytes from the elsewhere
> > > storage (if any exist) and it returns a 100 byte field. The net
> > > result is less DASD tied up with extraneous spaces in the file
> > > with no impact to the developer. However, when I look at the
> > > file via DSPPFM or WRKLNK, I see all 100 bytes even if all 100
> > > are spaces.
> > >
> > > My questions are:
> > > 1) Is the OS smart enough to pad out the 21-100 bytes when
> > > displayed via WRKLNK and DSPPFM so I don't see the difference or
> > > is my understanding of how the DDS VARLEN works flawed?
> > > 2) Assuming my VARLEN is guessed correctly and most records fit
> > > into the allocated bytes, is there any noticeable impact with
> > > using VARLEN  fields?
> > > 3) If my guess is incorrect and a big chunk of records require
> > > the additional 21-100 bytes, does this have a huge performance
> > > impact?