Indexed Files

1. Indexed Files

2. Direct Access Files • Allows records to be accessed directly • Common file organizations • Indexed • Relative • Indexed file – Two components • Data - Unique key identifies each record • Index - Key and disk pointer to disk interval • Separate • Imbedded

3. Indexed Files

4. Index File - SELECT CLAUSE • Required entries • ORGANIZATION IS INDEXED • ACCESS IS SEQUENTIALRANDOMDYNAMIC • RECORD KEY IS data-name identifies the primary record key within the file’s record description

5. Index File - SELECT CLAUSE • Optional entries • [ALTERNATE RECORD KEY IS data-name-2 [WITH DUPLICATES]] • [FILE STATUS IS data-name-3] • An optional working storage item to record the status of I/O operations

6. Creating an Indexed File • Input is sorted using RECORD KEY as primary sort sequence • For loading purposes the indexed file is defined for SEQUENTIAL access • Duplicates must be removed from the Input file or trapped • INVALID KEY clause • Or FILE STATUS

7. Creating an Indexed File • File defined as SEQUENTIAL access • File is OPENed for OUTPUT • WRITE record-name INVALID KEY- error trapping imperative statements NOT INVALID KEY imperative statementsEND-WRITE

8. Reading an Indexed File - Sequentially • ACCESS IS SEQUENTIAL – in SELECT Statement • File OPEN INPUT • READ file-name [KEY IS data-name] INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-READ • Alternatively you can use the READ with AT END / NOT AT END format

9. Reading an Indexed File - Randomly • ACCESS IS RANDOM in SELECT statement • File OPEN INPUT • Value of RECORD KEY must be set prior to READ • READ file-name [KEY IS data-name] INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-READ • When RAMDOM ACCESS is used there is no end of file checking

10. Updating an Index File • Access is defined as SEQUENTIAL or RANDOM • File OPEN I-O • A Read must precede any write • REWRITE record-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-REWRITE

11. Deleting records from an Indexed File • If access is SEQUENTIAL • READ must precede the DELETE • If access is RANDOM • the RECORD KEY must be set prior to the DELETE • DELETE file-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-DELETE

12. DYNAMIC Access - START • File can be read both sequentially and randomly • ACCESS IS DYNAMIC in SELECT statement • START file-name KEY IS {=, >, >=} data-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-START • START simply positions the file’s record pointer a READ must follow

13. DYNAMIC Access - READ • READ file-name NEXT RECORD AT END imperative statements NOT AT END imperative statementsEND-READ

14. Relative Files

15. Relative Files • Data - • Records are accessed in a file relative to the beginning of the file • ACCT# 0001 will the first record in the file • ACCT# 0534 will be the 534th record in the file • Disk space must be reserved in advance • Every record should be primed HIGH-VALUES or LOW-VALUES • Some files/records the Record key readily translates into a relative file location • Customer records - ACCT # • Employee records - Employee#

16. Relative Files • Record key may require some sort of conversion (hashing) to determine the relative position • SSNumber - 321301670 - 321,301,670th record ?? • The remainder algorithm • Key / N ( where N is the number of expected records) • Take the remainder + 1 to determine relative key • Example if N = 5500 take 321301670 / 5500 • Remainder = 2670 Relative key = 2671 • Not every record location is filled with data

17. Relative - SELECT CLAUSE • Required entries • ORGANIZATION IS RELATIVE • ACCESS IS SEQUENTIALRANDOMDYNAMIC • RELATIVE KEY IS data-nameidentifies a WS item that contains the record number to be accessed • Unsigned Numeric • > 0 and <= number of records pre-allocated • Optional if ACCESS IS SEQUENTIAL

18. ALLOCATING a Relative File • Pre-format a file with number of records needed • RELATIVE file is defined for SEQUENTIAL access • Use a WRITE statement to add records • EXAMPLE –MOVE LOW-VALUES TO CUTOMER-MASTER-RECORD PERFORM 1000 TIMES WRITE CUSTOMER-MASTER-RECORD END-PERFORM

19. LOADING A RELATIVE FILE • Input file not necessarily sorted • ACCESS IS RANDOM • Calculate the relative key if necessary • Need to READ the relative file first to be sure a record does not exist in the location • If the record is all LOW-VALUES okay to add at this location using a REWRITE

20. Reading a Relative File Sequentially • SEQUENTIAL access • File OPEN INPUT • READ file-name AT END imperative statements NOT AT END imperative statementsEND-READ • Be sure to check for null records before processing

21. Reading a Relative File - Randomly • RANDOM access • File OPEN INPUT • RELATIVE KEY must be set prior to READ • READ file-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-READ • must check the record contents to verify this is the record wanted

22. Updating a Relative File • Access defined as SEQUENTIAL or RANDOM • File OPEN I-O • A Read must precede any write • May require hashing the record key to RELATIVE KEY before accessing • REWRITE record-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-REWRITE

23. Deleting records from a RELATIVE File • If access is SEQUENTIAL a READ must precede the DELETE • If RANDOM the RELATIVE KEY must be set prior to the DELETE • DELETE file-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-DELETE

24. DYNAMIC Access - START • File can be read both sequentially and randomly • START file-name KEY IS {=, >, >=} data-name INVALID KEY imperative statements NOT INVALID KEY imperative statementsEND-START • START simply positions the file’s record pointer a READ must follow

25. DYNAMIC Access - READ • READ file-name NEXT RECORD AT END imperative statements NOT AT END imperative statementsEND-READ