CODEWITHSUNDEEP
optimizationteradatadatabase-administration
Daniel Patriarca
Daniel Patriarca

Reputation: 365

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

Upvotes: 0

Views: 822

Answers (3)

dnoeth
dnoeth

Reputation: 60482

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

Upvotes: 2

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

Collect statistics information is usually stored in dbc.columnstatsv or dbc.indexstatsv. You van check these two dbc views and see if it helps.

Upvotes: 0

Fred
Fred

Reputation: 2080

No, the output of a HELP or SHOW can only be returned to the client. It can't be used as a subquery / CTE or accessed in a Stored Procedure.

Consider running the ferret utility directly on the database nodes (one command can list information about many tables).

You might also look at the CreateFsysInfoTable / PopulateFsysInfoTable macros (see the SQL Functions, Operators, Expressions, and Predicates manual) to obtain and store information about one table at a time.

Upvotes: 0

Related Questions