Have you ever had a situation where your DB2/400 database size grows fast?
If you are not using the entire buffer size for each record, and you want to reduce the size of the database, you can achieve it programmatically. IBM has provided the field level keyword VARLEN for physical files to control the field buffer usage. This keyword has been available for a long time but was not used extensively by programmers due to the additional complexity involved.
Here is an Analysis of the Field Level Keyword VARLEN and its usage.
A VARFL1 125 COLHDG('Variable' 'Length' 'Alc 25') VARLEN(25)
In the above example, we have a field of length 128 bytes, where 25 bytes will be initially allocated for the field data. As long as you have your data for VARFL1 less than 25 bytes, the storage used for VARFL1 will be 25 bytes. Say your field length is 26 bytes, then the storage for VARFL1 will be 50 bytes. And it keeps on incrementing by a value of 25 until it reaches 125.
It should be noted that the system will not be able to identify the length of the Data in VARFL1. The program should update the length of the data while accessing the file at record level. Actually in the above example, the initial size of the field will be 25 bytes but the buffer length for VARFL1 will be 27 bytes -- 25 bytes for data and 2 bytes for length. When you reach the maximum length, the buffer for field VARFL1 will have 2 bytes for length and 125 bytes for data.
Inside the RPG Program, the field VARFL1 should be defined for a length of 127 bytes. The first 2 bytes should be filled with the length of the data and the next 125 bytes with actual data. While doing a write operation, DB2 will allocate the size specified in the field's length for the field VARFL1.
It should be noted that the field length should always be passed as a binary field. Also, while creating the RPG program, specify Convert Option Parameter of CVTOPT(*VARCHAR).
The sample program VARLENRP will show how to use the VARLEN keyword.
Here is an analysis of the how much the database size is reduced.
Initially when the keyword VARLEN is used, it will appear as if the database size had increased instead of being reduced. Typically the tools for displaying the file information -- like, DSPFFD, DSPPFM, etc. -- are designed for fixed length fields and hence it may not reflect actual file data.
A detailed analysis of the VARLEN keyword with a data sample of 10000 records revealed that file with VARLEN keyword used only 3% of the space occupied by the file without VARLEN keyword. The program ANALYSRP will dump the analysis data in the file ANALYS00.
Here is the graph showing database size vs. number of records: Analys1, which is only for the first 40 records.
A further analysis with 10000 records revealed that it has a significant effect. The graph Analys2 shows 10000 records.
Programs:
analys00.txt
analysrp.txt
objsizcl.txt
stdlenpf.txt
stdlenpg.txt
stdlenrp.txt
varlends.txt
varlenpf.txt
varlenpg.txt
varlenrp.txt
Limitations: The only catch in this technique is that the I/O operations performance will be affected. But the result for a sample performance analysis for a total of 10000 records was in the order of milliseconds.
This tip originally appeared on Search400.com.
About the Author
Sudhakar Kunji is a senior analyst in Technical Services at Manhattan Associates in Atlanta, a Supply Chain Execution Solution provider.
Reader Feedback
Nick Hobson writes: Variable length database fields are very useful. However, there are a few misconceptions in this tip.
1. RPG IV has offered native support for variable length fields
since V4R2. As such, it's not necessary to specify
CVTOPT(*VARCHAR) when creating an RPG program that uses
variable length fields. Nor do you need to explicitly set the
length of the field by manipulating the first two bytes. The
length is set automatically from the length of the source
string. Note that the %subst and %trimr BIFs can reduce the
effective length of the source string.
2. If specified, the two byte length field should be defined as
4b 0 (or preferably 5u 0), rather than 2b 0. (*HIVAL for a 2b
0 field is only 99.)
3. The allocated (or typical) length does not function as an
increment. Rather, it specifies the number of bytes allocated
for the field in the fixed portion of the file. Any excess
bytes are stored in the variable length portion of the file.
Note that if the allocated length is omitted it defaults to
zero, and all of the data for the field is stored in the
variable length portion. This minimizes file size.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Dozens of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
