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

So, I've made the following progress:

- Created an external procedure to determine and return the creation date of the object using the QUSROBJD API. This works well outside of SQL.

- Subsequently, created a SQL function for the external procedure. This works well for SQL.

I have the logic for the grid that I want to return but I'm having issues with the UDTF itself. It creates successfully, but returns no rows. I'm missing something, I'm sure, but since this is the first time doing this, I need an education.

Here is the current code:

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
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;


The syntax checks out okay. Calling it results in a blank grid:

SELECT MYLIB.* FROM TABLE(QGPL.LIBSTATS('SomeLib')) AS MYLIB

The joblog shows SQL0100 - Row not found... so there is something wrong in the syntax of the function itself.

I can run the SELECT statement for a given schema (replacing SCHEMA_NAME in the WHERE clause with a literal) and it is successful.

Any assistance is greatly appreciated.

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