Last active
February 15, 2024 14:10
-
-
Save bobcozzi/18ee5a22f6847628331a2a13110866d8 to your computer and use it in GitHub Desktop.
OBJECT_STATISTICS Enhanced for V7R2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ------------------------------------------------------- -- | |
-- 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 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 (%).<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 type parameters but only the object, library and | |
object 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