Monitoring the growth of db2 tablespaces

Claude Birtz

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]
--------------------------------------------------------------------



Mike Holmans

Re: Monitoring the growth of db2 tablespaces
(in response to Claude Birtz)
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

Re: Monitoring the growth of db2 tablespaces
(in response to Mike Holmans)
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