RE: Dynamic Field Length...

As I understand it, the varying length field is broken into two parts: the
fixed part which corresponds to the allocated bytes portion of the VARLEN
keyword, and the overflow part that is everything else. The system must
perform additional I/O to retrieve any data longer than the default
allocation size.  By sizing the VARLEN to a length that will *usually* hold
the data, you can minimize performance issues.

Here's a snip from the manual:
http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AUC02/FIGPHYDDS?S
crollTOP=FIGPHYDDS
<quote>
Character or hexadecimal data can be defined as variable length by
specifying the VARLEN field level keyword. Generally you would use variable
length fields, for example, as an employee name within a database. Names
usually can be stored in a 30-byte field; however, there are times when you
need 100 bytes to store a very long name. If you always define the field as
100 bytes, you waste storage. If you always define the field as 30 bytes,
some names are truncated.


You can use the DDS VARLEN keyword to define a character field as variable
length. You can define this field as:


Variable-length with no allocated length. This allows the field to be stored
using only the number of bytes equal to the data (plus two bytes per field
for the length value and a few overhead bytes per record). However,
performance might be affected because all data is stored in the variable
portion of the file, which requires two disk read operations to retrieve.


Variable-length with an allocated length equal to the most likely size of
the data. This allows most field data to be stored in the fixed portion of
the file and minimizes unused storage allocations common with fixed-length
field definitions. Only one read operation is required to retrieve field
data with a length less than the allocated field length. Field data with a
length greater than the allocated length is stored in the variable portion
of the file and requires two read operations to retrieve the data.
<end>

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Hatzenbeler, Tim [mailto:thatzenbeler@clinitech.net]
Sent: Thursday, October 18, 2001 2:33 PM
To: 'midrange-l@midrange.com'
Subject: RE: Dynamic Field Length...


This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
so with varlen,  if I define a record with 1024 characters but only use 50
characters for 100 records, and then 1000 characters for one record, is my
file size.

(50*100)+1000 = 6000 bytes

or would it be 1024 * 101 = 103424 bytes

I know there is additional overhead, but for the sake of the conversation...
it would only use 6000 bytes of storage...

thanks, tim

> -----Original Message-----
> From: Phil [SMTP:sublime78ska@yahoo.com]
> Sent: Thursday, October 18, 2001 12:33 PM
> To:   midrange-l@midrange.com
> Subject:      RE: Dynamic Field Length...
>
> Tim,
>
> In SQL you can do this (for example):
>
> CREATE TABLE PHILWORK/PHILTEST
>  (FIELD1 INTEGER NOT NULL WITH DEFAULT,
>   DESCRIPTION FOR COLUMN DSCR VARCHAR (1024 ) ALLOCATE(25))
>
> what this does is create a null-capable column named DESCRIPTION with an
> alias called DSCR (to use in an RPG pgm so you don't have to reference it
> as
> DESCR00001) which is variable length but with 25 bytes allocated.  This is
> supposed to speed up processing if the length is usually 25 or under (or
> whatever length you want to make it).  Or don't use ALLOCATE.
>
> There's a good redbook - "SQL Cookbook" I believe it's called.  It's for
> UDB
> DB2 V7 so not everything applies to the as/400 but enough of it does to
> make
> it valuable.
>
> In DDS you can do it this way:
>
>   A          R TESTR
>   A            FIELD1         9B 0
>   A            DESCR       1024A         ALIAS(DESCRIPTION)
>   A                                      VARLEN(25)
>   A                                      ALWNULL
>
> The big difference is the sql table's record format is the same as the
> table
> name and the dds file's record format is TESTR while the file name is
> TEST.
>
> Say to those Sequel guys "Step back non-believers!"
>
> Phil
>
>
>
>
> > Does our database have a memo type of field?
> >
> > ie: in Sequel, they can input text with variable lengths...  With DDS,
> we
> > have to setup our database with a fixed number or characters...
> >
> > Either via SQL, or DDS (or both) is it possible to setup a field (ie:
> > comment field) to accept an unlimited number of characters...
> > This would be
> > a non keyed field...
> >
> >
> > thanks, tim
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing list
> > To post a message email: MIDRANGE-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > or email: MIDRANGE-L-request@midrange.com
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.