Database Growth Report

Tools

Database Growth Report

How can I generate a report which shows how different Teradata Databases are growing?
Can someone please provide me some guidance on this?

Thanks
2 REPLIES

Re: Database Growth Report

Thanks Leo,

We already have a table which has the following columns:
TheDate,
CurrentPerm,
DatabaseName,
TableName.

Do you have any sql which can help me generate a report from this table?

Thanks
Aman

Re: Database Growth Report

OK..let me put this in the following way..

CREATE SET TABLE dbgrowth ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
databasename VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
currentperm INTEGER,
currentspool INTEGER,
currenttemp INTEGER,
prevperm INTEGER,
prevspool INTEGER,
prevtemp INTEGER,
lastrecorded DATE FORMAT 'YY/MM/DD',
currentdate DATE FORMAT 'YY/MM/DD');
The first time I populate the table, I would insert rows directly from dbc.databases view.

insert into dbgrowth select databasename,permspace,spoolspace,tempspace,0,0,0,date,date from dbc.databases;

Suppose you need to generate a report after one week from the date the table is first populated.run the following update query on the table.

Update dbgrowth from dbc.databases B
set
prevperm=currentperm,
prevspool=currentspool,
prevtemp=currenttemp,
currentperm=B.permspace,
currentspool=B.spoolspace,
currenttemp=B.tempspace,
lastrecorded=currentdate,
currentdate=date
where
dbgrowth.databasename=B.databasename;

To generate the growth report, run the below query after the update,

Select currentperm-prevperm from dbgrowth;

The advantage here is you will maintain one row per database in the table .But, you will be able to calculate the growth only between the lastrecorded and currentdate. you will not have growth records of previous periods.However, maintaining sucha huge record does not make sense.