RE: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Tuesday, September 24, 2013 12:24 PM
To: midrange-l@xxxxxxxxxxxx
Subject: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT


[Before I forget, this is a V6.1.0 LPAR. I hope Thunderbird doesn't
screw up the formatting on this post.]

I couldn't get exactly what I needed from the last thread (see
http://archive.midrange.com/midrange-l/201309/msg00615.html) and, I'll
admit, that thread sort of morphed into something different anyway.

Therefore, new thread.

I created a procedure to return the results thinking that I was creating
a UDTF but I don't think what I've created exactly qualifies as a UDTF.
The only way that I can determine that it can be called is via iNav, e.g.,

CALL QGPL.LIBSTATS('MyLib')

That works okay for me (although, it is sloooooow for big libraries) but
there are others who refuse to use iNav even if they have it installed
on their PC. So, I need a way to call it from STRSQL, if possible.

Compounding the issue is that I need to exclude source files and the
only way I know is to check in QADBXREF. I can't find a system
catalog/view that has a flag based on DBXTYP. Does anyone know an
alternative?

Further, I need the object creation date. I was also unable to find
that in a system catalog/view. It must be there because the object
creation date shows up when you right-click on an object in iNav and
select "Description". Instead, I had to resort to outfiling the object
descriptions for the entire schema and joining to it. To make matters
worse, the creation date is in MDY format in the outfile so I created a
function to convert it to *ISO format.

Feel free to pick these apart and make any suggestions for improvement
as I'm sure that there are many opportunities.


CREATE FUNCTION QGPL.MDY2ISO (
PARM1 VARCHAR(6) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC QGPL.MDY2ISO
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
BEGIN

RETURN ( VALUES (CASE
WHEN SUBSTR(PARM1, 1, 2) NOT BETWEEN '01' AND
'12' THEN NULL
WHEN SUBSTR(PARM1, 3, 2) NOT BETWEEN '01' AND
'31' THEN NULL
WHEN SUBSTR(PARM1, 1, 2) NOT BETWEEN '01' AND
'99' THEN NULL
ELSE DATE(
DIGITS(
CAST(
CASE WHEN SUBSTR(PARM1,
5, 2) >= '40' THEN '19' ELSE '20' END
CONCAT SUBSTR(PARM1, 5, 2)
CONCAT SUBSTR(PARM1, 1, 4)
AS DEC(8, 0)
)
) CONCAT '000000'
)
END
)
);
END ;





CREATE PROCEDURE QGPL.LIBSTATS (
IN SCHEMA_NAME VARCHAR(10) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC QGPL.LIBSTATS
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
PROGRAM TYPE SUB
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SQLCURRULE = *STD ,
SRTSEQ = *HEX
BEGIN

DECLARE CMD CHAR(200);
DECLARE CMDLEN DECIMAL(15,5);

DECLARE C1 CURSOR FOR
SELECT
A.Table_Schema
, A.Table_Name
, A.Member_Name
, T.Owner
, T.Desc
, A.Days_Used_Count
, A.Number_Rows
, A.Number_Deleted_Rows
, A.Percent_Deleted
, A.Data_Size
, A.Last_Used
, Q.CreationDate
, A.Last_Chgd
, A.Last_Saved
, A.Last_Restored
FROM ( SELECT
Table_Schema AS Table_Schema
, Table_Name AS Table_Name
, System_Table_Member AS Member_Name
, Number_Rows AS Number_Rows
, Number_Deleted_Rows AS Number_Deleted_Rows
, CAST(CASE WHEN ( Number_Deleted_Rows = 0 )
THEN 0
WHEN ( Number_Rows = 0 )
AND ( Number_Deleted_Rows > 0 )
THEN 100.00
ELSE ( 100.00 * Number_Deleted_Rows
/ ( Number_Rows + Number_Deleted_Rows ) )
END
AS DECIMAL( 5 , 2 )
) AS Percent_Deleted
, Data_Size AS Data_Size
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' ' ) AS
Last_Used
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' ' ) AS
Last_Chgd
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' ' ) AS
Last_Saved
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' ' ) AS
Last_Restored
, Days_Used_Count AS Days_Used_Count
FROM qsys2.syspartitionstat
WHERE Table_Schema = UPPER( Schema_Name )
ORDER BY
Table_Schema
, Table_Name
, System_Table_Member
) A
JOIN ( SELECT
DBXLIB AS LibName
, DBXFIL AS FileName
, DBXOWN AS Owner
, DBXTXT AS Desc
FROM QSYS.QADBXREF
WHERE ( ( substr( DBXLIB,1,1 ) not in ( '#' , 'Q' ) )
OR DBXLIB = 'QGPL' )
AND DBXATR = 'PF'
AND DBXTYP <> 'S'
ORDER BY DBXLIB, DBXFIL
) T
ON A.Table_Schema = T.LibName
AND A.Table_Name = T.FileName
JOIN ( SELECT
odlbnm AS LibraryName
, odobnm AS ObjectName
, mdy2iso(odcdat) AS CreationDate
FROM qtemp.libout
WHERE odobat = 'PF'
) Q
ON T.LibName = Q.LibraryName
AND T.FileName = Q.ObjectName
ORDER BY A.Last_Used ASC
FOR READ ONLY WITH NC ;

SET PATH "QGPL";

SET CMD= 'DSPOBJD OBJ('
CONCAT SCHEMA_NAME
CONCAT '/*ALL) OBJTYPE(*FILE)'
CONCAT ' DETAIL(*FULL)'
CONCAT ' OUTPUT(*OUTFILE)'
CONCAT ' OUTFILE(QTEMP/LIBOUT)'
CONCAT ' OUTMBR(*FIRST *REPLACE)' ;

SET CMDLEN = LENGTH(CMD) ;
CALL QSYS.QCMDEXC(CMD, CMDLEN) ;

OPEN C1 ;

SET RESULT SETS CURSOR C1 ;

END ;


Thanks in advance for any assistance,
Robert
--
"Contrariwise, if it was so, it might be; and if it were so, it would
be; but as it isn't, it ain't. That's logic."--Tweedledee
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.