UniData DBA Tips and Tricks (post #1)
Getting the most out of 'guide'
In addition to verification of UniData file integrity, the 'guide' command provides information about the status of the file, which can be used to plan your resizing efforts.
This section, is intended to provide you with more information on using the guide statistics so you can better manage your files.
In future posts, we will go over some guidelines for determining resize values, and we will go over methods to determine sizing for future growth.
Note that a poorly sized UniData file impacts performance, It is recommended that you routinely check the status of your UniData files to ensure they are still properly sized.
The 'guide' command's -r option allows you to put the status and statistics about the file into a UniData file for easy query access.
Steps to Create and Populate the UDT_GUIDE file
The UniData guide command has the ability to to store detail information about the UniData Hashfiles into a UniData
Hashfile so that reports can be generated with UniQuery Commands.
## Step 1: Create a file to store the guide output
:CREATE.FILE DATA UDT_GUIDE 11
Create file GUIDE, modulo/11,blocksize/1024
Hash type = 3
( Edit the VOC, and set the Dictionary pointer to the UDT_GUIDE file in the sys account.
:AE VOC UDT_GUIDE
Top of "UDT_GUIDE" in "VOC", 3 lines, 12 characters.
001: F
002: UDT_GUIDE
--: I
003= @UDTHOME\\sys\\D_UDT_GUIDE
--: FI
Filed "UDT_GUIDE" in file "VOC".
Note that the Dictionary for the output of guide -r is in the sys account
## Step 2: Run guide -r
( please see the documentation for more information on the guide command )
:! guide -r GUIDE_OUT MEMBERS
: LIST GUIDE_OUT ALL
LIST GUIDE_OUT ALL 13:29:47 Feb 02 2021 1
File ID MEMBERS
File Name
Date 02/02/21
Time 13:28
File Type Static Hashing
File Status OK
File Access Method ACP
Cache Method None
VMS File Extents
File Size 1531904
Ovflw File Size
File Modulo 373
Minimum Modulo
Split Load
Merge Load
File Hash Type 0
File Block Size 4096
Level 1 Overflow 0
Primary Overflow 0
Level 2 Overflow 0
Primary Overflow 0
Free Blocks 0
Total Records 2500
Avg Rec / Grp 6.70
Dev Rec / Grp 0.46
Max Rec / Grp 7
Min Rec / Grp 6
Avg Bytes / Rec 419.48
Dev Bytes / Rec 106.19
Max Bytes / Rec 583
Min Bytes / Rec 243
Avg Bytes / Key 4.00
Dev Bytes / Key 0.00
Max Bytes / Key 4
Min Bytes / Key 4
Avg Record Size 432.48
Dev Record Size 106.19
Max Record Size 596
Min Record Size 256
New Modulo 193
New Block Size 8192
0 <= Size < 512 1859
512 <= Size < 1024 641
1024 <= Size < 2048 0
2048 <= Size < 3072 0
3072 <= Size < 4096 0
4096 <= Size < 5120 0
5120 <= Size < 6144 0
6144 <= Size < 7168 0
7168 <= Size < 8192 0
8192 <= Size < 9216 0
9216 <= Size < 10240 0
10240 <= Size < 11264 0
11264 <= Size < 12288 0
12288 <= Size < 13312 0
13312 <= Size < 14336 0
14336 <= Size < 15360 0
15360 <= Size < 16384 0
16384 <= Size 0
Largest Keys
Largest Keys (Size)
Smallest Keys
Smallest Keys (Size)
IS_64BIT 0
RFS 0
Split Style
Fileload 0
Fileload Percent 0
1 record listed
Some notes about the output
In addition the basics, Modulo and Blocksize, guide provides information about its contents, it provides the File Status.
Possible values include:
- Dense
- Empty
- Ok
- Overflow 1
- Overflow 2
- Sparse
Next Steps
As a DBA it is up to you to maintain the files, and resize when needed, based on your knowledge of the Application and the data usage. You can use the File Status to identify those files that may be candidates for resizing. While any file can be resized for disk space utilization and/or performance, your focus should be those files in overflow.
Level two Overflow are files where the keys (ids) have filled up the primary group and required an overflow block to contain more keys. These files should be resized.
Level 1 Overflow is when the data portion of the items in a group have expanded to an overflow group. Since it is possible for a item to be bigger than the blocksize of the group, not all Level 1 overflow can be eliminated.
In future posts, I will describe some methods for calculating the proper size of UniData Files to minimize overflow, and when (and how) to use UniData Dynamic files.
------------------------------
Michael Rajkowski
Rocket Software
------------------------------
