Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 28 Jun 2001 @ 20:47:08 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Statistics about table statistics
 
From:   Geoffrey Rommel

  I would like to be about to produce a report that lists all statistics on all columns with date collected and be able to maintain this info in a table (s) for historical trends.  


  Does anyone have an easy way to do this? I would rather not develop a VB script to strip out the information from a series of HELP STATISTICS...  


VB? Heresy!

I don't know about easy, but I have written a Perl script that does more or less what you describe. I'm just printing the results to stdout, but you could put them in a table instead.

     #!/usr/bin/perl -w
     # Teradata: Show all stats in a database.  These are just reported,
     # not put in a table or anything.

     $db = shift;

     @tbls = tbls_in_db($db);

     foreach $tbl (@tbls) {
        show_stats("$db.$tbl");
     }
     exit;

     #--- SUBROUTINES
     #
     #--- Return all tables in a database.
     sub tbls_in_db {
      my $db = shift;

      open(BTEQ, "bteq <&1
     .run file=/home/grommel/dba/Top /* logon info */
      select TableName
      from DBC.Tables
       where TableKind = 'T'
         and DatabaseName = '$db'
       order by 1;
     .quit
     ZZ
     |") or die "Could not run bteq: $!";

      my @tbls = ();
      while () {
         chomp;
         next if /^TableName/;
         next unless /^(\w+)\s*$/;
         push @tbls, $1;
      }
      close BTEQ;

      return @tbls;
     }

     #--- Run HELP STATISTICS on a table; display results.
     sub show_stats {
     my $tbl = shift;

     open(BTEQ, "bteq <&1
     .run file=/home/grommel/dba/.guest
     .set width 250
      help statistics $tbl;
     .quit
     ZZ
     |") or die "Unable to run BTEQ";

     print "Table $tbl\n";
     while () {
     # If no stats were found, quit now.
        if (/no statistics/) {
           close BTEQ;  return;
        }
        next unless /^[\d\/]{8,10}\s/;
        print "  $_";  ## Or put in a table, or put in an array and sort
     later.
     }

     return;
     }


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016