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.