Sequential file maintenance
This presentation is the property of its rightful owner.
Sponsored Links
1 / 34

Sequential File Maintenance PowerPoint PPT Presentation


  • 134 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sequential file maintenance

Sequential File Maintenance


Sequential files

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


Master files

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.


Master file

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


Designing a master file

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.


Designing a master file1

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.).


Transaction files

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


Sequential update

OLD MASTER

TRANSACTION

NEWMASTER

UPDATEPROGRAM

ERRORMESSAGES

Sequential Update

INPUT

OUTPUT

OUTPUT

INPUT


Sequential update1

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


Transaction file

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


Sequential update with data files

Sequential Update with Data Files

OLD MASTER FILE:

111111111ADAMS015000 NEW YORK222222222BAKER025000 NEW YORK333333333ZIDROW008000 NEW YORK444444444MILGROM040000 BOSTON555555555BENJAMIN100000 CHICAGO666666666SHERRY007500 CHICAGO777777777BOROW017500 BOSTON888888888JAMES050000 NEW YORK 999999999RENAZEV030000 NEW YORK

NEW MASTER FILE:

111111111ADAMS015000 NEW YORK222222222BAKER028000 BOSTON333333333ZIDROW008000 NEW YORK400000000NEW EMPLOYEE016000 BOSTON444444444MILGROM040000 BOSTON 555555555BENJAMIN100000 CHICAGO 610000000NEW EMPLOYEE II018000 NEW YORK777777777BOROW055000 NEW YORK888888888JAMES050000 NEW YORK999999999RENAZEV030000 NEW YORK

SEQUENTIALUPDATE

TRANSACTION FILE:

222222222028000 C222222222 BOSTON C400000000NEW EMPLOYEE016000 BOSTON A500000000020000 C610000000NEW EMPLOYEE II018000 CHICAGO A610000000 NEW YORK C 666666666SHERRY D777777777055000 C888888888JAMES017500 NEW YORK A

ERROR MESSAGES:

NO MATCH500000000

DUPLICATE ADDITION888888888


Data validation

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


The files used control listing or audit trail

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.


Update audit trail

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


Two period sequential update

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


Sequential update with data validation

TRANSACTIONFILE

OLD MASTER

NEW MASTER

VALIDTRANSACTIONS

Step 1: VALIDATE

TRANSACTIONS

STEP 2: UPDATE

ERRORMESSAGES

ERRORMESSAGES

Sequential Update with Data Validation


How transaction records are processed

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.


How transaction records are processed1

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.


Balance line algorithm

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


Balance line algorithm1

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


Balance line algorithm2

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


Balance line algorithm3

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


Sequential file maintenance

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


Other issues

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


The rewrite statement for a disk file opened as i o

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


Using an activity status field for designating records to be deleted

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


The extend option

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


The extend option1

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.


Sequential file i o

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


Hierarchy chart for data validation program

Hierarchy Chart for Data Validation Program


Validate transaction file

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


Continued

(continued)

INVALID TRANSACTION CODE100000000GRABER P 30000MISSING LOCATION CODE222222222NEW GUY RT AMISSING OR NON-NUMERIC COMMISSION RATE222222222NEW GUY RT A INVALID LOCATION CODE333333333ESMAN TNNY 09 AMISSING NAME OR INITIALS555555555JORDAN BOS07 ASOCIAL SECURITY NUMBER OUT OF SEQUENCE666666666JOHNSON M NYC12 AMISSING OR NON-NUMERIC SALES AMOUNT800000000VASQUEZ 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


Sequential file maintenance

Test Data

100000000GRABERP ATL1500000000200000000RUBINMABOS0800020000300000000ANDERSONIRBOS1000113000400000000MOLDOFBLATL1500000000500000000GLASSMANJSNYC1000045000600000000GRAUERRTNYC0800087500700000000MILGROMA SF 0900120000800000000VASQUEZC ATL1200060000900000000CLARKE 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)


  • Login