This all worked for me (tweak where it makes sense):
drop function needles.libstats;
CREATE FUNCTION needles.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
DETERMINISTIC
NOT FENCED
NO EXTERNAL ACTION
DISALLOW PARALLEL
CARDINALITY 10
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( 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;
SELECT MYLIB.* FROM TABLE(needles.LIBSTATS('qgpl')) AS MYLIB;
Steve Needles
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Thursday, September 26, 2013 2:53 PM
To: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Subject: Re: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT
Not stupid and yes, the library does exist. If I isolate the SELECT statement and substitute a literal library name for SCHEMA_NAME in the WHERE clause, it returns rows successfully. In fact, I've tried it with many library names with the same results.
It MUST be in the function statement itself but I have no idea where.
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
On 09/26/2013 3:34 PM, Needles,Stephen J wrote:
This might sound stupid...but have you checked to make sure the library you are testing with exists in the tables?
Steve Needles
--
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.
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
midrange.com
