bdfd1m1d | Database Administration |
B+Tree indexing is a method of accessing and maintaining data. It should be used for large files that have unusual, unknown, or changing distributions because it reduces I/O processing when files are read. Also consider B+Tree indexing for files with long overflow chains.
A B+Tree file consists of a data file, which contains logical records (LRECs), and an index file, which contains technical logical records (TLRECs). The B+Tree index file, which consists of blocks (also called nodes), is maintained internally by the TPFDF product. The index file has its own file ID, DSECT, and DBDEF statements. The prime block of the B+Tree index file (also called the root node) is pointed to by the header in the prime block of the B+Tree data file.
B+Tree indexing is similar to block indexing but has the following advantages:
Notes:
B+Tree index file node blocks contain TLRECs that contain file addresses of the blocks at a lower level of a file. Those lower-level blocks may be other node blocks or data blocks. TLRECs in node blocks have the following layout:
B+Tree data blocks are the same for B+Tree files as they are for other files except the STDSBA field in the prime block header points to the root node of the B+Tree index.
To use B+Tree indexing, set the DBDEF macro parameters, equivalent DSECT parameters, or equivalent default values for a B+Tree data file as follows:
Also, the B+Tree data file DBDEF must have the following:
If you decide to use B+Tree indexing support, keep the following points in mind:
Number of TLRECs = (block size - 26) / (8 + key size)
A data file that uses B+Tree indexing has a B+Tree index file associated with it. The data file consists of data blocks that contain LRECs. The B+Tree index file consists of node blocks that contain TLRECs.
Figure 64 shows data file, GR91SR, which uses B+Tree index file IR70SR. The figure only shows a portion of the index and data files and is not intended to show a complete B+Tree structure. Data file GR91SR shows 4 data blocks. B+Tree index file IR70SR shows a root node and 4 leaf nodes.
Figure 65 shows the DSECT and the DBDEF statements for GR91SR. Figure 66 shows the DSECT and the DBDEF statements for IR70SR.
Figure 65 shows part of the DSECT and DBDEF for data file GR91SR, which uses B+Tree indexing. No matter what data is in an LREC, it is organized according to this definition.
The DBDEF includes statements that are necessary for recoup to perform single-ECB chain chasing. Chain chasing a B+Tree file involves chasing a normal chain of data blocks and a companion chain of node blocks.
Figure 65. B+Tree Data File DSECT and DBDEF
&SW00WID SETC 'B073' ** FILE ID &SW00TQK SETC '15' ** HIGHEST TLREC GR91SIZ DS H ** SIZE OF LOGICAL RECORD GR91KEY DS X ** LOGICAL RECORD IDENTIFIER #GR91K80 EQU X'80' ** LOGICAL RECORD KEY X'80' GR91ORG EQU * ** START OF LOGICAL RECORD DESCRIPTION GR91SAL DS CL5 ** SALARY GR91DPT DS CL4 ** DEPARTMENT GR91NAM DS CL6 ** LAST NAME GR91E80 EQU * DBDEF FILE=GR91SR, NODEID=B070, ** B+TREE INDEX FILE KEYCHECK=YES, ** REQUIRED FOR A B+TREE FILE UNIQUE=YES, ** REQUIRED FOR A B+TREE FILE (ID3=(CHK0),RID=B070,ADR=STDSBA-STDREC), (PKY=#GR91K80, ** KEY x'80' KEY1=(PKY=#GR91K80,UP), ** UP ORG ON PKY KEY2=(R=GR91SAL,DOWN), ** DOWN ORG ON SALARY KEY3=(R=GR91NAM,UP)), ... ** UP ORG ON LAST NAME
Use the sample B+Tree index file DSECT, SAMTSR, to build your own DSECT. You can add statements to define a B+Tree index file with its own characteristics (for example, file ID, WRS size, and so on), but do not change the existing statements. The only DBDEF override values that you can use are:
Figure 66 shows part of the DSECT and DBDEF for B+Tree index file IR70SR.
Figure 66. B+Tree Index File DSECT and DBDEF
&SW00WID SETC 'B070' ** FILE ID &SW00RBV SETC '#TPFDBFF' ** FILE ALGORITHM &SW00OP1 SETC '00000000' ** OPT BYTE1 &SW00OP2 SETC '00000110' ** OPT BYTE2 &SW00OP3 SETC '00000000' ** OPT BYTE3 &SW00TQK SETC '02' ** HIGHEST TLREC &SW00NOC SETA 0 ** NUMBER OF CHAINS -FOR ADD CURRENT ONLY- &SW00PIN SETC '00' ** ENSURE NODES ARE NEVER PACKED IR70SIZ DS H ** SIZE OF VARIABLE LEN LREC IR70KEY DS X ** PRIMARY KEY IR70ORG EQU * ** START OF LOGICAL RECORD DESCRIPTION IR70FA1 DS XL4 ** LOWER LEVEL FADDR IR70RC1 DS XL1 ** RECORD CODE CHECK IR70A03 DS 0CL1 ** KEY FIELDS IR70E03 EQU * ** END OF LOGICAL RECORD WITH KEY = X'03' IR70FA2 DS XL4 ** LOWER LEVEL FADDR IR70RC2 DS XL1 ** RECORD CODE CHECK IR70A04 DS 0CL1 ** KEY FIELDS IR70E04 EQU * ** END OF LOGICAL RECORD WITH KEY = X'04' DBDEF FILE=IR70SR,TRS=0,NODE=YES
The DBDEF shown in Figure 65 includes statements necessary for recoup to perform single ECB chain chasing. This may not be adequate for large data files. As an alternative, you can define the DBDEF for the B+Tree data and index files to allow multiple ECB chain chasing. Figure 67 shows one example of how multiple ECB chain chasing can be defined. Depending on the size of the chains and their location in the overall data structure, different methods of chain chasing might be necessary in each customer environment.
Figure 67. DBDEF for Multiple ECB Chain Chasing
STDHD REG=R14 DBDEF FILE=GR91SR,PFC=-1,DELEMPTY=YES, NODEID=B070, KEYCHECK=YES, UNIQUE=YES, (PKY=#GR91K80, KEY1=(PKY=#GR91K80,UP), KEY2=(R=GR91SAL,DOWN), KEY3=(R=GR91NAM,UP)), (ID3=(CHK0),RID=B073,FNR=2,ADR=STDFCH-STDREC,CDO=CHK), (ID3=(CHK0),RID=B070,ADR=STDSBA-STDREC) DBDEF FILE=GR91SR,FVN=1,PFC=-1, RCT=0,BOR=0,EO#=0,EOR=0 DBDEF FILE=GR91SR,FVN=2, RCT=0,BOR=0,EO#=0,EOR=0 DBDEF FILE=IR70SR,TRS=0,NODE=YES, (ITK=X'04',ID2=,FNR=1,RID=STDSBA-STDREC,ADR=IR70FA1-IR70REC) DBDEF CDLBL=CHK L R14,EBCCR0 Load base of block STDHD REG=R14 OC STDSBA,STDSBA Is root node there ? BZ DO_FCH No - chain chase forward chains B 4(,R6) Yes - don't chain chase fch DO_FCH B 8(,R6) Chain chase forward chains
The chain chasing of the structure in Figure 67 is as follows: