• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    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

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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 © 2025 IT Jungle