× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.