Does your physical data model include a virtual layer? If no, this gist is for you...

virtually done.sql

--
-- Subject: Establishing a virtual layer in the data model using SQL Views
-- Author: Scott Forstie  
-- Date  : Rocktober, 2021
-- Features Used : This Gist uses QSYS2.SYSFILES, QSYS2.SYSCOLUMNS2, dynamic SQL, and SQL PL
-- Prereq:  IBM i 7.3 or higher, with the Db2 PTF Group level from September 9, 2021 or later
--
-- Function - The request was, I don't have a DBE.. I don't have SQL Views... how do I get started with
--            shifting users and applications away from directly consuming the physical files?
--
--

-- Don't have a library named COOLSTUFF? Execute this dynamic compound statement to create it
begin
  declare dontcare integer;
  declare continue handler for sqlexception set dontcare = 1;
  call qsys2.qcmdexc('crtlib coolstuff');
end;

create or replace function coolstuff.build_view_statement(p_table_schema varchar(128) for sbcs data, 
                                                          p_table_name varchar(128) for sbcs data,
                                                          p_view_name varchar(128) for sbcs data,
                                                          p_view_system_name varchar(128) for sbcs data default null)
returns clob(1M) for sbcs data 
no external action
modifies sql data 
not fenced 
not deterministic
set option usrprf = *USER, dynusrprf = *user, commit = *none
begin
  declare v_column_name varchar(128) for sbcs data;
  declare v_system_column_name varchar(10) for sbcs data;
  declare v_rcdfmt varchar(10) for sbcs data;
  declare final_column_text clob(100K) for sbcs data default '';
  declare final_select_text clob(100K) for sbcs data default ' select ';
  declare final_create_view_text clob(100K) for sbcs data default ' create view ';

  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare lc integer default 0;
  declare local_sqlcode integer;
  declare local_sqlstate char(5);
  declare v_message_text varchar(70) for sbcs data;
  
  declare column_cursor_stmt_text varchar(2000) for sbcs data default 
  'select
     qsys2.delimit_name(column_name), 
     qsys2.delimit_name(system_column_name) 
   from qsys2.syscolumns2 c
     where table_schema = ? and table_name = ? 
     AND HIDDEN = ''N'' 
   order by ordinal_position';   -- Don't include hidden columns
  declare column_cursor cursor for column_cursor_stmt;

  declare continue handler for sqlexception 
    begin
    get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
    set v_message_text = 'coolstuff.view_column_names() failed with: ' concat local_sqlcode concat ' AND ' concat local_sqlstate;
    signal sqlstate 'QZY01' set message_text = v_message_text;
    set at_end = 1;
  end;
  declare continue handler for not_found set at_end = 1;

  prepare column_cursor_stmt from column_cursor_stmt_text;
  open column_cursor using p_table_schema, p_table_name;
  set at_end = 0;
  fetch from column_cursor into v_column_name, v_system_column_name; 
  
  while (at_end = 0) do   
    set lc = lc + 1;
    if (lc > 1) then 
      set final_column_text = final_column_text concat ', ';
      set final_select_text = final_select_text concat ', ';
    end if;
    set final_select_text = final_select_text concat v_column_name;
    if (v_column_name = v_system_column_name) then
      set final_column_text = final_column_text concat v_column_name;
    else
      set final_column_text = final_column_text concat v_column_name concat ' FOR COLUMN ' concat v_system_column_name;
    end if; 
    fetch from column_cursor into v_column_name, v_system_column_name;   
  end while;   
  close column_cursor;
  
  set final_select_text = final_select_text concat ' from ' concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(p_table_name);
  
  set final_create_view_text = final_create_view_text concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(p_view_name) 
     concat case when p_view_system_name is not null then ' for system name ' concat qsys2.delimit_name(p_view_system_name) else '' end 
     concat ' ( ' concat final_column_text concat ' ) as ( '  concat final_select_text concat ' ) ';

  select format_name into v_rcdfmt from qsys2.sysfiles 
    where table_schema = p_table_schema and
          table_name   = p_table_name;
  if (v_rcdfmt is not null) then 
    set final_create_view_text = final_create_view_text concat ' rcdfmt ' concat v_rcdfmt;
  end if;
  return final_create_view_text;
end;       
stop;
-- test it
values coolstuff.build_view_statement(
    P_TABLE_SCHEMA     => 'TOYSTORE', 
    P_TABLE_NAME       => 'SALES', 
    P_VIEW_NAME        => 'SALESV',
    P_VIEW_SYSTEM_NAME => default);

stop;

create or replace procedure coolstuff.build_views_over_physicals(p_table_schema varchar(128) for sbcs data)
external action
modifies sql data 
dynamic result sets 1 
set option usrprf = *USER, dynusrprf = *user, commit = *none
begin
  declare v_table_name varchar(128) for sbcs data;
  declare v_view_name varchar(128) for sbcs data;
  declare v_system_table_name varchar(10) for sbcs data; 
  declare v_owner varchar(10) for sbcs data;
  declare v_object_audit varchar(10) for sbcs data;
  
  declare view_library_name varchar(10) for sbcs data;
  declare view_object_name varchar(10) for sbcs data;
  declare create_view_text clob(1M) for sbcs data;
  declare transfer_ownership_text varchar(500) for sbcs data;
  declare chgobjaud_cmd varchar(500) for sbcs data;
  declare grtobjaud_cmd varchar(500) for sbcs data;
  
  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare lc integer default 0;
  declare local_sqlcode integer;
  declare local_sqlstate char(5);
  declare v_message_text varchar(100) for sbcs data; 
  
  declare physical_files_stmt_text varchar(2000) for sbcs data default 
  'select table_name, system_table_name, file_owner
     from qsys2.sysfiles
     where table_schema = ? and
           native_type  = ''PHYSICAL'' and
           file_type    = ''DATA''     and
           program_described = ''NO'' and
           number_members = 1';
  declare physical_files_cursor cursor for physical_files_cursor_stmt;
  declare views_created_cursor cursor for select * from session.views_created;

  declare continue handler for sqlexception 
    begin
    get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate, v_message_text = message_text;
    --set v_message_text = 'coolstuff.build_views_over_physicals() failed with: ' concat local_sqlcode concat ' AND ' concat local_sqlstate;
    --signal sqlstate 'QZV01' set message_text = v_message_text;
    --set at_end = 1;
  end;
  declare continue handler for not_found set at_end = 1;
  
  -- Populate the list of objects that were created
  declare global temporary table session.views_created (
        table_schema            varchar(258) for sbcs data,
        table_name              varchar(258) for sbcs data,
        view_schema             varchar(258) for sbcs data,
        view_name               varchar(258) for sbcs data,
        view_library_name       char(10) for sbcs data,
        view_system_object_name char(10) for sbcs data,
        view_sqlcode            integer, 
        view_sqlstate           char(5) for sbcs data,
        view_error_text         varchar(1000) for sbcs data,
        create_view_stmt        clob(1M) for sbcs data
      )
    with replace;

  prepare physical_files_cursor_stmt from physical_files_stmt_text;
  open physical_files_cursor using p_table_schema;
  set at_end = 0;
  fetch from physical_files_cursor into v_table_name, v_system_table_name, v_owner; 

  while (at_end = 0) do   
    set lc = lc + 1;
    set v_view_name = v_table_name concat 'V';
    set create_view_text = 
         coolstuff.build_view_statement(
           P_TABLE_SCHEMA     => p_table_schema, 
           P_TABLE_NAME       => v_table_name, 
           P_VIEW_NAME        => v_view_name,
           P_VIEW_SYSTEM_NAME => default);

    set local_sqlcode = 0;
    set local_sqlstate = '';
     
    begin 
      declare continue handler for sqlexception 
        begin
          get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
        end;
      -- ================================================================================================
      -- Create the view
      -- ================================================================================================
      execute immediate create_view_text;
    end;
    
    if (local_sqlcode = 0) then 
      -- ================================================================================================
      -- Transfer the ownership of the new view to be owned by the owner of the base physical file
      -- ================================================================================================
      set transfer_ownership_text = 'transfer ownership of view ' concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(v_view_name) concat ' to user ' concat qsys2.delimit_name(v_owner);
      execute immediate transfer_ownership_text;
      select objname, objlib into view_object_name, view_library_name
        from table(qsys2.object_statistics(p_table_schema, '*FILE', v_view_name)); 
      
      -- ================================================================================================
      -- Inherit object auditing configuration of the base physical file, if applicable
      -- ================================================================================================
      select OBJECT_AUDIT into v_object_audit
        from table(qsys2.object_statistics(p_table_schema, '*FILE', v_table_name));
      if (v_object_audit is not null) and (v_object_audit <> '*NONE') then 
        set chgobjaud_cmd = 'QSYS/CHGOBJAUD OBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(view_object_name) concat ') OBJTYPE(*FILE) OBJAUD(' concat v_object_audit concat ')';
      end if;
    
      -- ================================================================================================
      -- Inherit the authorization configuration of the base physical file
      -- ================================================================================================
      set grtobjaud_cmd = 'QSYS/GRTOBJAUT OBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(view_object_name) concat 
                                            ') OBJTYPE(*FILE) REFOBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(v_system_table_name) concat ')';
    end if;
    
    -- ================================================================================================
    -- Log the result
    -- ================================================================================================
    insert into session.views_created values(p_table_schema, v_table_name, p_table_schema, v_view_name, view_library_name, view_object_name, 
                                             local_sqlcode, local_sqlstate, v_message_text, create_view_text);
     
    fetch from physical_files_cursor into v_table_name, v_system_table_name, v_owner; 
  end while;   
  close physical_files_cursor;
  open views_created_cursor;
end;       
stop;
-- ================================================================================================
--
-- To invoke, pass in the name of the schema:
-- 
-- ================================================================================================
call coolstuff.build_views_over_physicals(p_table_schema => 'TOYSTORE'); 
stop;

-- ================================================================================================
--
-- If you lose the result set window, query this to get it back: 
-- 
-- ================================================================================================
select * from session.views_created;