1 / 34

Sequential File Maintenance

Sequential File Maintenance. Sequential Files. Must access one record after another in physical order File may or may not be logically ordered Usually the Master is in sequence by some key If not in order it must be sorted before update Can reside on any type of storage device

arne
Download Presentation

Sequential File Maintenance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sequential File Maintenance

  2. Sequential Files • Must access one record after another in physical order • File may or may not be logically ordered • Usually the Master is in sequence by some key • If not in order it must be sorted before update • Can reside on any type of storage device • Updating requires • Reading current file (Old) • Applying all changes • Writing new file - reflecting changes (New) • Note not all records of the Old master will be updated but they must be contained on the New master

  3. Master Files • In most companies, a master file will be stored on a magnetic disk. • The features of magnetic media such as disk that make it ideally suited for storing master file data include the following: 1. Disks can store billions of characters. 2. Disk drives can read and write data very quickly. 3. Disk records can be any size.

  4. Master File • A file containing a companies permanent records ie. • Inventory • On hand, Sales YTD, Cost, Selling Price, Description, Vendor etc. • Employee • Name, Address, Pay Rate, Tax Data, No of dependents etc. • Customer • Name, Address, Balance Due, Sales YTD, Past Due Amt • Production Control • Quantity on Order, Build Schedule, Process Schedule

  5. Designing a Master File • The following are elements to consider when designing a master file: 1. The first field or fields should be key fields that uniquely identify the record. 2. Where possible, key fields should consist of numbers (e.g., Social Security Number rather than Employee Name, Part Number rather than Part Description, etc.). 3. Secondary key fields (e.g., Name) should follow primary key fields in a record.

  6. Designing a Master File 4. Fields should appear in a master file in order of importance (e.g., Employee Name and Address before Birth Date, etc.). 5. Be sure that fields are large enough to accommodate the data (e.g., a 10- position Last Name field, for example, is not likely to be large enough). 6. Use coded fields where possible to save space (e.g., codes are used for Marital Status, Insurance Plan Option, etc.).

  7. Transaction Files • A file that contains the periodic activity of transactions that record needed changes to a Master File ie. • Sales Activity • Sales • Price Changes • New Products • Employee changes • Change of address • New hires • Terminated Employees

  8. OLD MASTER TRANSACTION NEWMASTER UPDATEPROGRAM ERRORMESSAGES Sequential Update INPUT OUTPUT OUTPUT INPUT

  9. Sequential Update • Update existing records • Add new records • Delete obsolete records • Requires • A unique key field • Customer Number, Employee Number, Part Number • Both Transaction file and Master File must be in same sequence • New master must be kept in sequence

  10. Transaction File • Activity is batched • Usually a transaction code that defines update process ie. • A = add a new record • D = delete a record • C = change the data • Change may be based on transaction data fields that are not empty

  11. Sequential Update with Data Files OLD MASTER FILE: 111111111ADAMS 015000 NEW YORK222222222BAKER 025000 NEW YORK333333333ZIDROW 008000 NEW YORK444444444MILGROM 040000 BOSTON555555555BENJAMIN 100000 CHICAGO666666666SHERRY 007500 CHICAGO777777777BOROW 017500 BOSTON888888888JAMES 050000 NEW YORK 999999999RENAZEV 030000 NEW YORK NEW MASTER FILE: 111111111ADAMS 015000 NEW YORK222222222BAKER 028000 BOSTON333333333ZIDROW 008000 NEW YORK400000000NEW EMPLOYEE 016000 BOSTON444444444MILGROM 040000 BOSTON 555555555BENJAMIN 100000 CHICAGO 610000000NEW EMPLOYEE II 018000 NEW YORK777777777BOROW 055000 NEW YORK888888888JAMES 050000 NEW YORK999999999RENAZEV 030000 NEW YORK SEQUENTIALUPDATE TRANSACTION FILE: 222222222 028000 C222222222 BOSTON C400000000NEW EMPLOYEE 016000 BOSTON A500000000 020000 C610000000NEW EMPLOYEE II 018000 CHICAGO A610000000 NEW YORK C 666666666SHERRY D777777777 055000 C888888888JAMES 017500 NEW YORK A ERROR MESSAGES: NO MATCH 500000000 DUPLICATE ADDITION 888888888

  12. Data Validation • One Step Validation • Data fields audited to contain valid values • Relationship between Transaction file and Master file checked for validity • Error listing identifying the errors and transactions • Two Step Validation • Data fields audited for valid values • File relationship not checked • Output • Error listing identifying errors and transactions • File containing accepted transactions

  13. The Files UsedControl Listing or Audit Trail • A print file or control listing is usually created during a sequential file update. • Such a print file would list: (1) changes made to the master file (2) errors encountered during processing (3) totals to be used for control and checking purposes.

  14. Update Audit Trail • Identify the transactions in error • Use transaction record image • Identify all errors relative to a transaction • Do not attempt to update Master file • Update Counts • Old Master records read • Master records Added • Master Records Deleted • Master Records Changed • New Master Records Written • Transaction Records Read • Transaction Records Accepted

  15. MASTERAS OF 1/1 JANUARYTRANSACTIONS MASTERAS OF 2/1 MASTERAS OF 3/1 FEBRUARYTRANSACTIONS Two-period Sequential Update UPDATEPROGRAM UPDATEPROGRAM GRANDFATHER FATHER SON Processing done March 1 Processing done February 1

  16. TRANSACTIONFILE OLD MASTER NEW MASTER VALIDTRANSACTIONS Step 1: VALIDATE TRANSACTIONS STEP 2: UPDATE ERRORMESSAGES ERRORMESSAGES Sequential Update with Data Validation

  17. How Transaction Records Are Processed A. T-KEY = M-KEY 1. Delete the master record if T-CODE indicates deletion. 2. Change or update the master record if T- CODE indicates update. 3. Process the transaction record as an error if T-CODE indicates add a record.

  18. How Transaction Records Are Processed B. T-KEY < M-KEY 1. Add the transaction record to the master file if T-CODE indicates a new record. 2 . Process the transaction record as an error if T-CODE does not indicate a new record. C. T-KEY > M-KEY 1. Rewrite the master record as is.

  19. Balance Line Algorithm • Essence of the problem is to keep the files (Transaction and Old Master) in sync • All updating is performed on a copy of the Master file record in Working-Storage • Problem is determining which one to read from andwhen to write a New Master file record • Algorithm uses the concept of ACTIVE Key • A WS data field – the same format and size as the Record Key

  20. Balance Line Algorithm • Assign ACTIVE Key a value based on the lesser of OLD Master File key and Transaction File keyChoosing the ACTIVE Key • If Trans-Key < OM-Key then Move Trans-Key to ACTIVE-Key Else Move OM-Key to ACTIVE-KeyEnd-If

  21. Balance Line Algorithm • Processing is determined by the Key = to the ACTIVE KeyDo While ACTIVE Key Not = High-ValuesIF OM-Key = ACTIVE-Key then Move Old-Master to New Master WS Read Old-Master End-If

  22. Balance Line Algorithm • Do While Trans-Key = Active-Key Apply Transaction - If Valid Read Another Transaction RecordEnd-Doat this point Trans-Key is > ACTIVE KeyIf No Deletion to be Processed then Write New-Master-File From New-Master-WSEnd-IfChoose the Next ACTIVE KeyEnd-Do

  23. UPDATE MASTERFILE READ TRANSACTIONFILE READ OLD-MASTER FILE CHOOSE ACTIVE KEY PROCESS ACTIVE KEY BUILD WS MASTER APPLY TRANSTO MASTER CHOOSE ACTIVE KEY WRITE NEW MASTER READ OLD-MASTER FILE ADD NEW RECORD CORRECT EXISTING RECORD DELETE EXISTING RECORD READ TRANSACTION FILE Hierarchy Chart for Sequential Update

  24. Other Issues • OPEN OUTPUT EXTENDED • Only for Additions to file • Works okay if you are not concerned about keeping the file in sequence • Direct Access Devices (disk) • Update in place • REWRITE verb • Record must be read before REWRITE can be issued • Can’t be used with Additions or Deletions

  25. The REWRITE Statement for a Disk File Opened as I-O • Disks can serve as both input and output • Thus, it is possible to read a disk record, make changes directly to the same record, and rewrite it or update it in place. • A disk file, for example MASTER FILE, can be opened as I-O, which means records from the disk will be accessed, read, changed, and rewritten: OPEN I-OMASTER-FILE REWRITECUSTOMER-MASTER-RECORD Note: A REWRITE must be preceded by a READ. NB: there is no Invalid Key option

  26. Using an Activity-Status Field for Designating Records to be Deleted • When using rewrite • We need a different procedure for deleting records. • One common technique • Begin each record with a one-character activity-status code that precedes the key field. • Assign a value to activity-status code • “A” for Active • “D” for Deleted

  27. The EXTEND Option • Records may be added to the end of a sequential disk (or tape) file by using the following OPEN statement: OPEN EXTENDfile-name • When the OPEN EXTEND statement is executed, the disk pointer is positioned at the end of the file, immediately after the last record. • If no file exists a new one will be created – result is the same as OPEN OUTPUT • This is a common technique used to collect batches of transaction over a period of time • Note: the same can be accomplished on the Mainframe by specifying DISP=MOD

  28. The EXTEND Option • A WRITE statement, then, will add records to the end of this file. • If all records to be added have key fields in sequence that are greater than those currently on the master, then the entire file will be in the correct order. • The records that are added are not in sequence, the file must be sorted before it is processed again.

  29. Sequential File I/O • The following is a chart of permissible input/output statements depending on how a sequential file was opened: StatementINPUT OUTPUT I-O EXTEND READ X X WRITE X X REWRITE X Operations ADDX X CHANGEX X DELETEX X

  30. Hierarchy Chart for Data Validation Program

  31. Validate Transaction File 000000000BOROW JSATL07 A000000000BOROW JS 10000C000000000BOROW JS 20000C100000000GRABER P 30000222222222NEW GUY RT A333333333ESMAN TNNY 09 A400000000MOLDOF BLATL15 A444444444RICHARDS IM 05000C555555555JORDAN BOS07 A700000000MILGROM A D666666666JOHNSON M NYC12 A800000000VASQUEZ C 55000C800000000VASQUEZ C C999999999GILLENSON MANYC10 A (a) Transaction File

  32. (continued) INVALID TRANSACTION CODE 100000000GRABER P 30000MISSING LOCATION CODE 222222222NEW GUY RT AMISSING OR NON-NUMERIC COMMISSION RATE 222222222NEW GUY RT A INVALID LOCATION CODE 333333333ESMAN TNNY 09 AMISSING NAME OR INITIALS 555555555JORDAN BOS07 ASOCIAL SECURITY NUMBER OUT OF SEQUENCE 666666666JOHNSON M NYC12 AMISSING OR NON-NUMERIC SALES AMOUNT 800000000VASQUEZ C C (b) Error Messages 000000000BOROW JSATL07 A000000000BOROW JS 10000C000000000BOROW JS 20000C400000000MOLDOF BLATL15 A444444444RICHARDS IM 05000C 700000000MILGROM A D 800000000VASQUEZ C 55000C 999999999GILLENSON MANYC10 A (c) Valid Transaction File

  33. Test Data 100000000GRABER P ATL1500000000200000000RUBIN MABOS0800020000300000000ANDERSON IRBOS1000113000400000000MOLDOF BLATL1500000000500000000GLASSMAN JSNYC1000045000600000000GRAUER RTNYC0800087500700000000MILGROM A SF 0900120000 800000000VASQUEZ C ATL1200060000900000000CLARK E NYC0700002500 (a) Old Master 000000000BOROW JSATL07 A000000000BOROW JS 10000C000000000BOROW JS 20000C400000000MOLDOF BLATL15 A444444444RICHARDS IM 05000C 700000000MILGROM A D 800000000VASQUEZ C 55000C 999999999GILLENSON MANYC10 A (b) Valid Transaction File (Output of Edit Program)

More Related