• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • How To Create Updateable Views

    July 7, 2015 Ted Holt

    It has been said that perception is more important than reality. I agree, especially when such perception is implemented as a view in a relational database. When creating a view, it is good to keep two things in mind.

    First, consider that a view may be updateable or read-only. An updateable view allows the user to update rows in the table through the view, but also to insert new rows and delete rows. Read-only views may only be used when retrieving data. Both types of view are superb, and each type has its uses.

    One strong advantage of updateable views is that they decrease the effort required to maintain applications. For example, a view that mimics the format (record layout) of a table that is no longer in use permits old programs to run without having to be modified to read the table that replaced the former table.

    If you need for a view to be updateable, you have to follow a few sensible rules:

    1. The view must be built over one table–no more and no less.

    2. Each row of the view must map back to exactly one row of the underlying table.

    3. The view may include calculated columns, but you cannot update such columns.

    4. The view may not contain aggregate processing (i.e. GROUP BY, HAVING, MIN, MAX, SUM, etc.).

    5. In order to insert new rows through the view, all columns of the table that are not also in the view must be null-capable. Obviously this requirement means that the view must include the primary key in order to support inserts.

    In short . . .

    No joins.

    No SELECT DISTINCT.

    No UNION, no EXCEPT, no INTERSECT.

    No aggregate or summary columns.

    There is a way around these limitations. To update through a read-only view, use an INSTEAD OF trigger. See Michael Sansoterra’s excellent article, Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers for more information.

    Second, you must also decide whether or not inserts and updates should be allowed to violate row-selection criteria. Sometimes they should. Sometimes they shouldn’t.

    For example, assume a view that shows manufacturing orders that are in progress. The view does not include orders that have not been started, nor orders that have been completed. You may want to allow a user to update the order status to “complete” to remove the order from the view, but of course, not from the table.

    On the other hand, suppose purchasing programs use a view that selects purchased (as opposed to manufactured) items only. Those programs could be prevented from updating the type column, which would change a purchased part to a manufactured part.

    To force all rows that are inserted or updated through the view to conform to the row selection criteria of the view, use the WITH CHECK OPTION clause.

    Let me illustrate.

    Suppose you have a table of employee data.

    create table Employees
      ( ClockNumber for column Clock
                    dec (5)       not null,
        Name        varchar(20),
        Type        char(1),
        Department  char(2),
        NationalID  char(9),
        MaritalStatus for column Marital
                    char(1),
        Exemptions  dec(3),
        TelephoneNumber for column Telephone
                    char(6),
        primary key (ClockNumber))
    
    insert into employees values
    ( 101, 'Barney Fife',      'S', '07', 111, 'S', 0, 'BR-549'),
    ( 102, 'Luther Heggs',     'S', '02', 222, 'S', 0, 'AA-123'),
    ( 103, 'Theodore Ogilvie', 'S', '03', 333, 'M', 0, null),
    ( 104, 'Jesse W. Haywood', 'H', '03', 444, 'S', 0, '56565'),
    ( 105, 'Lester Calhoun',   'H', '02', 555, 'M', 0, null),
    ( 106, 'Roy Fleming',      'H', '02', 666, 'S', 0, null)
    

    Suppose you want certain users, who use a query tool on a PC, to access the contact information of hourly employees. You could give them access to an hourly contacts view.

    create view HourlyContacts
       for system name HContacts
       (ClockNumber, Name, TelephoneNumber)
     as (select ClockNumber, Name, TelephoneNumber
           from Employees
          where Type = 'H')
    

    Suppose someone inserts a new employee using that view.

    insert into HourlyContacts values
       (107, 'Henry Limpet', '70707')
    

    DB2 for i happily responds with message SQL7856 (1 rows inserted in HCONTACTS in MYLIB.)

    However, querying the hourly contacts view does not show the new employee.

    select * from HourlyContacts
    
    CLOCKNUMBER  NAME               TELEPHONENUMBER
          104    Jesse W. Haywood        56565
          105    Lester Calhoun          -
          106    Roy Fleming             -
    

    However, suppose the view were created with the check option.

    create view HourlyContacts
       for system name HContacts
       (ClockNumber, Name, TelephoneNumber)
     as (select ClockNumber, Name, TelephoneNumber
           from Employees
          where Type = 'H')
    with check option
    

    The same insert fails with message SQL0161 (INSERT or UPDATE not allowed because a resulting row does not satisfy view definition HCONTACTS in MYLIB.).

    Let’s try one more time. Add the type column to the view.

    create view HourlyContacts
       for system name HContacts
       (ClockNumber, Name, Type, TelephoneNumber)
     as (select ClockNumber, Name, Type, TelephoneNumber
           from Employees
          where Type = 'H')
    with check option
    

    Insert a salaried employee.

    insert into HourlyContacts values
       (107, 'Henry Limpet', 'S', '70707')
    

    Again DB2 heartlessly responds with SQL0161.

    But you can insert hourly employees.

    insert into HourlyContacts values
       (107, 'Henry Limpet', 'H', '70707')
    

    DB2 responds with message SQL7956 (1 rows inserted in HCONTACTS in MYLIB.)

    Here’s the data as seen through the view.

    select * from HourlyContacts 
    
    CLOCKNUMBER  NAME                  TYPE  TELEPHONENUMBER
          104    Jesse W. Haywood       H        56565
          105    Lester Calhoun         H        -
          106    Roy Fleming            H        -
          107    Henry Limpet           H        70707
    

    Keep in mind that I am only talking about inserting and updating through the view. WITH CHECK OPTION has nothing to do with inserting and updating through non-related views and the underlying table itself.

    Both updateable and read-only views have their places. It’s worth spending a little extra time before creating a view to decide whether or not the view should be updateable, and whether or not you should allow inserts and updates that don’t conform to the row selection of the view.

    RELATED STORY

    Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Fresche Solutions

    ON-DEMAND SESSION

    Protecting Your IBM i Systems from Ransomware and Other Cyber Threats

    Zero-day attacks and ransomware threats are on the rise and data that resides on IBM i is not immune. Now is the time to learn how to defend it.

    Join Marcel Sarrasin, CPO, Fresche and Pauline Brazil Ayala, VP of Operations, Trinity Guard as they introduce you to TGSuite, the next generation of IBM i security tools and dive into IFS and network security to help you learn how to configure the defenses on your system and guard your valuable data.

    In the session, Pauline and Marcel will discuss:

    • What a secure system looks like in 2022
    • Cybersecurity and auditing, data-level reporting and job activity monitoring
    • Advanced exit point security – knowing and managing who has access to your IBM i
    • Setting up alerts on critical security events as they happen
    • Managing all your LPARs from one centralized web console

    Watch Now!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Fresche Legacy:  Optimize IBM i apps; Improve business processes; Deliver modernization success
    SystemObjects:  Send SMS, email & push messages from your IBM i applications!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    cozTools Brings Subscription Pricing To Software Licensing Manufacturing Automation At A ‘Crossroads’ For IBM i Shops

    Leave a Reply Cancel reply

Volume 15, Number 13 -- July 7, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
Robot
Fresche Legacy

Table of Contents

  • Looking For Stuff With iSphere
  • How To Create Updateable Views
  • Security Architecture For Business Applications

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.