A user over at TeradataQuestions recently asked a if there was a table in Teradata that allowed you to track the growth of tables or databases over time. The short answer to this question was no. The data dictionary tables in Teradata only provider the current information about tablesizes and the current and max sizes of databases. If you want to track this historically for chargeback or capacity planning you need to write your own maintenance routine.

The good news is this routine is fairly straightforward once you figure out what information you want to retain historically. The data dictionary objects that will be involved in this are DBC.Dbase, DBC.TVM, and DBC.DatabaseSpace. Originally, I was going to use the data dictionary view DBC.TableSize but opted to denormalize the table and retain the database level information here as well.

I am using a database called SysDBA in this example. This is where I prefer to keep all my DBA related objects that go above and beyond what Teradata provides in the Data Dictionary database (DBC), keeping my objects separate from their objects as a matter of personal preference. Below you will find the table definition that I am using. I opted to use the same data type that is found in DBC.DatabaseSpace for consistency sake, but that isn’t to say you couldn’t opt to retain the value in BIGINT or DECIMAL. Teradata is tracking the tables sizes in bytes but I have opted to also capture them in megabyte and gigabyte equivalents. I have also decided to retain both the current and max database size on this table as well by leveraging the ALL table. I have excluded PeakPerm from this, but it could easily be included if you feel there is value knowing what the PeakPerm was for a database when the data was capture in the table.

CREATE TABLE SysDBA.PermSpaceHist, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      DatabaseName VARCHAR(30) CHARACTERSET LATIN NOT CASESPECFIC NOT NULL,
      TableName VARCHAR(30) CHARACTERSET LATIN NOT CASESPECIFIC NOT NULL,
      TableSize FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      TableSizeMB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      TableSizeGB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      DatabaseSize FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      DatabaseSizeMB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      DatabaseSizeGB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      MaxDatabaseSize FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      MaxDatabaseSizeMB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      MaxDatabaseSizeGB FLOAT FORMAT '----,---,---,---,--9' NOT NULL,
      CollectTimestamp TIMESTAMP(0) NOT NULL
     )
PRIMARY INDEX PIDX (DatabaseName)
;

I prefer to use a BTEQ script to maintain this table. This allows me to schedule it to run using cron from our ETL server. If you have an enterprise scheduling system or ETL tool that allows you to run custom scripts those would be appropriate locations to run this. This data is currently being captured on a weekly basis as there is no need to track the growth at a daily basis currently.

Below is core SQL to maintain this table.

INSERT INTO SysDBA.TableSizeHist
SELECT D.DatabaseName
     , T.TVMName AS TableName
     , SUM(CASE WHEN TVMId <> '000000000000'XB
                THEN CurrentPermSpace
                ELSE NULL
           END ) AS CurrentTablePerm
     , CurrentTablePerm / 1024000.0000 AS CurrentTablePermMB
     , CurrentTablePerm / 1024000000.0000 AS CurrentTablePermGB
     , SUM(CASE WHEN TVMId = '000000000000'XB
                THEN CurrentPermSpace
                ELSE NULL
           END ) AS CurrentDBPerm
     , CurrentDBPerm / 1024000.0000 AS CurrentDBPermMB
     , CurrentDBPerm / 1024000000.0000 AS CurrentDBPermGB
     , SUM(CASE WHEN TVMId = '000000000000'XB
                THEN MaxPermSpace
                ELSE NULL
           END ) AS MaxDBPerm
     , MaxDBPerm / 1024000.0000 AS MaxDBPermMB
     , MaxDBPerm / 1024000000.0000 AS MaxDBPermGB
     , CURRENT_TIMESTAMP(0)
FROM DBC.DatabaseSpace S
INNER JOIN
     DBC.Dbase D
      ON D.DatabaseId = S.DatabaseId
INNER JOIN
     DBC.TVM T
      ON T.TVMId       = S.TableId
WHERE D.DatabaseId <> '000000000000'XB /* All Database */
ORDER BY 1,2
GROUP BY 1,2
;