Rob Paller A blog about databases, business intelligence, and an outlet for my inner geek

Building a Teradata Object Catalog

Recently I was asked to provide an inventory of a production Teradata system that included a list of all user created objects and as much information about the objects that I could gather included perm space usage, row counts, and object type. Two of the three could easily be gathered using the data dictionary tables in DBC. However, the row count requirement made me pause for a moment and weigh the impact this could have. The production system that I was working on had hundreds of databases and thousands of tables.

I knew writing an SQL script that would build the SELECT queries that would perform a COUNT(*) against every table and capture that in a flat file or table on the system was trivial. However, when you have a system with thousands or tens of thousands of objects it would take quite some time to run all of those statements even if many of the tables are relatively small. It just didn't make good sense to take that approach.

Then I realized that nearly every table had some level of statistics collected against it and maintained at least semi-regularly depending on the DBA that was responsible for the database. The trouble is that Teradata's HELP STATISTICS command only shows you the number of unique values for each set of columns containing statistics. But then I remembered that the view I used as part of my stats maintenance routine did have a column for the number of rows. The views that I use as the basis for my stats maintenance routine were written by Dieter Noeth and is available for the general public to use courtesy of the TeradataForum. For the sake of brevity and maintaining a single source for the code I am not going to post the SQL for the views here but you can find them here in the Attachment Library over at the TeradataForum. (It should be noted that there are two different sets of views that support both the 32 bit and 64 bit versions of Teradata. Make sure you use the correct set of views for your installation.)

In order to keep things organized there are three views used to gather the information instead of creating a bunch of derived tables. The view Stats_Data is responsible for collapsing the number of fields into a comma delimited list for the first 16 columns and then extracting the pertinent binary data for the statistics collected on a given set of columns. The second view Stats_Basics_[32/64]bit extrapolates the binary data from the Stats_Data view into individual binary fields that require further refining. The final view Stats_Details composes the binary data into its final numeric and temporal values from which additional reporting and analysis can be done.

With these three views in place the Object_Catalog view could easily be constructed by simply bringing together a view additional data dictionary tables and the Stats_Details view. The Object_Catalog view displays the following attributes about the objects in each database on the system:

• Database Name - self-explanatory
• Object Name - self-explanatory
• Column Count - This is a count of the number of fields for the object in TVFields.
• Table Rows - The number of rows on the table when the stats were last collected. NULL if stats are not collected.
• Table Size - The current perm space being consumed by the table.
• Table Missing Stats - Indicator field for if stats exist on the table. NULL for non-table objects.
• Table Kind - A single character field categorizing the object type. (e.g. T, V, M, etc.)
• Table Kind Description - A description of the object type (e.g. Table, View, Macro, etc.)

The SQL for the Object_Catalog view can be found below:

Teradata Routine Maintenance: Perm Space History

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
;