Create secondary index for NK-ISAM file
Component: | BS2000 |
Functional area: | File processingg |
Domain: | FILE |
Privileges: | STD-PROCESSING |
Function
There is a key field for each record in an NK-ISAM file, which contains the record’s primary key. The user can specify the position and length of this primary key in the ADD-FILE-LINK command (KEY-POSITION and KEY-LENGTH operands) when the file is created. These details are copied into the catalog entry (see the command SHOW-FILE-ATTRIBUTES). All primary keys are managed internally by means of a directory, called the primary index. NK-ISAM finds any required record in the file by looking up its primary key in the primary index.
The command CREATE-ALTERNATE-INDEX allows an additional, secondary, index to be set up for the file. It defines an additional key field for the records in the file. For records which already exist (which will be read in sequentially), NK-ISAM creates an internal directory, which is called the secondary index. This is made up of the secondary keys for each record, their corresponding primary keys, and the entries sorted into order of the secondary key values (see the SORT-WORK-FILE operand).
Secondary keys are supported in macro calls for the ISAM access method (see the “DMS Macros” manual [12]). When a particular secondary key value is used for a read or positioning operation within the file, NK-ISAM looks through the entries to find this key. The entry which it finds will contain the corresponding primary key value. Using the primary key determined in this way, the required record is looked up in the primary index.
Any particular secondary key value may be contained in more than one record (depending on the DUPLICATE-KEY operand). When secondary keys are being used, the primary key values must always be unique. The correspondence between the secondary and primary keys then allows the record(s) to be unambiguously identified.
The user may define up to 30 different secondary keys for a file. Each of the secondary keys which is defined, and the associated secondary index which is created for it, has a name which must be unique (see the KEY-NAME operand). Provided that the following requirements are met, the key fields may be in any required position:
For variable-length records, allowance must be made for the 4-byte record length field at the start of the record.
Any key field which is defined must lie within the record. In the case of variable-length records, the length of the shortest records will represent a limit.
Key fields may not lie within any overflow blocks, which may arise when a K-ISAM file is converted to an NK-ISAM file if the maximum length of record was used.
Secondary keys cannot be managed separately from the primary key. When a file is extended, the secondary indices are automatically extended. For large files it is better (for performance reasons) to set up the required secondary index or indices after the file has been created.
The SHOW-INDEX-ATTRIBUTES command provides information on secondary keys which have been defined, or secondary indices which have been created, for a file.
Its execution involves read access to the file (OPEN=INPUT).
The user can delete secondary indices again using the DELETE-ALTERNATE-INDEX command.
Terminating index creation:
If NK-ISAM detects an error during the creation of an index (e.g. a data record which is too short), any partly created index will be deleted.
If the index creation is terminated due to a system crash, the file is locked and can be recreated using the REPAIR-DISK-FILES command. When this is done, the secondary index is deleted.
An incompletely created index is retained, and is identified as INCOMPLETE if it is output using SHOW-INDEX-ATTRIBUTES. Attempts to open the file will be rejected with DMS0D84. The incomplete secondary index can only be deleted, and then recreated from the beginning.
Format
CREATE-ALTERNATE-INDEX | ||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
Operands
FILE-NAME = <filename 1..54>
Name of the NK-ISAM file.
The file must fulfill the following requirements:
It must be already cataloged.
It must be an NK-ISAM file, i.e. the PAM keys are at the beginning of each PAM page (BLK-CONTR=DATA).
The primary index must already exist, i.e. the file must have already been opened once in OUTPUT mode.
The file must not contain any primary keys with the same value (duplicate keys).
The primary keys must not contain any logical or value flags (see the LOGICAL-FLAG-LENGTH and VALUE-FLAG-LENGTH operands in the ADD-FILE-LINK command).
ALTERNATE-INDEX = list-poss(30): *PARAMETERS(...)
For the secondary index which is to be created, this operand specifies the index name, the position and length of the key in the record, and the permissibility of duplicate secondary key values in different records.
Up to 30 secondary indices can be defined in a list.
KEY-NAME = <name 1..8>
Defines the name of the secondary index.
The selected name must be unique. The SHOW-INDEX-ATTRIBUTES command can be used to establish which names have already been used.
KEY-POSITION = <integer 1..32496>
Defines the position of the first byte of the key field within each data record. Records of variable length begin with a 4-byte field, the record length field. The permitted positions depend on the length of the key field.
KEY-LENGTH = <integer 1..127>
Defines the length of the key field in bytes.
The key field may be up to 127 bytes long. The position of the key field within the record will depend on its position and its length. A key field may not lie within an “overflow” block. Overflow blocks are created if a file is converted from K-ISAM format to NK-ISAM format and the maximum record length is used in the K-ISAM format file.
DUPLICATE-KEY = *YES / *NO
Specifies whether the secondary keys in different records may have the same value. The default setting is YES, i.e. duplicate keys are permitted.
In this case, the secondary index may contain several entries for any particular secondary key value. These entries are sorted according to their date of creation (in time stamp order).
SORT-WORK-FILE = *STD / *LINK(...)
Specifies which disk is to be used for the sort run when the secondary index is being created, if main memory provides insufficient space.
When the sort run ends, the assigned file will be deleted.
The default setting is *STD, i.e. if it is required, the sort run will use a file named DISWORK.<tsn>.
SORT-WORK-FILE = *LINK(...)
By specifying its file link name, the user can select another file as the work file.
LINK-NAME = <name 1..8>
File link name under which the file to be used is entered into the TFT.
Return codes
(SC2) | SC1 | Maincode | Meaning |
|---|---|---|---|
0 | CMD0001 | Command executed without errors | |
32 | DMS0A37 | Internal system error | |
32 | DMS0A3A | Inconsistent control block | |
32 | DMS0A42 | Internal error on opening the file | |
32 | DMS0A43 | Internal error on closing the file | |
32 | DMS0A44 | Error on reading the file | |
32 | DMS0A45 | Error on writing to the file | |
32 | DMS0A47 | DMS error in sort part of NK-ISAM | |
32 | DMS0A48 | Internal error in sort part of NK-ISAM | |
32 | DMS0A4F | RDTFT error | |
64 | DMS0A3D | Incomplete secondary key found | |
64 | DMS0A3F | Secondary index already defined | |
64 | DMS0A30 | Remote BS2000 system has a version < V10.0A | |
64 | DMS0A31 | Specified catalog ID does not exist | |
64 | DMS0A33 | File contains duplicate primary keys | |
64 | DMS0A34 | Secondary index already exists | |
64 | DMS0A36 | File contains ISAM flags | |
64 | DMS0A39 | Invalid KEYPOS parameter | |
64 | DMS0A40 | List includes duplicated names | |
64 | DMS0A46 | Not an NK-ISAM file | |
64 | DMS0A4D | File contains duplicate secondary key | |
64 | DMS0A4E | SHARUPD = YES was specified | |
128 | DMS0A49 | Command interrupted | |
130 | DMS0A32 | Specified catalog ID not available | |
130 | DMS0A38 | Insufficient virtual memory | |
130 | DMS0A3C | Maximum number of secondary keys | |
130 | DMS0A3E | ISAM pool is overloaded |
Examples
Creation of multiple secondary indices
/show-file-attr max.file.4,inf=(org=*yes) —————————————————————————————— (1)
%0000000126 :2OSG:$USER1.MAX.FILE.4 % ------------------------------- ORGANIZATION ------------------------------- % FILE-STRUC = ISAM BUF-LEN = STD(1) BLK-CONTR = PAMKEY % IO(USAGE) = READ-WRITE IO(PERF) = STD DISK-WRITE = IMMEDIATE % REC-FORM = (V,N) REC-SIZE = 0 % KEY-LEN = 8 KEY-POS = 5 % AVAIL = *STD % WORK-FILE = *NO F-PREFORM = *K S0-MIGR = *ALLOWED %:2OSG: PUBLIC: 1 FILE RES= 126 FRE= 5 REL= 3 PAGES
/cre-alter-index file-name=max.file.4, alter-index=( (key-name=strasse,key-pos=30,key-len=20), —— (2) (key-name=ort,key-pos=50,key-len=25) ) —— (3)
/show-index-at max.file.4 ———————————————————————————————— (4)
%FILE-NAME: :2OSG:$USER1.MAX.FILE.4 % KEY-NAME KEY-POSITION KEY-LENGTH DUPKEY %---------------------------------------------------------------- % STRASSE 30 20 YES % ORT 50 25 YES
(1) | Returns information on the organization of the NK-ISAM file MAX.FILE.4. The primary key begins at the 5th byte (after a record length field of 4 bytes) and is 8 bytes long. The file contains a customer list, where the 8-digit customer number serves as the primary key. |
Two secondary indices are defined in a list in the CREATE-ALTERNATE-INDEX command (see points 2 and 3): | |
(2) | In order to enable access in a program via the street name which is contained in each record, a secondary index with the name STREET is to be created for this data field. The data field begins at the 30th byte and is 20 bytes long. Since a street name may be contained in several records, DUPLICATE-KEY=*YES is permitted (default value). |
(3) | In order to enable access in a program via the city name which is contained in each record, a secondary index with the name CITY is to be created for this data field. The data field begins at the 50th byte and is 25 bytes long. Since a city name may be included in several records, DUPLICATE-KEY=*YES is permitted (default value). |
(4) | The SHOW-INDEX-ATTRUBUTES command returns information on all secondary indices that were created for the file MAX.FILE.4. |