bdfd1m1b | Database Administration |
There is no real limit to the number of different basic indexing methods available in the TPFDF product. However, the methods that follow are adequate for most applications:
Figure 52 shows an index file containing index LRECs. These LRECs reference the subfiles in a detail file.
In this example, there is only one ordinal in the index file, so algorithm #TPFDB04 is used. The detail file contains two sets of LRECs. It is organized in ascending order (UP organized) by the LREC ID (PKY=).
The LRECs in LREC ID X'80' are further organized in ascending order on field GR25DF2. The LRECs in LREC ID X'90' are organized in descending order (DOWN organized).
Figure 52. File Description for Simple Indexing
Top-level index file DBDEF FILE=GR50DF, file GR50DF - (PKY=#GR50K80, primary key X'80' - KEY1=(PKY=#GR50K80,UP), - default keys - KEY2=(R=GR50DF1,UP)), - (ITK=#GR50K80, forward path, index LREC - ID2=, type of reference - INDEX=(GR25DF,0)) indexed file, index slot 0
Detail file DBDEF FILE=GR25DF, file GR25DF - (PKY=#GR25K80, primary key X'80' - KEY1=(PKY=#GR25K80,UP), - default keys - KEY2=(R=GR25DF2,UP)), - (PKY=#GR25K90, primary key X'90' - KEY1=(PKY=#GR25K90,UP), - default keys - KEY2=(R=GR25DF3,DOWN)), - (IID=GR50DF, backward path, default path 0 - IKY=#GR50K80, index LREC - IPA=0, offset of addressing argument - ILA=0, length of addressing argument - IPK=0, offset of index key - ILK=4, length of index key - KEY1=(PKY=#GR50K80,UP), search keys for index - KEY2=(R=GR50DF1,S=0,UP)) default length assumed
Figure 53 shows an example of a file indexed by 2 files. One index file uses a 6-byte field as an index key. The other file uses a different 8-byte field as an index key.
The detail file (GR25DF) is indexed by two files, so any chain chasing in the structure GRY1DF to GR25DF is repeated for the structure GRY2DF to GR25DF. The duplicate processing is avoided when RCIDID=1234 (and ID2=(RCI)) is specified in the forward path.
Figure 53. File Description for Multiple Indexing to a Single Detail Subfile
The index files (GRY1DF and GRY2DF) must be updated each time the detail file (GR25DF) is changed. The DBDEF statements for path 0 and path 1 specify partial update paths. The two paths are combined when PATH=ALL is specified in a macro or function. This ensures that both index paths are updated at the same time.
Figure 54 shows how the IPK and ILK parameters are defined in the detail file DBDEF. IPK and ILK define the offset into and the length to use of an algorithm string passed with the ALG parameter in a macro or function. The algorithm string contains both index keys. For more information, see Creating a DBDEF Macro Definition.
Figure 54. Algorithm String for the Update Path
Figure 55 shows how path 0 is used to update or read the detail file (GR25DF). Path 0 uses the first part of the algorithm string, passed with the ALG parameter in a macro or function, as an index key to read the GRY1DF file, which locates the detail file.
Figure 55. Reading the Detail File through Index File GRY1DF
Figure 56 shows how path 2 is used to read the detail file (GR25DF). (In the definition for path 2, IMI=#BIT0 specifies that path 2 is a read-only path.) Path 2 also uses the first part of the algorithm string, passed with the ALG parameter in a macro or function, as an index key to read the GRY2DF file, which locates the detail file.
Figure 56. Reading the Detail File through Index File GRY2DF
Top-level index file 1 DBDEF FILE=GRY1DF, file GRY1DF - RCIDID=1234, RCI processing in this structure - (PKY=#GRY1K70, primary key X'70' - KEY1=(PKY=#GRY1K70,UP), - default keys - KEY2=(R=GRY1DF4,DOWN)), - (ITK=#GRY1K70, forward path, index LREC - ID2=(RCI), type of reference - INDEX=(GR25DF,0)) indexed file
Top-level index file 2 DBDEF FILE=GRY2DF, file GRY2DF - RCIDID=1234, RCI processing in this structure - (PKY=#GRY2KC0, primary key X'C0' - KEY1=(PKY=#GRY2KC0,UP), - default keys - KEY2=(R=GRY2DF5,UP), - (ITK=#GRY2KC0, forward path, index LREC - ID2=(RCI), type of reference - INDEX=(GR25DF,0)) indexed file
Detail file DBDEF FILE=GR25DF, file GR25DF - (PKY=#GR25K80, primary key X'80' - KEY1=(PKY=#GR25K80,UP), - default keys - KEY2=(R=GR25DF6,UP), - (IID=GRY1DF, backward path, default path 0 - IKY=#GRY1K70, index LREC - IPA=0, offset of addressing argument - ILA=1, length of addressing argument - IPK=0, offset of index key - ILK=6, length of index key - KEY1=(PKY=#GRY1K70,UP), search keys for index - KEY2=(R=GRY1DF4,S=0,DOWN)), - (IID=GRY2DF,PTH=1, backward path, path 1 - IKY=#GRY2KC0, index LREC - IPA=0, offset of addressing argument - ILA=0, length of addressing argument - IPK=6, offset of index key - ILK=8, length of index key - KEY1=(PKY=#GRY2KC0,UP), search keys for index - KEY2=(R=GRY2DF5,S=6,UP)), - (IID=GRY2DF,PTH=2, backward path, path 2 - IMI=#BIT0, read-only path - IKY=#GRY2KC0, index LREC - IPA=0, offset of addressing argument - ILA=0, length of addressing argument - IPK=0, offset of index key - ILK=8, length of index key KEY1=(PKY=#GRY2KC0,UP), search keys for index - KEY2=(R=GRY2DF5,S=0,UP))
Figure 57 shows a two-level index structure where
TPFDF algorithm #TPFDB02 is used to locate the required prime block in the top-level index file. The #TPFDB02 algorithm is used only as an example here, but the #TPFDBFF algorithm must be used for the lower-level files because they are indexed by another file.
Figure 57. File Description for Multiple-Level Indexing
The top-level index file is addressed using the #TPFDB02 algorithm. #TPFDB02 requires a 2-byte alphabetic input argument (addressing argument). The addressing argument part of the string is defined by IPA= and ILA=. Figure 58 shows how the IPA and ILA parameters are used in the DBDEF for the GR50DF file to specify the addressing argument part for the file GR10DF.
Figure 58. Addressing Argument and Index Key for the Top-Level Index File
Figure 59. Addressing Argument and Index Key for the Intermediate-Level Index File
Top-level index file DBDEF FILE=GR10DF, file GR10DF - (PKY=#GR10K40, primary key X'40' - KEY1=(PKY=#GR10K40,DOWN), - default keys - KEY2=(R=GR10DF7,DOWN)), - (ITK=#GR10K40, forward path, index LREC - ID2=, type of reference - INDEX=(GR50DF,0)) indexed file -
Intermediate-level index file DBDEF FILE=GR50DF, file GR50DF - (PKY=#GR50K80, primary key X'80' - KEY1=(PKY=#GR50K80,UP), - default keys - KEY2=(R=GR50DF8,UP)), - (ITK=#GR50K80, forward path, index LREC - ID2=, type of reference - INDEX=(GR25DF,0)), indexed file - (IID=GR10DF, backward path, default path 0 - IKY=#GR10K40, index LREC - IPA=0, offset of addressing argument - ILA=2, length of addressing argument - IPK=0, offset of index key - ILK=8, length of index key - KEY1=(PKY=#GR10K40,UP), search keys for index - KEY2=(R=GR10DF7,S=0,DOWN))
Detail file DBDEF FILE=GR25DF, file GR25DF - (PKY=#GR25K60, primary key X'60' - KEY1=(PKY=#GR25K60,DOWN), - default keys - KEY2=(R=GR25DF9,DOWN)), - (PKY=#GR25K90, primary key X'90' - KEY1=(PKY=#GR25K90,DOWN), - default keys - KEY2=(R=GR25DFA,DOWN)), - (IID=GR50DF, backward path, default path 0 - IKY=#GR50K80, index LREC - IPA=0, offset of addressing argument - ILA=0, length of addressing argument - IPK=2, offset of index key - ILK=6, length of index key - KEY1=(PKY=#GR50K80,UP), search keys for index - KEY2=(R=GR50DF8,S=2,UP))
Figure 60 shows a file structure where a passenger name is used to refer to a passenger detail file and another detail file. The duplicate passenger name can be avoided if you have two pointers in the same file related to the same passenger name.
Figure 60. Separate Entries for One Passenger Name
Figure 61. One Index File Pointing to Two Detail Files
Top-level index file DBDEF=GR50DF, file GR50DF - (PKY=#GR50K80, primary key X'80' - KEY1=(PKY=#GR50K80,UP), - default keys - KEY2=(R=GR50DFB,DOWN)), - (ITK=#GR50K80, forward path, index LREC - ID2=, type of reference - INDEX=(GRZ1DF,0)), indexed file, slot 0 - (ID2=, type of reference - INDEX=(GRZ2DF,1)) indexed file, slot 1
Detail files DBDEF FILE=GRZ2DF, file GRZ2DF - (PKY=#GRZ2K70, primary key X'70' - KEY1=(PKY=#GRZ2K70,UP), - default keys - KEY2=(R=GRZ2DFD,DOWN)), - (IID=GR50DF, backward path, default path 0 - IKY=#GR50K80, index LREC - IPA=0, offset of addressing argument - ILA=3, length of addressing argument - IPK=0, offset of index key - ILK=25, length of index key - KEY1=(PKY=#GR50K80,UP), search keys for index - KEY2=(R=GR50DFB,DOWN))
DBDEF FILE=GRZ1DF, file GRZ1DF - (PKY=#GRZ1K50, primary key X'50' - KEY1=(PKY=#GRZ1K50,UP), - default keys - KEY2=(R=GRZ1DFE,UP)), - (IID=GR50DF, backward path, - IKY=#GR50K80, index LREC - IFR=1, index slot 1 - IPA=0, offset of addressing argument - ILA=3, length of addressing argument - IPK=0, offset of index key - ILK=25, length of index key - KEY1=(PKY=#GR50K80,UP), search keys for index - KEY2=(R=GR50DFB,DOWN))