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

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)
...
Begin
Declare-Section;
Other Statements;
Return SELECT ....
From ...
End;

The Result-Set is returned through the SELECT statement specified within the
Return Statement.
(http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Fr
bafzaftb.htm
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Fr
bafyudftableex.htm)

For more information about creating and using UDTFs please read the
following article.
http://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/

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
code)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"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!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[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,
DOH!).

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
FROM QSYS2.SYSTABLES
WHERE Table_Type IN ( 'P', 'T' )
AND File_Type = 'D'
ORDER BY Table_Schema, Table_Name
) T
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.

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