IBM i - Understanding CTEs and how to combine IBM i Services using LATERAL

CTE Example.sql

--
-- Description: The "Common Table Expression" (CTE) is a very powerful and useful tool which allows a temporary "table" to be
--                created in memory while a query is running. The contents of that temporary table (or "result set") can be
--                referenced elsewhere in the query.
--
--              The table functions provided by IBM as "IBM i Services" are ALSO very powerful. These functions allow us to
--                use SQL to retrieve many different details from the system. This includes details about user profiles,
--                network connections, objects, streamfiles, security, and much more.
--
--              We can leverage CTEs to combine multiple IBM i Services table functions (and views) and make them even more 
--                powerful. In the examples below we will explore such functionality by combining two services. The first is
--                the OBJECT_OWNERSHIP() table function which returns a list of objects that are owned by a specific user 
--                profile (or group profile). The second is the OBJECT_STATISTICS() table function, which returns a great many
--                details about any individual object, including the size and last used date.
--
--              By combining these table functions, we can write a single query to retrieve a list of objects that are owned by
--                a specific user profile, along with details about each of those objects such as the size and last used date.

--------------------------------------------------------------------------------------------------------------------------------

--Example 1a - List the objects owned by the 'PGMR' user profile using the OBJECT_OWNERSHIP() table function. Note that this
--             table function can return a lot of information about the objects, but not their SIZE or LAST USED DATE.
SELECT OBJECT_LIBRARY, 
       OBJECT_NAME, 
       OBJECT_TYPE
    FROM TABLE ( QSYS2.OBJECT_OWNERSHIP('PGMR') )
    WHERE PATH_NAME IS NULL /* this ensures we don't include IFS objects */;

--Example 1b - Create a CTE called OBJ_LIST consisting of the same list of objects as the prior example, and then run a simple 
--             SELECT against the CTE. This is redundant, but it illustrates how a CTE works in a simple way. Note that the CTE 
--             is called "OBJ_LIST" and is defined as having 3 fields/columns "OBJLIB, OBJNAME, and OBJTYPE".
WITH OBJ_LIST (OBJLIB, OBJNAME, OBJTYPE) AS (
        SELECT OBJECT_LIBRARY, 
               OBJECT_NAME, 
               OBJECT_TYPE
            FROM TABLE ( QSYS2.OBJECT_OWNERSHIP('PGMR') )
            WHERE PATH_NAME IS NULL
    )
    SELECT * 
        FROM OBJ_LIST;


--------------------------------------------------------------------------------------------------------------------------------


--Example 2 - Retrieve details about a specific named object (object size and last used date) using the OBJECT_STATISTICS() table
--            function. Note that you must specify the object library, name, and type when using this table function, so it can
--            only return details about a single object. Note that we're calling the results "OBJ_DETAILS" on the last line.
SELECT OBJSIZE, 
       LAST_USED_TIMESTAMP
    FROM TABLE (
            QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'JOELIB',  --specific library name
                                     OBJECT_NAME   => 'ROYSHRA', --specific object name
                                     OBJTYPELIST   => '*FILE' )  --specific object type
        ) OBJ_DETAILS;


--------------------------------------------------------------------------------------------------------------------------------


--Example 3 - Use LATERAL to combine the earlier examples, using OBJECT_OWNERSHIP() to retrieve the list of objects owned by the 
--            'PGMR' user profile, and using OBJECT_STATISTICS() to add details about each object (size and last used date).
WITH OBJ_LIST (OBJLIB, OBJNAME, OBJTYPE) AS (
        SELECT OBJECT_LIBRARY, 
               OBJECT_NAME, 
               OBJECT_TYPE
            FROM TABLE ( QSYS2.OBJECT_OWNERSHIP('PGMR') )
            WHERE PATH_NAME IS NULL
    )
    SELECT OBJ_LIST.*, OBJ_DETAILS.* --select all of the fields from both OBJ_LIST and OBJ_DETAILS "tables"
        FROM OBJ_LIST, LATERAL (
                 SELECT OBJSIZE, 
                        LAST_USED_TIMESTAMP
                     FROM TABLE (
                             QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => OBJ_LIST.OBJLIB,   --library name from the outer query
                                                      OBJECT_NAME   => OBJ_LIST.OBJNAME,  --object name from the outer query
                                                      OBJTYPELIST   => OBJ_LIST.OBJTYPE ) --object type from the outer query
                         ) ) OBJ_DETAILS;