|
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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
