Skip to content

Instantly share code, notes, and snippets.

@bobcozzi
Last active February 15, 2024 14:10
Show Gist options
  • Save bobcozzi/18ee5a22f6847628331a2a13110866d8 to your computer and use it in GitHub Desktop.
Save bobcozzi/18ee5a22f6847628331a2a13110866d8 to your computer and use it in GitHub Desktop.
OBJECT_STATISTICS Enhanced for V7R2
-- ------------------------------------------------------- --
-- 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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment