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

You may get a better performance avoiding the UPPER(TRIM(ParameterValue)) in
the RETURN SELECT statement.
Just add a SET Statement before the return statement and use the (modified)
SCHEMA_NAME in your SELECT statement:

BEGIN
Set Schema_Name = UPPER(TRIM(Schema_Name);
Return SELECT ...
WHERE Sys_DName = Schema_Name
...

If you are on Release 7.1 creating a VIEW (instead of an UDTF would be the
better option).
The schema_Name can be included as Global Variable (CREATE VARIABLE) and set
in your environment immediately before using the view.

Birgitta

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Robert Clay
Gesendet: Friday, 27.9 2013 15:48
An: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Betreff: Re: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT

Finally got it to work with the addition of scalar function TRIM().

Here is the working code ("working", but probably not final):

DROP FUNCTION QGPL.LIBSTATS ;

CREATE FUNCTION QGPL.LIBSTATS ( Schema_Name VARCHAR(10) ) RETURNS TABLE (
Lib_Schema CHAR(10)
, Table_Name CHAR(10)
, Mbr_Name CHAR(10)
, Owner CHAR(10)
, Text CHAR(50)
, Days_Used INTEGER
, Nbr_Rows BIGINT
, Del_Rows BIGINT
, Del_Percnt DECIMAL( 5 , 2 )
, Data_Size BIGINT
, Last_Used CHAR(10)
, Created CHAR(10)
, Last_Chgd CHAR(10)
, Last_Saved CHAR(10)
, Last_Rstrd CHAR(10)
)

LANGUAGE SQL
NOT DETERMINISTIC
NOT FENCED
NO EXTERNAL ACTION
DISALLOW PARALLEL
CARDINALITY 100

BEGIN

RETURN
(
SELECT
A.Lib_Name
, A.Table_Name
, A.Member_Name
, T.Owner
, T.Desc
, A.Days_Used
, A.Number_Rows
, A.Deleted_Rows
, A.PercntDltd
, A.Data_Size
, A.Last_Used
, CHAR( GetCreateD( Lib_Name, Table_Name ) )
AS Create_Date
, A.Last_Chgd
, A.Last_Saved
, A.Last_Rstrd
FROM ( SELECT
Sys_DName AS Lib_Name
, Sys_TName AS Table_Name
, Sys_MName AS Member_Name
, Card AS Number_Rows
, Deleted AS Deleted_Rows
, CAST(CASE WHEN ( Deleted = 0 ) THEN 0
WHEN ( Card = 0 ) AND ( Deleted > 0 ) THEN 100.00
ELSE ( 100.00 * Deleted / ( Card + Deleted ) )
END
AS DECIMAL( 5 , 2 )
) AS PercntDltd
, Size AS Data_Size
, COALESCE( CHAR( DATE( LastUsed ) ), ' ' ) AS Last_Used
, COALESCE( CHAR( DATE( LastChg ) ), ' ' ) AS Last_Chgd
, COALESCE( CHAR( DATE( LastSave ) ), ' ' ) AS Last_Saved
, COALESCE( CHAR( DATE( LastRst ) ), ' ' ) AS Last_Rstrd
, DaysUsed AS Days_Used
FROM qsys2.syspartitionstat
WHERE Sys_DName = UPPER( TRIM( Schema_Name ) )
ORDER BY Sys_DName, Sys_TName, Sys_MName
) A
JOIN ( SELECT
DBName AS LibName
, Name AS TableName
, SUBSTR( Creator , 1, 10 ) AS Owner
, Label AS Desc
FROM QSYS2.SYSTABLES
WHERE Table_Type IN ( 'P', 'T' )
AND File_Type = 'D'
ORDER BY DBName, Name
) T
ON A.Lib_Name = T.LibName
AND A.Table_Name = T.TableName
ORDER BY A.Last_Used ASC
);

END;





"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