I removed the UDF GetCreateD and removed the ORDER BYs (for performance...it was too slow) and it worked fine.
I suspect the SQL0100 is referring to your GetCreateD UDF not finding anything?
Steve Needles
Steve Needles
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Thursday, September 26, 2013 12:44 PM
To: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Subject: 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
--
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
