bdfd1m1c | Database Administration |
Block indexing considerably reduces I/O processing when files are being
read but can make updating slower.
The prime block of a block index subfile contains technical LRECs (TLRECs),
which are maintained internally by the TPFDF product.
As you can see in Figure 62, each index TLREC contains the following:
- Primary key (always 02)
- File address of the overflow block
- Record code check (RCC) of the prime block
- Data identifying the keys of the first LREC in an overflow block.
Figure 62. Index TLREC
- Note:
- Not all TLRECs hold the same amount of user data. Use the DSECT to
define how much data a TLREC can hold.
To avoid having to look at every LREC in every block, the TPFDF product
stores indexing data in the TLRECs. The TLRECs hold data extracted from
the first LREC in each overflow block.
The TPFDF product compares each TLREC with the LREC keys that the
application program is searching for. If the LREC keys that the
application program is searching for are less than or equal to the keys of the
first LREC in the current block, the TPFDF product looks back to the previous
block to find the matching LREC.
Figure 63 shows an example of this process. In this example,
the application program is searching for the key, SMITH. The search
process is as follows:
(1) The TPFDF product looks at the TLREC for overflow block 1
(DAVIDSON). The key held there is less than SMITH.
(2) The TPFDF product looks at the TLREC for overflow block 2
(FREEMAN). The key held there is less than SMITH.
(3) The TPFDF product looks at the TLREC for overflow block
3. The key held there is equal to SMITH .
(4) The TPFDF product searches the previous overflow block (2) for
SMITH and finds the first instance of it.
Figure 63. Block Indexing
To implement block index support, set global set symbol &SW00SKE in the
file DSECT macro definition to the size, in bytes, of the key fields of the
LRECs. If there are different types of LRECs in the file, set
&SW00SKE to the longest key field.
If you update an existing file to use block indexing, the change is not
immediate. The database administrator must first reassemble the DBDEF
macro. The TPFDF product adds block index support LRECs to the file
when LRECs are deleted from the file or when the file is packed and
closed.
- Note:
- The TPFDF product uses X'02' as the LREC ID in LRECs that it uses for
block index support. Ensure the value for global symbol &SW00TQK is
greater than X'02'.
Block index files have the following characteristics:
- LRECs must be variable-length.
- LRECs must be organized UP or DOWN. Do not use NOORG (no
organization specified). If you do, the TPFDF product might not locate
the correct LREC.
- Every key field in the LREC must have some organization (for
example, if KEY1 is UP organized, KEY2 must be UP or DOWN organized).
Do not use NOORG (no organization specified). If you do, the TPFDF
product might not locate the correct LREC.
- TLRECs are not immediately generated if a file expands and overflow blocks
are added. During a pack of the subfile, the TPFDF product determines
how many chains there are and stores this number in the first TLREC.
During the next pack, the TPFDF product generates this many TLRECs,
initializes them, and determines the number of chains (which may have changed
since the previous pack). Therefore, the number of TLRECs does not
always accurately reflect the number of overflow blocks in a file.
- You must pack a block index file to validate the file references
after CRUISE capture and restore processing because CRUISE capture and restore
processing nullifies the validation of block index technical LRECs
(TLRs).
- TLRECs are only held in the prime block of the subfile. If the
prime block becomes full, only those overflow blocks that have corresponding
TLRECs are accessed with two physical block accesses. For example, if
the prime block containing LRECs pointing to overflow blocks A, B, and C was
full, and there were overflow blocks for A-Z, it would take two
accesses to access the B overflow block but 24 accesses to access the Z
overflow block.
- LREC keys should be unique (for performance reasons).