To create the variable length field just described, use the following DB2 statement:
CREATE TABLE library/table-name
(field VARCHAR(50) ALLOCATE(20) NOT NULL)
In this particular example the field was created with the NOT NULL option. The other two options are
NULL and NOT NULL WITH DEFAULT. Refer to the NULLS section in the SQL Reference to
determine which NULLS option would be best for your use. Also, for additional information on variable
length field support, refer to either the SQL Reference or the SQL Programming Concepts.
Performance Expectations
y
Variable length field support, when used correctly, can provide performance improvements in many
environments. The savings in I/O when processing a variable length field can be significant. The
biggest performance gains that will be obtained from using variable length fields are for description
or comment types of fields that are converted to variable length. However, because there is additional
overhead associated with accessing the spill area, it is generally not a good idea to convert a field to
variable length if the majority (70-100%) of the records would have data in this area. To avoid this
problem, design the variable length field(s) with the proper allocation length so that the amount of
data in the spill area stays below the 60% range. This will also prevent a potential waste of space
with the variable length implementation.
y
Another potential savings from the use of variable length fields is in DASD space. This is particularly
true in implementations where there is a large difference between the ALLOCATE and the
VARCHAR attributes AND the amount of spill data is below 60%. Also, by minimizing the size of
the file, the performance of operations such as CPYF (Copy File) will also be improved.
y
When using a variable length field as a join field, the impact to performance for the join will depend
on the number of records returned and the amount of data that spills. For a join field that contains a
low percentage of spill data and which already has an index built over it that can be used in the join, a
user would most likely find the performance acceptable. However, if an index must be built and/or
the field contains a large amount of overflow, a performance problem will likely occur when the join
is processed.
y
Because of the extra processing that is required for variable length fields, it is not a good idea to
convert every field in a file to variable length. This is particularly true for fields that are part of an
index key. Accessing records via a variable length key field is noticeably slower than via a fixed
length key field. Also, index builds over variable length fields will be noticeably slower than over
fixed length fields.
y
When accessing a file that contains variable length fields through a high-level language such as
COBOL, the variable that the field is read into must be defined as variable or of a varying length. If
this is not done, the data that is read in to the fixed length variable will be treated as fixed length. If
the variable is defined as PIC X(40) and only 25 bytes of data is read in, the remaining 15 bytes will
be space filled. The value in that variable will now contain 40 bytes. The following COBOL
example shows how to declare the receiving variable as a variable length variable:
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
60
