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?
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 (
, 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)
NO EXTERNAL ACTION
, CHAR( GetCreateD( Lib_Name, Table_Name ) )
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 ) )
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
WHERE Sys_DName = UPPER( SCHEMA_NAME )
ORDER BY Sys_DName, Sys_TName, Sys_MName
JOIN ( SELECT
DBName AS LibName
, Name AS TableName
, SUBSTR( Creator , 1, 10 ) AS Owner
, Label AS Desc
WHERE Table_Type IN ( 'P', 'T' )
AND File_Type = 'D'
ORDER BY DBName, Name
ON A.Lib_Name = T.LibName
AND A.Table_Name = T.TableName
ORDER BY A.Last_Used ASC
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.
"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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
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.