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 ;
This is a very informative and useful script. I faced problem with INSERT/SELECT part as the case condition :
, SUM(CASE WHEN TVMId <> ‘000000000000’XB
was throwing error. Same for WHERE condition :
WHERE D.DatabaseId <> ‘000000000000’XB /* All Database */
Can you be more clear on the conditions?
What exactly is < and >? Are they to be placed with some other parameters?
I simply equate both conditions with
, SUM(CASE WHEN TVMId = ‘000000000000’XB — and
WHERE D.DatabaseId = ‘000000000000’XB /* All Database */
But the resultset contained 0 values for most of the columns.
Thank you for pointing out the that < and > were incorrectly translated by my syntax formatting plug-in. They are supposed to be a symbol. I will update the code to try and correct that mistake. The â€˜000000000000â€²XB is the TableID for the ALL psuedo-table in Teradata.
SELECT query works fine now (after the changes), only the tablename at CREATE TABLE and INSERT INTO are different. Must be a typo error !!
Does dbc.tablesize store username?
I would like to find tablesize sum for only a specific user?
Are you looking for the databases or tables created by a specific user that are not owned by the user? (e.g. ProdDB or TestDB.StephsTable) Or everything contained within the user’s database? (e.g. Stephanie.MyTable)
Both can be done with a little work. Let me know what you are looking for and I can help you with the SQL.
I am glad you found it useful. Thank you for commenting!
Comments are closed.