Some views can be used to INSERT, UPDATE or DELETE data in an underlying physical file or SQL table. This example shows how the Database Engineer can construct the SQL view to be read only.

read only views.sql

-- Setup a sample database
call qsys.create_sql_sample('HIS_STORE');
set schema his_store;

-- Create a view over sales. The view qualifies as an updateable and deletable view.
--
-- Background:
-- Deletable views: A view is deletable if an INSTEAD OF trigger for the delete operation has been defined for the view, or if all of the following are true:
-- ================
--    The outer fullselect identifies only one base table or deletable view that is not a catalog table or view. 
--      It cannot be a nested table expression, table function, or a table with a FOR SYSTEM_TIME clause.
--    The outer fullselect does not include a VALUES clause.
--    The outer fullselect does not include a GROUP BY clause or HAVING clause.
--    The outer fullselect does not include aggregate functions in the select list.
--    The outer fullselect does not include a UNION, UNION ALL, EXCEPT, or INTERSECT operator.
--    The outer fullselect does not include the DISTINCT clause.
--
-- Updatable views: A view is updatable if an INSTEAD OF trigger for the update operation has been defined for the view, or if all of the following are true:
-- ================
--    Independent of an INSTEAD OF trigger for delete, the view is deletable
--    At least one column of the view is updatable.
--
-- A column of a view is updatable if an INSTEAD OF trigger for the update operation has been defined for the view, or if the corresponding result column of the subselect is derived solely from a column of a table or an updatable column of another view (that is, it is not derived from an expression that contains an operator, scalar function, constant, or a column that itself is derived from such expressions).
--
-- Insertable views: A view is insertable if an INSTEAD OF trigger has been defined for the view, or if at least one column of the view is updatable.
-- ================
--
-- Read-only views: A view is read-only if it is not deletable.
-- ================

create or replace view updateable_sales as
    select sales_date, sales_person, region, sales
      from sales;
 
select * from updateable_sales
 order by sales_date desc 
 limit 1;
 
update updateable_sales set 
  sales = sales + 10;
  
select * from updateable_sales
 order by sales_date desc 
 limit 1;

delete from updateable_sales 
  where sales = 13;
stop;

--
-- Q: What if you needed your view to not be updateable or deletable (aka read-only)
--
-- A: Violate the requirements above in some manner.
--    In this example, I use UNION ALL to add in an empty table.
-- 
create or replace view not_updateable_sales as
    select sales_date, sales_person, region, sales from sales
    union all
    select sales_date, sales_person, region, sales from sales where 0=1;
    
select * from not_updateable_sales
 order by sales_date desc 
 limit 1;
 
update not_updateable_sales set 
  sales = sales + 10;
--  Failure: 
--  SQL State: 42807 Vendor Code: -150
--  Message: [SQL0150] View, index, or table NOT_UPDATEABLE_SALES in HIS_STORE read-only.
--  

delete from not_updateable_sales limit 1;
--  Failure: 
--  SQL State: 42807 Vendor Code: -150
--  Message: [SQL0150] View, index, or table NOT_UPDATEABLE_SALES in HIS_STORE read-only.
--  
  
stop;

--
-- Alternative solution:
--
create or replace view robs_try
as (select m.* from sales m cross join table(values(1)) x);
 
update robs_try set 
  sales = sales + 10;

delete from  robs_try  limit 1;