Secondary Indexing (Index Databases)
secondary indexdefines an alternative (or secondary) access path that overrides the underlying hierarchical access path. DL/I supports the following types of secondary indexes:
- An index to a root or dependent segment on the basis of any field in the segment
- An index to a root or dependent segment on the basis of any field in a physically dependent segment
The key field for an index can be a single field or up to five fields in the same segment concatenated in any order. A physical database can have multiple secondary indexes.
Define Secondary Index
Secondary indexes must be defined as separate databases. The segment occurrences in a secondary index database contain the values of the specified key field(s) and the pointers to the associated segment occurrences in the physical database. There are three types of segments, as follows:
- Pointer segment: The secondary index segment is known as thepointer segment.
- Source segment: The segment containing the key field(s) is known as thesource segment
- Target segment: The source and target segments can be the same or different.
Secondary indexes differ from HIDAM indexes. They allow you to index segments other than root segments. In a secondary index, the pointer segment can contain up to five concatenated fields, rather than just one field. Also, the source and target segments do not have to be the same.
Secondary indexes are defined in a manner similar to HIDAM index databases. Related statements must be included in both the index DBD and the associated physical DBD.
Sample DBD Definitions
The sample below shows the DBD definitions for a physical HDAM database (DB2) and an associated secondary index database (DBINDX2).
DBD NAME=DB2,ACCESS=HDAM, RMNAME=(GLDHDC20,5,660,850) DATASET DD1=DBHDAM,DEVICE=3350,BLOCK=2048,SCAN=1 SEGM NAME=SEG1,PARENT=0,BYTES=15 FIELD NAME=(FIELD1,SEQ,U),BYTES=5,START=1 LCHILD NAME=(SEG6,DBINDX2),PTR=INDX XDFLD NAME=XDFLD1,SEGMENT=SEG2, SRCH=FIELD2,DDATA=FIELD3 SEGM NAME=SEG2,PARENT=SEG1,BYTES=25 FIELD NAME=(FIELD2,SEQ,U),BYTES=5,START=1 FIELD NAME=(FIELD3),BYTES=10,START=6 SEGM NAME=SEG3,PARENT=SEG2,BYTES=15 FIELD NAME=(FIELD4,SEQ,U),BYTES=10,START=1 SEGM NAME=SEG4,PARENT=SEG2,BYTES=30 FIELD NAME=(FIELD5,SEQ,U),BYTES=20,START=1 DBDGEN FINISH END DBD NAME=DBINDX2,ACCESS=INDEX DATASET DD1=INDX2,DEVICE=3350,BLOCK=23, RECORD=88,SCAN=1 SEGM NAME=SEG6,PARENT=0,BYTES=15 FIELD NAME=(FIELD6,SEQ,U),START=1,BYTES=15 LCHILD NAME=(SEG1,DB2),POINTER=SINGL,INDEX=XDFLD1 DBDGEN FINISH END
Figure 15. DBD definitions for a physical and secondary database
Index DBD Statements
index DBDmust contain the following statements:
- DBD statement── ACCESS parameter must specify INDEX.
- SEGM statement── Defines the index pointer segment as the root for the index database. Only one SEGM statement is allowed.
- FIELD statement── Defines the sequence field for the pointer segment. Only one FIELD statement is allowed.
- LCHILD statement── Identifies the target segment and the physical DBD in which it is defined. The INDEX parameter specifies the name of the indexed field (XDFLD) in the associated physical DBD. Only one LCHILD statement is allowed.
Physical DBD Statements
physical DBDmust contain the following statements:
- DBD statement── ACCESS parameter must specify HISAM, HDAM, or HIDAM. While it is possible to set up a secondary index for a logical database (ACCESS=LOGICAL), it is not recommended for reasons of performance and data independence. HSAM databases are restricted to sequential access.
- LCHILD statement── Identifies the pointer segment and the index DBD in which it is defined. The PTR (pointer) parameter must specify INDX (for index). The LCHILD statement must be included under the SEGM statement for the target segment.
- XDFLD statement── Identifies a source segment and its index field. The value for the NAME parameter is referenced in the LCHILD statement in the index DBD. The SEGMENT parameter specifies the source segment. The SRCH parameter specifies the sequence field in the source segment to be used for indexing. The DDATA parameter specifies a data field in the source segment to be used for indexing.Note that the values for the SRCH and DDATA fields will be concatenated to produce the actual index-key field values. The XDFLD statement must be included under the SEGM statement for the target segment.
Restructuring a Hierarchy
If a secondary index points to a dependent segment, the effect is to restructure the hierarchy so that the dependent segment appears as the root. In the new hierarchy, the higher level segments in the original hierarchy become dependents of the new root. They appear as leftmost dependents in reverse hierarchical order. A secondary index is similar to a logical relationship in that they both restructure an underlying hierarchy. However, a secondary index is different from a logical relationship in that it can deal only with a single physical database. Logical relationships can combine segments from one or more physical databases.
The following diagram illustrates an original, underlying hierarchy and the new hierarchy that results from indexing a dependent segment (SEG2).
Full and Sparse Indexing
A secondary index can be either full or sparse.
full indexmaintains an entry for each source segment occurrence in which the search field has a value.
sparse indexmaintains entries only for selected values in the search field. Because sparse indexing is more selective than full indexing, it provides faster search times for the desired target segments.