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

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

Well at least you can read and (hopefully) understand the individual step involved.

I love SQL for set retrieval etc. but so often it seem to descend rapidly into the land of write-only code.


On 2013-09-27, at 11:24 AM, rob@xxxxxxxxx wrote:

> Unlike RPG, which is self documenting.
>
> ROTFL
>
>
> Rob Berendt
> -- 
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Jon Paris <jon.paris@xxxxxxxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>, 
> Date:   09/27/2013 11:15 AM
> Subject:        Re: UDTF (or similar) to retrieve data from 
> SYSPARTITIONSTAT
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> 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
>
>
>
>
> -- 
> 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 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