You CREATED a Stored Procedure and NOT an UDTF!
An UDTF must be created with the CREATE FUNCTION Statement (and NOT with the
CREATE PROCEDURE statement)
Create Function YourSchema/yourUDTF (Par1 ..., Par2 ..., ... ParN)
Returns Table (Col1 DataType + Length,
Col2 DataType + Length,
ColX Data Type + Length)
Return SELECT ....
The Result-Set is returned through the SELECT statement specified within the
For more information about creating and using UDTFs please read the
You may also get some problem when using long SQL names: The up to 10
character system names are stored in the columns SYSTEM_TABLE_SCHEMA and
SYSTEM_TABLE_NAME both columns should be used (at least when joining with
the Outfile created by the DSPOBJD command).
The creation date is only returned from the DSPOBJD command (alternatively
you could use the API QUSROBJD format OBJD0100 which is more complicated to
Mit freundlichen Grüßen / Best regards
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"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!"
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Robert Clay
Gesendet: Tuesday, 24.9 2013 21:46
An: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Betreff: Re: UDTF (or similar) to retrieve data from SYSPARTITIONSTAT
I found a possible replacement for QADBXREF: QSYS2.SYSTABLES (I know,
Does anyone see a problem with that?
In the interim, I've re-written the join (formerly on QADBXREF) as:
JOIN ( SELECT
Table_Schema AS LibName
, Table_Name AS FileName
, Table_Owner AS Owner
, Table_Text AS Desc
WHERE Table_Type IN ( 'P', 'T' )
AND File_Type = 'D'
ORDER BY Table_Schema, Table_Name
ON A.Table_Schema = T.LibName
AND A.Table_Name = T.FileName
Now, I need the Creation Date from somewhere other than the DSPOBJD outfile.
"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