Avoid updatable Views in DB2 with fantastic SQL

Views in DB2
Views in DB2

In simple words a VIEW is “To the user, a view just looks like a table. Except for the view definition, a view does not take up space or store its own data; the data presented in a view is derived from other tables. You can create a view on existing tables, on other views, or some combination of the two. A view defined on another view is called a nested view”

SQL Query to create a view

CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N';

How to make a VIEW is READ ONLY or updatable VIEW

When you create a view, due to its structure it may be either a read-only view or an updatable view. The SELECT statement of a view determines whether the view is read-only or updatable.

Generally, if the rows of a view can be mapped to rows of the base table, then the view is updatable.

For example, the view NONFICTIONBOOKS, as you defined it in the previous example, is updatable because each row in the view is a row in the base table.

Other Key Rules for Views

The rules for whether a view is updatable are complex and depend on the SELECT statement in the definition. For example, views that use VALUES, DISTINCT, or any form of join are not directly updatable.

How will you determine a VIEW is updatable or READ-ONLY

You can easily determine whether a view is updatable by looking at the READONLY column of the VIEWS system catalog table: Y means it is read-only and N means it is not.

How to avoid a VIEW is updatable

You can write SQL in the following way:

CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N' WITH CHECK OPTION;

This view still restricts the user to seeing only non-fiction books. In addition, it prevents the user from inserting rows that do not have a value of N in the BOOKTYPE column and updating the value of the BOOKTYPE column in existing rows to a value other than N.

How to UPDATE Read-only View

There is a mechanism to allow the appearance of updating data through a read-only view: INSTEAD OF triggers. These triggers can be defined on a view to intercept UPDATE, INSERT, and DELETE against a view, and instead perform actions against other tables, most commonly the base tables the view is built upon.

How to write Master file popular program in COBOL

This is sample COBOL program for reading Master file and Transaction file, and writes modified records into output file. This is most powerful logic across all financial projects using same Technics.

IDENTIFICATION DIVISION.
       PROGRAM-ID.  STRUCT.
      *****************************************************************
      * PROGRAM TO READ INPUT FILE, UPDATE IT FROM A TRANSACTIONS     *
      * FILE, AND WRITE AN OUTPUT FILE.                               *
      * IN:  In-File CONTAINS INPUT FILE.                             *
      *      Trans-File CONTAINS THE TRANSACTIONS FILE.               *
      * OUT: Out-File CONTAINS THE UPDATED OUTPUT FILE.               *
      *****************************************************************
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
           SELECT In-File ASSIGN TO "NONSTIN.TXT"
                  ORGANIZATION IS LINE SEQUENTIAL.
           SELECT Out-File ASSIGN TO "NONSTOUT.TXT"
                  ORGANIZATION IS LINE SEQUENTIAL.
           SELECT Trans-File ASSIGN TO "NONSTTR.TXT"
                  ORGANIZATION IS LINE SEQUENTIAL.
       DATA DIVISION.
       FILE SECTION.
       FD  In-File
              BLOCK CONTAINS 0 RECORDS.
       01  In-Record.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       FD  Out-File BLOCK CONTAINS 0 RECORDS.
       01  Out-Rec.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       FD  Trans-File BLOCK CONTAINS 0 RECORDS.
       01  Trans-Record.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       WORKING-STORAGE SECTION.
       01  In-Rec.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       01  Trans-Rec.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       01  Out-Record.
           05  K                         PIC X(15).
           05  L.
               10  M                     PIC S9.
               10  N                     PIC X(4).
       01  In-EOF                        PIC X.
      *            Flag for end of file.  "Y", EOF.  "N", no EOF.
       01  Record-Count                  PIC S9(8).
      *          Record-Count counts the records read and written.
       PROCEDURE DIVISION.
       A00-Begin.
           DISPLAY "BEGINNING STRUCT PROGRAM."
           OPEN INPUT In-File, Trans-File, OUTPUT Out-File
           MOVE LOW-VALUES TO In-Rec, Trans-Rec
           PERFORM A10-READ-In-File WITH TEST AFTER
                   UNTIL In-Rec = HIGH-VALUES
           DISPLAY "END OF PROGRAM"
           CLOSE In-File, Trans-File, Out-File
           GOBACK
           .
      **** Exit
       A10-Read-In-File.
           READ In-File INTO In-Rec
             AT END MOVE HIGH-VALUES TO In-Rec
           END-READ
           PERFORM WITH TEST BEFORE UNTIL Trans-Rec >= In-Rec
              IF Trans-Rec - LOW-VALUES
                 THEN READ Trans-File INTO Trans-Rec
                           AT END MOVE HIGH-VALUES TO Trans-Rec
                      END-READ
              END-IF
              IF Trans-Rec < In-Rec
                 THEN DISPLAY "TRANSACTION IGNORED:", Trans-Rec
                      MOVE LOW-VALU"ES TO Trans-Rec
              END-IF
           END-PERFORM
           EVALUATE TRUE
             WHEN In-Rec = HIGH-VALUES CONTINUE
             WHEN Trans-Rec = In-Rec DISPLAY "DELETING:", In-Rec
             WHEN OTHER
                  DISPLAY "WRITING: ", In-Rec
                  WRITE Out-Rec FROM In-Rec
           END-EVALUATE
           .
      **** Exit
       END PROGRAM STRUCT.
NONSTIN.TXT:
111111111111111AAAA
222222222222221AAAA
222222222222222AAAA
222222222222222DDDD
444444444444441BBBB
555555555555552CCCC
NONSTTR.TXT:
111111111111111BBBB
222222222222221AAAA
222222222222222AAAA
222222222222222EEEE
444444444444441BBBB
555555555555553CCCC

How to use PERFORM TEST AFTER logic in COBOL

Perform with Test after usage in COBOL program.

IDENTIFICATION DIVISION.
       PROGRAM-ID.  Empage.
      *****************************************************************
      * PROGRAM TO READ INPUT FILE, COMPUTE PERSON'S AGE, AND WRITE   *
      * OUTPUT FILE.                                                  *
      * IN:  In-File CONTAINS INPUT FILE.                             *
      * OUT: Out-File CONTAINS INPUT FILE RECORDS WITH AGE ADDED.     *
      *****************************************************************
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
           SELECT In-File ASSIGN TO "OLDFILE.TXT"
                  ORGANIZATION IS LINE SEQUENTIAL.
           SELECT Out-File ASSIGN TO "NEWFILE.TXT"
                  ORGANIZATION IS LINE SEQUENTIAL.
       DATA DIVISION.
       FILE SECTION.
       FD  In-File BLOCK CONTAINS 0 RECORDS.
       01  In-Emp.
           02  In-Name                   PIC X(30).
           02  In-Birth-Date.
               03  In-Birth-Yr           PIC 99.
               03  In-Birth-Mo           PIC 99.
               03  In-Birth-Dy           PIC 99.
           02                            PIC X.
           02  In-Hire-Date.
               03  In-Hire-Yr            PIC 99.
               03  In-Hire-Mo            PIC 99.
               03  In-Hire-Dy            PIC 99.
       FD  Out-File BLOCK CONTAINS 0 RECORDS.
       01  Out-Emp.
           02  Out-Name                  PIC X(30).
           02  Out-Birth-Date.
               03  Out-Birth-Yr          PIC 99.
               03  Out-Birth-Mo          PIC 99.
               03  Out-Birth-Dy          PIC 99.
           02                            PIC X.
           02  Out-Hire-Date.
               03  Out-Hire-Yr           PIC 99.
               03  Out-Hire-Mo           PIC 99.
               03  Out-Hire-Dy           PIC 99.
           02  Hire-Age                  PIC 999.
       WORKING-STORAGE SECTION.
       01  EOF-In-File                   PIC X.
      *            EOF-In-File is end of file flag for In-File.
      *            "N" if no EOF.  "Y" for EOF.
       PROCEDURE DIVISION.
       A00-Begin.
           OPEN INPUT In-File, OUTPUT Out-File
           PERFORM WITH TEST AFTER UNTIL EOF-In-File = "Y"
              READ In-File
                 AT END MOVE "Y" TO EOF-In-File
                 NOT AT END
                    MOVE "N" TO EOF-In-File
                    MOVE In-Emp TO Out-Emp
                    COMPUTE Hire-Age = In-Hire-Yr - In-Birth-Yr
                    IF In-Hire-Mo < In-Birth-Mo
                       THEN COMPUTE Hire-Age = Hire-Age - 1
                    END-IF
                    IF In-Hire-Mo = In-Birth-Mo AND
                       In-Hire-Dy < In-Birth-Dy
                       THEN COMPUTE Hire-Age = Hire-Age - 1
                    END-IF
                    WRITE Out-Emp
              END-READ
           END-PERFORM
           CLOSE In-File, Out-File
           GOBACK
           .
       END PROGRAM Empage.
OLDFILE.TXT:
Able, John A.                 590201 950924
Baker, Harry M.               620511 950924
Cox, Mary T.                  730330 950924
Minow, Samuel T.              681221 950924
Smith, Joseph L.              641121 950924
Zach, Thomas W.               750913 950924

Ref: Wiley-advanced cobol