OBJECT_STATISTICS Enhanced for V7R2

objstats72.sql

     --  -------------------------------------------------------  --
     --  OBJECT_STATS Table Function for V7R3+ Features on V7R2   --
     --  (c) Copyright 2023 - R. Cozzi Jr. All rights reserved.   --
     --  Reproduction in whole or part is permitted provided this --
     --  copyright information is included.                       --
     --  -------------------------------------------------------  --
     --  This Table function returns information on IBM i V7R2    --
     --  that was not included until V7R3 including:              --
     --  New Columns:                                             --
     --   + OBJLIB - The "short" library name                     --
     --   + LAST_USED_DATE - The DATE-only version of             --
     --       the LAST_USED_TIMESTAMP column.                     --
     --   + CRTUSRPRF - The User Profile of the user who created  --
     --     the object. This is the same as the OBJDEFINER column --
     --     but easier to remember.                               --
     --  New Features:
     --   + All parameters ignore upper/lower case differences
     --   + OBJECT_NAME parameter supports a generic object name
     --  Limited Features:
     --   + The OBJTYPELIST on V7R3 and later is VARCHAR(812)
     --     while on V7R2 it is VARCHAR(70). The longer length
     --     supports more object types since OBJTYPELIST accepts
     --     multiple object types e.g., '*FILE *PGM *DTAARA'
     --     The longer parm length accomodates more types.
     --     To be compatible with V7R2, we limited OBJTYPELIST
     --     to VARCHAR(70) of that release version.
     --  -------------------------------------------------------  --
     --  While this will work on V7R3 and later as well as V7R2,  --
     --  it is intended to be used with V7R2 only and then        --
     --  replaced with the native OBJECT_STATISTICS Table         --
     --  function when the IBM i operating system is updated      --
     --  to V7R3, V7R4, V7R5 or later.                            --
     --  -------------------------------------------------------  --


CREATE OR REPLACE FUNCTION SQLTOOLS.OBJECT_STATISTICS(
                              OBJECT_SCHEMA VARCHAR(258) ,
                              OBJTYPELIST   VARCHAR(70) DEFAULT NULL,
                              OBJECT_NAME   VARCHAR(258) DEFAULT NULL
                                                )

  RETURNS TABLE (
     OBJNAME VARCHAR(10) FOR SBCS DATA ,
     OBJTYPE VARCHAR(8) FOR SBCS DATA ,
     OBJOWNER VARCHAR(10) FOR SBCS DATA ,
     OBJDEFINER VARCHAR(10) FOR SBCS DATA ,
     OBJCREATED TIMESTAMP ,
     OBJSIZE DECIMAL(15, 0) ,
     OBJTEXT VARCHAR(50) ,
     OBJLONGNAME VARCHAR(128) FOR SBCS DATA ,

     LAST_USED_TIMESTAMP TIMESTAMP ,
     -- LAST_USED_OBJECT VARCHAR(4) FOR SBCS DATA ,
     DAYS_USED_COUNT INTEGER ,
     LAST_RESET_TIMESTAMP TIMESTAMP ,

     IASP_NUMBER SMALLINT ,
     OBJATTRIBUTE VARCHAR(10) FOR SBCS DATA ,
     OBJLONGSCHEMA VARCHAR(128) FOR SBCS DATA ,
     TEXT VARGRAPHIC(50) CCSID 1200 ,
     SQL_OBJECT_TYPE VARCHAR(9) FOR SBCS DATA ,

       -- For V7R2 users Library Name Char(10)
     OBJLIB VARCHAR(10) FOR SBCS DATA,

       -- Note the next two columns are for convience.
       -- They are not included in the native OBJECT_STATISTICS
       -- function but are provided here for easier reference.
       
       --     "Created By" User Profile
     CRTUSRPRF VARCHAR(10) FOR SBCS DATA,
       --     "Last Used Date" (as a date-only value)
     LAST_USED_DATE  DATE
  )

  LANGUAGE SQL
  READS SQL DATA
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  ALLOW PARALLEL
  NOT FENCED
  CARDINALITY 5000
  SPECIFIC ST_OBJSTAT

R: BEGIN
     DECLARE OBJ72 VARCHAR(258);

     if (OBJECT_NAME is NOT NULL and OBJECT_NAME <> '') THEN
       if (POSITION('"', OBJECT_NAME) = 0) THEN
         set OBJECT_NAME = UPPER(OBJECT_NAME);
       end if;
     end if;

     if (OBJECT_NAME = '*SIMPLE') THEN
       set OBJECT_NAME = '*ALLSIMPLE';
     end if;

     if (OBJECT_SCHEMA is NOT NULL) THEN
       if (LEFT(OBJECT_NAME,1) = '*' or POSITION('"', OBJECT_SCHEMA) = 0) THEN
         set OBJECT_SCHEMA = UPPER(OBJECT_SCHEMA);
       end if;
     end if;
     if (OBJECT_SCHEMA = '*SIMPLE') THEN
       set OBJECT_SCHEMA = '*ALLSIMPLE';
     end if;

     if (OBJTYPELIST is not NULL and OBJTYPELIST <> '') THEN
       set OBJTYPELIST = UPPER(OBJTYPELIST);
     else
       set OBJTYPELIST = '*ALL';  -- Default to *ALL when NULL or blank
     end if;

     if (POSITION('*', OBJECT_NAME) > 1) THEN
       set R.OBJ72 = REPLACE(OBJECT_NAME,'*', '%');
       set OBJECT_NAME = NULL;
     end if;

  return
      SELECT
             OL.OBJNAME,
             OL.OBJTYPE,
             OL.OBJOWNER,
             OL.OBJDEFINER,
             OL.OBJCREATED,
             OL.OBJSIZE,
             OL.OBJTEXT,
             OL.OBJLONGNAME,
             OL.LAST_USED_TIMESTAMP,
             -- LAST_USED_OBJECT VARCHAR(4) FOR SBCS DATA ,
             OL.DAYS_USED_COUNT,
             OL.LAST_RESET_TIMESTAMP,
             OL.IASP_NUMBER,
             OL.OBJATTRIBUTE,
             OL.OBJLONGSCHEMA,
             OL.TEXT,
             OL.SQL_OBJECT_TYPE,
                        -- "Short" Object Library name
             LN.objname AS OBJLIB,
                        -- Alias for more meaningful name (same as OBJDEFINER)
             OL.OBJDEFINER AS CRTUSRPRF,
                        -- Date Last Used as a DATE instead of DTS
             CAST(OL.LAST_USED_TIMESTAMP as DATE) LAST_USED_DATE

        FROM TABLE (QSYS2.OBJECT_STATISTICS(OBJECT_SCHEMA,
                                      OBJTYPELIST,
                                      OBJECT_NAME)) OL,
           LATERAL (
               SELECT OBJNAME
                 FROM TABLE (
                     QSYS2.object_statistics(OL.OBJLONGSCHEMA, '*LIB')
                   ) LIBS
             ) LN
      -- Generic name support, otherwise simple 'A'='A' compare
        WHERE OL.OBJNAME LIKE COALESCE(R.OBJ72,OL.OBJNAME);
end;


LABEL on specific routine sqltools.ST_OBJSTAT IS
'Enhanced OBJECT_STATISTICS for IBM i V7R2 Users';

COMMENT ON SPECIFIC FUNCTION SQLTOOLS.ST_OBJSTAT IS
'This is a wrapper for the IBM OBJECT_STATISTICS Function.
This version is enhanced for IBM i V7R2 customers. It includes the
highly desireable "short" library name as a varchar(10) value.
In addition, two existing columns were refactored for this function.
<ol><li>CRTUSRPRF - User Profile that created the object.
Same as OBJDEFINER</li>
<li>LAST_USED_DATE - Last used Date. Extracted from LAST_USED_TIMESTAMP</li>
</ol>These two refactored columns are available with V7R2 but in the case
of the CRTUSRPRF column, it was named oddly, and in the case of the
LAST_USED_DATE, the original LAST_USED_TIMESTAMP included the time portion
which is not kept with the object (only the date is). To make this usable,
Users will often do something like:
<pre>cast(LAST_USED_TIMESTAMP as DATE) as LAST_USED_DATE</pre>
This function does this CAST for you.<br/> The function returns both
of the original columns in their original form along with these two
reformatted columns.<br />
In addition, a generic object name may be specified for the OBJECT_NAME
parameter. For more information, see the IBM i V7R3 (or later) documentation
for the OBJECT_STATISTICS Table function under the
<b>Librarian&nbsp;Services</b> section.';

COMMENT ON PARAMETER SPECIFIC FUNCTION SQLTOOLS.ST_OBJSTAT
(
OBJECT_SCHEMA IS
  'A long or short library name or one of the special values:
  <ul><li>*ALL</li><li>*ALLSIMPLE</li><li>*ALLUSR</li>
  <li>*ALLUSRAVL</li><li>*CURLIB</li><li>*LIBL</li><li>*USRLIBL</li></ul>',
OBJTYPELIST IS
  'A list of one or more system object types separated by blanks or commas.
  The special value *ALL may be specifed to indicate that all object types
  matching the OBJECT_SCHEMA and OBJECT_NAME parameters should be returned.
  The default is NULL which is the same as *ALL. For example if you want
  to include all programs, service programs, and CL commands, you would
  specify: objtypelist=>''*PGM *SRVPGM *CMD'' with or without the
  leading asterisk.',
OBJECT_NAME IS 'The name of the object to be returned. A long or short
  object name may be specified. The name may be generic name, full or *ALL.
  When a generic name is specified all objects that
  match the generic pattern are returned. (NOTE This function uses
  the CL-style "generic name" syntax: e.g., ''GEN*'') however this version
  also accepts the SQL "generic" support character which is
  the percent sign&nbsp;(%).<br/>
  There are two special values supported:<ul><li>*ALLSIMPLE</li>
  <li>*ALL</li></ul>*ALL means all object names are returned that
  match the library and object type parameters.<br />
  *ALLSIMPLE returns all object names that match the library and
  object&nbsp;type parameters but only the object, library and
  object&nbsp;type columns are returned. If this parameter is not
  specified, the default is NULL which is the same as *ALL.'
);

GRANT EXECUTE
ON SPECIFIC FUNCTION SQLTOOLS.ST_OBJSTAT
TO PUBLIC ;

GRANT ALTER , EXECUTE
ON SPECIFIC FUNCTION SQLTOOLS.ST_OBJSTAT
TO QSYS WITH GRANT OPTION ;