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

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

That's what I love about SQL - the code is so obvious, intuitive, and easy to modify <running and ducking>


On 2013-09-27, at 9:47 AM, Robert Clay <zreclay@xxxxxxxxx> wrote:

> Finally got it to work with the addition of scalar function TRIM().
>
> Here is the working code ("working", but probably not final):
>
> DROP FUNCTION QGPL.LIBSTATS ;
>
> 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
> NOT DETERMINISTIC
> NOT FENCED
> NO EXTERNAL ACTION
> DISALLOW PARALLEL
> CARDINALITY 100
>
> 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( TRIM( 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;
>
>
>
>
>
> "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.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com