Search code examples
optimizationteradatadatabase-administration

Is there a way to load the results of a teradata show stats values into a table?

I know that you can determine whether a table is block level compressed by executing a show command on the stats values:

show stats values on DBname.TABLEname
-- BLCPctCompressed >0 means yes

My question is whether there is a way to access these results procedurally or in batch? I am working on researching ways to save space on my Teradata system and hunting for tables that are not BLC can be time consuming if you go table by table. I wanted to inquire if there was procedural way to report all the tables in my database with a Y/N for BLC before doing something more complex like constructing a shell script that exports the show stats values on <table> result to a file and runs a perl script to evaluate the BLC line for the Y/N. Perhaps there is a way to insert the results of the show into a table? Something akin to create volatile table vt_stat_rslt as (show stats values on db.table) with data on commit preserve rows

Solution

  • If you're only looking for BLC info, this can be found in the BLCCompRatio column of dbc.StatsV:

     select * from dbc.STatsV where StatsId = 0