Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit

prtprvaut.sql

--
-- If you're not ready to use IFS_OBJECT_PRIVILEGES, this might help you...
-- Note: A special thanks to Sue Romano for her help with this Gist
--

-- https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-privileges-table-function
cl:PRTPVTAUT OBJTYPE(*STMF) DIR('/') SCHSUBDIR(*YES);

--
-- create an alias over the most recent execution of PRTPRVAUT
--
begin
  declare sqlstmt clob(1g);
  declare v_partition_name varchar(10);
  declare prtprvaut cursor for
    select table_partition
      from qsys2.syspartitionstat
      where table_schema = 'QUSRSYS' and
            table_name = 'QPVSTMF'
      order by create_timestamp desc
      limit 1;
  open prtprvaut;
  fetch from prtprvaut
    into v_partition_name;
  set sqlstmt = 'create or replace alias coolstuff.prtprvaut FOR QUSRSYS.QPVSTMF('
        concat v_partition_name concat ')';
  execute immediate sqlstmt;
  close prtprvaut;
end;
    
--
-- Raw
-- 
select * from coolstuff.prtprvaut;


--
-- Refined
--
select timestamp(timestamp_format(ADRDAT concat adrtim, 'YYMMDDHH24MISS'), 0) as time,
       interpret(
         varbinary_format(hex(smallint(length(ADRPON) / 2)) concat hex(ADRPON)) as vargraphic(2048)
         ccsid 1200) as path_name,
       interpret(
         varbinary_format(hex(smallint(length(ADROBN) / 2)) concat hex(ADROBN)) as vargraphic(512)
         ccsid 1200) as object_name, 
       ADRATL as authorization_list, 
       ADROWN as owner, 
       ADRPGR as primary_group, 
       ADRPRF as user_name,
       case hex(adrmgt)
         when hex(x'01') then 'YES'
         else 'NO'
       end as object_management,
       case hex(adrexs)
         when hex(x'01') then 'YES'
         else 'NO'
       end as object_existence,
       case hex(adralt)
         when hex(x'01') then 'YES'
         else 'NO'
       end as object_alter,
       case hex(adrref)
         when hex(x'01') then 'YES'
         else 'NO'
       end as object_reference,
       case hex(adropr)
         when hex(x'01') then 'YES'
         else 'NO'
       end as object_operational,
       case hex(adrrea)
         when hex(x'01') then 'YES'
         else 'NO'
       end as data_read,
       case hex(adradd)
         when hex(x'01') then 'YES'
         else 'NO'
       end as data_add,
       case hex(adrupd)
         when hex(x'01') then 'YES'
         else 'NO'
       end as data_update,
       case hex(adrdlt)
         when hex(x'01') then 'YES'
         else 'NO'
       end as data_delete,
       case hex(adrexe)
         when hex(x'01') then 'YES'
         else 'NO'
       end as data_execute,
       case hex(adrexc)
         when hex(x'01') then 'YES'
         else 'NO'
       end as exclude, 
       ADROBT as object_type, 
       ADRONL as object_name_length,
       ADRCCD as object_name_ccsid, 
       ADRCNT as object_name_country, 
       ADRLNG as object_name_languaage,
       ADRPAU as parent_public_authority, 
       ADRPNL as parent_name_length, 
       ADRPCS as parent_name_ccsid, 
       ADRPCC as parent_name_country, 
       ADRPLG as parent_name_language, 
       hex(ADRFID) as file_identifier, 
       hex(ADRPFI) as parent_file_identifier  
  from coolstuff.prtprvaut;