This request has come in many times: Provide a UDTF alternative to a an SQL View for some of the IBM i (SQL) Services. My response is that the UDTFs already exist, are sometimes not documented, and all times are OK for users to query directly.

Undocumented IBM i Services UDTFs.sql

--
-- Subject: Undocumented IBM i Services UDTFs
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses undocumented UDTFs for IBM i (SQL) Services
--
-- Notes:
-- ===============================================
-- 1) IBM i (SQL) Services frequently have SQL Views, which are documented here:
--    https://www.ibm.com/docs/en/i/7.5?topic=optimization-i-services
-- 2) In many cases, there are one or more UDTFs referenced in the SQL View definition.
--    IBM does not document all of these "behind the scenes" UDTFs, because the View is typically better than the UDTF.
--    The views return more columns and sometimes the view transforms the data returned into a more complete form.
-- 3) In most cases, the UDTF will perform better than the view
-- 4) Even though the UDTFs are undocumented, IBM i users can choose to code directly to the UDTFs.
-- 5) Like the views, the UDTFs reside within the QSYS2 schema.
-- 6) To discover the UDTFs used by a view:
--      select view_definition from qsys2.sysviews where table_schema = 'QSYS2' and table_name = <name of view>;
-- 7) To discover the UDTFs used by a view: (bonus version)
-- select distinct ps.schema, ps.name
--   from qsys2.sysviews, lateral (
--          select *
--            from table (
--                qsys2.parse_statement(SQL_STATEMENT => view_definition)
--              )
--        ) ps
--   where table_schema = 'QSYS2' and
--         table_name = 'OBJECT_LOCK_INFO' and
--         name_type = 'FUNCTION' and 
--         schema is not null;
--
-- The remainder of this script shows how to code to the UDTF alternative to the OBJECT_LOCK_INFO view.
--
stop;

-- The OBJECT_LOCK_INFO UDTF returns fewer columns than the OBJECT_LOCK_INFO view.
-- If you prefer to code directly to the UDTF, you need to pass in 4 parameters.
--
-- 1)  LIBRARY_NAME => ?,  /* VARCHAR(10)    */
-- 2)  OBJECT_NAME  => ?,  /* VARCHAR(10)    */
-- 3)  OBJECT_TYPE  => ?,  /* VARCHAR(10)    */
-- 4)  IASP_NUMBER  => ?   /* INTEGER where 0 is used for *SYSBAS */
--
-- OBJECT_LOCK_INFO view documentation:
-- https://www.ibm.com/docs/en/i/7.5?topic=services-object-lock-info-view
stop;

--
-- The following 2 queries return the same information... take your pick on which style to use
--
select job_name, lock_state, MEMBER_LOCK_TYPE, LOCK_SCOPE, SYSTEM_TABLE_MEMBER as member_name
  from qsys2.object_lock_info
  where object_type = '*FILE  ' and
        system_object_schema = 'QIWS' and
        system_object_name = 'QCUSTCDT'
  order by lock_state, lock_type;
stop;

select JOB_NAME, LOCK_STATE, MEMBER_LOCK_TYPE, LOCK_SCOPE, MEMBER_NAME
  from table (
      qsys2.object_lock_info(
        library_name => 'QIWS', object_name => 'QCUSTCDT', object_type => '*FILE', iasp_number => 0
      )
    )
  order by lock_state, MEMBER_LOCK_TYPE;
stop;   
   
--
-- The iasp_number for a *FILE object is dictacted by the IASP number of the library
--
select IASP_NUMBER from table(qsys2.object_statistics('TOYSTORE3', '*LIB'));
stop;

--
-- Which users and jobs are holding locks over this file?
--
select *
  from table (
      qsys2.object_lock_info(
        library_name => 'TOYSTORE3', 
        object_name  => 'SALES', 
        object_type  => '*FILE',
        iasp_number  => 144)
    ); 
stop;

--
-- description: What locks are held by any of Tim's activity 
-- Note: *USRPRF objects are always found in the QSYS library, which is in *SYSBAS, 
--       which is why the iasp_number is set to zero
--
select *
  from table (
      qsys2.object_lock_info(
        library_name => 'QSYS', 
        object_name  => 'TIMMR', 
        object_type  => '*USRPRF',
        iasp_number  => 0)
    ); 
stop;

--
-- description: Who are the *ALLOBJ users? (avoid IBM shipped profiles) 
--
declare global temporary table session.super_users(user_name, default_password, text) as (
      select authorization_name, user_default_password, trim(text_description)
      from qsys2.user_info
      where user_creator <> '*IBM' and
            special_authorities like '%*ALLOBJ%' or
            authorization_name in (select user_profile_name
                from qsys2.group_profile_entries
                where group_profile_name in (select authorization_name
                      from qsys2.user_info
                      where special_authorities like '%*ALLOBJ%'))
      order by authorization_name)
  with data with replace;
stop;

--
-- description: What are the *ALLOBJ users doing right now? 
--
select user_name, job_name, default_password, text,
       regexp_replace(j.v_sql_statement_text, '  ', '') as sql_stmt,
       v_client_ip_address as client_ip, v_client_applname as client_app, v_cpu_used
  from session.super_users s, lateral (
         select *
           from table (
               qsys2.object_lock_info(
                 library_name => 'QSYS', object_name => user_name, object_type => '*USRPRF',
                 iasp_number => 0)
             )
       ) o, lateral (
         select *
           from table (
               qsys2.get_job_info(job_name)
             )
       ) j;
stop;

--
-- If you read this far into the script, you deserve a bonus!
--
-- Here is a partial list of the undocumented UDTFs:
-- QSYS2.ASP_INFO
-- QSYS2.ASP_VARY_INFO
-- QSYS2.AUTHORITY_COLLECTION
-- QSYS2.COLUMN_STATISTICS
-- QSYS2.CONDENSE_ADVICE
-- QSYS2.DATABASE_MONITOR_INFORMATION
-- QSYS2.ENVIRONMENTAL_LIMITS
-- QSYS2.GETATLINFO  
-- QSYS2.GETMLBST  
-- QSYS2.GROUP_USERS
-- QSYS2.INDEX_PARTITION_STATISTICS
-- QSYS2.JOB_QUEUE_INFO
-- QSYS2.MESSAGE_QUEUE_INFO 
-- QSYS2.MQT_PARTITION_STATISTICS
-- QSYS2.NS_GET4CJOB 
-- QSYS2.GET4CSTS
-- QSYS2.NS_GET4ISTS 
-- QSYS2.NS_GET4RINF
-- QSYS2.NS_GET6CJOB
-- QSYS2.NS_GET6CSTS
-- QSYS2.NS_GET6ISTS
-- QSYS2.NS_GET6RINF
-- QSYS2.OBJECT_LOCK_INFO
-- QSYS2.OBJECT_PRIVILEGES
-- QSYS2.OUTPUT_QUEUE_INFO
-- QSYS2.PACKAGE_STMT_STATISTICS 
-- QSYS2.PARTITION_DISKS
-- QSYS2.PARTITION_INDEX_DISKS
-- QSYS2.PARTITION_STATISTICS
-- QSYS2.PRIVILEGES
-- QSYS2.PROGRAM_STATISTICS
-- QSYS2.PTF_INFO
-- QSYS2.QDBTS_TS_INDEX_INFO
-- QSYS2.QDBTS_TS_INDEX_NAME
-- QSYS2.QJORTVJI
-- QSYS2.QMPROFILES
-- QSYS2.QPM_QAPMCONF
-- QSYS2.QSQENVVAR
-- QSYS2.QSQJOBSCDE
-- QSYS2.QSQLCINF 
-- QSYS2.QSQLIBL
-- QSYS2.QSQRPYLE 
-- QSYS2.QSQSRVAUTE
-- QSYS2.QSQSYSCOL2
-- QSYS2.QSQSYSVAL 
-- QSYS2.QSYFINFO 
-- QSYS2.QSYFUSAGE 
-- QSYS2.QSYUSRINFO
-- QSYS2.RECORD_LOCK_INFO
-- QSYS2.SCHEMAS 
-- QSYS2.SERVER_SBS_ROUTING
-- QSYS2.SYSTMPSTG 
-- QSYS2.TCPIP_INFO
-- QSYS2.USERS

Thanks Scott! this is very useful!