Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 09
    2000

    Monitoring the growth of db2 tablespaces

    Claude Birtz
    [Centre des technologies de l'information de l`Etat]
    We execute Stospace each night and Runstats (with SAMPLE option) each sunday. I've created the following table
    CREATE TABLE INFO
    ("NAME" CHAR(18 ) NOT NULL
    ,TSNAME CHAR(8 ) NOT NULL
    ,CARD FLOAT NOT NULL
    ,SPACE INTEGER NOT NULL
    ,STATSTIME TIMESTAMP NOT NULL
    )

    which gets filled after each execution of RUNSTATS by this select:

    INSERT INTO INFO
    SELECT T.NAME , TS.NAME , T.CARDF , TS.SPACE , T.STATSTIME
    FROM SYSIBM.SYSTABLES T ,
    SYSIBM.SYSTABLESPACE TS
    WHERE
    T.DBNAME = 'XXXXX'
    AND T.TSNAME = TS.NAME
    AND T.TYPE = 'T' ;
    COMMIT;
    DELETE FROM INFO
    WHERE STATSTIME < CURRENT TIMESTAMP - 180 DAYS ;

    It's only used for one particular database, but I guess you could expand it to a whole system.

    Of course this table and SQL would have to be modified in case of partitionned TS.

    HTH,
    Claude.

    "Briggs, N. - Neil -" wrote:

    > Dear All,
    >
    > I have been asked to provide information as to how the tables are growing in
    > our DB2 system. Please can anyone send me what practices are followed are
    > their sites to give me some ideas.
    >
    > We have DB2 version 5 running on OS/390
    >
    > Kindest regards
    > Neil
    >
    >
    >

    --
    --------------------------------------------------------------------
    Claude Birtz CIE
    Tel: 49 925 622 Fax: 49 925 750
    mailto:[login to unmask email]
    --------------------------------------------------------------------



    Mike Holmans
    [BT]
    Now there's interesting. I haven't run STOSPACE in years because it seemed
    pretty useless. As far as I recall, it was useless because we were using
    partitioned tablespaces, our stogroups are mostly defined as (primary_vol,
    overflow_vol), and each partition would be in a different stogroup. The
    figures which STOSPACE came up with in that scenario were nonsensical, at
    least as I remember it.

    Is there any point in STOSPACE, and if there is, does it matter whether your
    DB2 datasets are SMS-managed?

    Mike Holmans
    BT ISE Technical Design
    [login to unmask email]

    > -----Original Message-----
    > From: Claude Birtz [SMTP:[login to unmask email]
    > Sent: Thursday, March 09, 2000 9:58 AM
    > To: [login to unmask email]
    > Subject: Re: Monitoring the growth of db2 tablespaces
    >
    > We execute Stospace each night and Runstats (with SAMPLE option) each
    > sunday. I've created the following table
    > CREATE TABLE INFO
    > ("NAME" CHAR(18 ) NOT NULL
    > ,TSNAME CHAR(8 ) NOT NULL
    > ,CARD FLOAT NOT NULL
    > ,SPACE INTEGER NOT NULL
    > ,STATSTIME TIMESTAMP NOT NULL
    > )
    >
    > which gets filled after each execution of RUNSTATS by this select:
    >
    > INSERT INTO INFO
    > SELECT T.NAME , TS.NAME , T.CARDF , TS.SPACE , T.STATSTIME
    > FROM SYSIBM.SYSTABLES T ,
    > SYSIBM.SYSTABLESPACE TS
    > WHERE
    > T.DBNAME = 'XXXXX'
    > AND T.TSNAME = TS.NAME
    > AND T.TYPE = 'T' ;
    > COMMIT;
    > DELETE FROM INFO
    > WHERE STATSTIME < CURRENT TIMESTAMP - 180 DAYS ;
    >
    > It's only used for one particular database, but I guess you could expand
    > it to a whole system.
    >
    > Of course this table and SQL would have to be modified in case of
    > partitionned TS.
    >
    > HTH,
    > Claude.
    >
    > "Briggs, N. - Neil -" wrote:
    >
    > > Dear All,
    > >
    > > I have been asked to provide information as to how the tables are
    > growing in
    > > our DB2 system. Please can anyone send me what practices are followed
    > are
    > > their sites to give me some ideas.
    > >
    > > We have DB2 version 5 running on OS/390
    > >
    > > Kindest regards
    > > Neil
    > >
    > >
    > >
    >
    >
    >
    > --
    > --------------------------------------------------------------------
    > Claude Birtz CIE
    > Tel: 49 925 622 Fax: 49 925 750
    > mailto:[login to unmask email]
    > --------------------------------------------------------------------
    >
    >
    >
    >
    >



    James Kwan
    Neil

    If you don't have a third party software, I would suggest you to write a
    rexx/clist to listc all db2 datasets and extract high used rba for each
    dataset. This will give you a rough figure of your data growth. If you
    need detailed information for each tablespace, I would suggest you to look
    for one of the third party solutions.

    James Kwan

    -----Original Message-----
    From: Briggs, N. - Neil - [mailto:[login to unmask email]
    Sent: Thursday, March 09, 2000 2:38 AM
    To: [login to unmask email]
    Subject: Monitiring the growth of db2 tablespaces


    Dear All,

    I have been asked to provide information as to how the tables are growing in
    our DB2 system. Please can anyone send me what practices are followed are
    their sites to give me some ideas.

    We have DB2 version 5 running on OS/390

    Kindest regards
    Neil









    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact