Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 29 Nov 2001 @ 17:41:16 GMT


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


Subj:   Re: Source for DDL in SHOW TABLE
 
From:   John Hall

I found a VIEW that I wrote a long time ago that isolates the date/time of statistics that were collected on a column. It's pretty much brute-force and lacks any elegance (if anybody improves it, I would love to get a more elegant solution). It's been a while since I last played with it, but it should give you some ideas (a similar VIEW would be needed for indexes - if anybody is interested, I'll post it to the list).

I've never been able to lay my hands on the field format of FIELDSTATISTICS or INDEXSTATISTICS. Even if we could get a copy, they aren't supported and subject to change (this is why, on occasion, that it's been necessary to re-collect statistics when moving from one release to another).

The date/time portion of FIELDSTATISTICS seems to be pretty consistent, but while trying to reverse-engineer FIELDSTATISTICS, the other fields (ie- modes) seemed to have moved. I stopped pursuing this because I would hate to put too much work into a system tool that might break unexpectedly just as I've upgraded to a new release (like it doesn't have enough potential for fun already ).

So instead of spending time on FIELDSTATISTICS/INDEXSTATISTICS, I just wrote a BTEQ script that generated a list of HELP STATISTICS. I then parse the output of the HELP STATISTICS to get the date/time. Although crude, it's much easier and less likely to blow-up when I need it.

There's also InTeraStats from Jim Calvert (see the link at www.teradataforum.com - under 'USEFUL LINKS'). The last time that I looked at InTeraStats was a number of years ago and so I don't know if it has any output that would be useful (like a batch mode and output to a file). If anybody has any experience with it, it would be interesting to hear about it.

/* --------------------------------------------------------
 *
 */

replace view db.column_statistics
   as sel d.databasename           (named databasename)
        , t.tvmnamei               (named tablename)
        , f.fieldname              (named columnname)
        , d.databaseid             (named databaseid)
        , t.tvmid                  (named tableid)
        , f.fieldid                (named fieldid)
        , f.fieldtype              (named fieldtype)
        , f.maxlength              (named maxlength)
        , f.totaldigits            (named totaldigits)
        , f.impliedpoint           (named impliedpoint)
        , f.nullable               (named nullable)
        , f.compressible           (named compressible)

  , (case when substr(f.fieldstatistics,2,1) = '07'xb
             and substr(f.fieldstatistics,1,1) > 'bf'xb
             and substr(f.fieldstatistics,1,1) < 'd0'xb
                 then (case substr(f.fieldstatistics,1,1)
                           when 'c8'xb then 0920000
                           when 'c9'xb then 0930000
                           when 'ca'xb then 0940000
                           when 'cb'xb then 0950000
                           when 'cc'xb then 0960000
                           when 'cd'xb then 0970000
                           when 'ce'xb then 0980000
                           when 'cf'xb then 0990000
                           end
                      )
          when substr(f.fieldstatistics,2,1) = '07'xb
             and substr(f.fieldstatistics,1,1) > 'cf'xb
             and substr(f.fieldstatistics,1,1) < 'e0'xb
                 then (case substr(f.fieldstatistics,1,1)
                            when 'd0'xb then 1000000
                            when 'd1'xb then 1010000
                            when 'd2'xb then 1020000
                            when 'd3'xb then 1030000
                            when 'd4'xb then 1040000
                            when 'd5'xb then 1050000
                            when 'd6'xb then 1060000
                            when 'd7'xb then 1070000
                            when 'd8'xb then 1080000
                            when 'd9'xb then 1090000
                            when 'da'xb then 1100000
                            when 'db'xb then 1110000
                            when 'dc'xb then 1120000
                            when 'dd'xb then 1130000
                            when 'de'xb then 1140000
                            when 'df'xb then 1150000
                            end
                      )
            else null
            end
      )

    + (case substr(f.fieldstatistics,3,1)
            when '01'xb then 0100
            when '02'xb then 0200
            when '03'xb then 0300
            when '04'xb then 0400
            when '05'xb then 0500
            when '06'xb then 0600
            when '07'xb then 0700
            when '08'xb then 0800
            when '09'xb then 0900
            when '0a'xb then 1000
            when '0b'xb then 1100
            when '0c'xb then 1200
            else null
            end
      )

    + (case when substr(f.fieldstatistics,4,1) > '00'xb
             and substr(f.fieldstatistics,4,1) < '10'xb
                 then (case substr(f.fieldstatistics,4,1)
                           when '01'xb then 01
                           when '02'xb then 02
                           when '03'xb then 03
                           when '04'xb then 04
                           when '05'xb then 05
                           when '06'xb then 06
                           when '07'xb then 07
                           when '08'xb then 08
                           when '09'xb then 09
                           when '0a'xb then 10
                           when '0b'xb then 11
                           when '0c'xb then 12
                           when '0d'xb then 13
                           when '0e'xb then 14
                           when '0f'xb then 15
                           end
                      )
            when substr(f.fieldstatistics,4,1) > '0f'xb
             and substr(f.fieldstatistics,4,1) < '20'xb
                 then (case substr(f.fieldstatistics,4,1)
                            when '10'xb then 16
                            when '11'xb then 17
                            when '12'xb then 18
                            when '13'xb then 19
                            when '14'xb then 20
                            when '15'xb then 21
                            when '16'xb then 22
                            when '17'xb then 23
                            when '18'xb then 24
                            when '19'xb then 25
                            when '1a'xb then 26
                            when '1b'xb then 27
                            when '1c'xb then 28
                            when '1d'xb then 29
                            when '1e'xb then 30
                            when '1f'xb then 31
                            end
                      )
            else null
            end
      )     (date)    (named stat_date)

    , (case when substr(f.fieldstatistics,5,1) < '10'xb
                 then (case substr(f.fieldstatistics,5,1)
                            when '00'xb then 000000
                            when '01'xb then 010000
                            when '02'xb then 020000
                            when '03'xb then 030000
                            when '04'xb then 040000
                            when '05'xb then 050000
                            when '06'xb then 060000
                            when '07'xb then 070000
                            when '08'xb then 080000
                            when '09'xb then 090000
                            when '0a'xb then 100000
                            when '0b'xb then 110000
                            when '0c'xb then 120000
                            when '0d'xb then 130000
                            when '0e'xb then 140000
                            when '0f'xb then 150000
                            end
                      )
            when substr(f.fieldstatistics,5,1) > '0f'xb
             and substr(f.fieldstatistics,5,1) < '18'xb
                 then (case substr(f.fieldstatistics,5,1)
                            when '10'xb then 160000
                            when '11'xb then 170000
                            when '12'xb then 180000
                            when '13'xb then 190000
                            when '14'xb then 200000
                            when '15'xb then 210000
                            when '16'xb then 220000
                            when '17'xb then 230000
                            when '18'xb then 240000
                            end
                      )
            else null
            end
      )

    + (case when substr(f.fieldstatistics,6,1) < '10'xb
                 then (case substr(f.fieldstatistics,6,1)
                            when '00'xb then 0000
                            when '01'xb then 0100
                            when '02'xb then 0200
                            when '03'xb then 0300
                            when '04'xb then 0400
                            when '05'xb then 0500
                            when '06'xb then 0600
                            when '07'xb then 0700
                            when '08'xb then 0800
                            when '09'xb then 0900
                            when '0a'xb then 1000
                            when '0b'xb then 1100
                            when '0c'xb then 1200
                            when '0d'xb then 1300
                            when '0e'xb then 1400
                            when '0f'xb then 1500
                            end
                      )
            when substr(f.fieldstatistics,6,1) > '0f'xb
             and substr(f.fieldstatistics,6,1) < '20'xb
                 then (case substr(f.fieldstatistics,6,1)
                            when '10'xb then 1600
                            when '11'xb then 1700
                            when '12'xb then 1800
                            when '13'xb then 1900
                            when '14'xb then 2000
                            when '15'xb then 2100
                            when '16'xb then 2200
                            when '17'xb then 2300
                            when '18'xb then 2400
                            when '19'xb then 2500
                            when '1a'xb then 2600
                            when '1b'xb then 2700
                            when '1c'xb then 2800
                            when '1d'xb then 2900
                            when '1e'xb then 3000
                            when '1f'xb then 3100
                            end
                      )
            when substr(f.fieldstatistics,6,1) > '1f'xb
             and substr(f.fieldstatistics,6,1) < '30'xb
                 then (case substr(f.fieldstatistics,6,1)
                            when '20'xb then 3200
                            when '21'xb then 3300
                            when '22'xb then 3400
                            when '23'xb then 3500
                            when '24'xb then 3600
                            when '25'xb then 3700
                            when '26'xb then 3800
                            when '27'xb then 3900
                            when '28'xb then 4000
                            when '29'xb then 4100
                            when '2a'xb then 4200
                            when '2b'xb then 4300
                            when '2c'xb then 4400
                            when '2d'xb then 4500
                            when '2e'xb then 4600
                            when '2f'xb then 4700
                            end
                      )
            when substr(f.fieldstatistics,6,1) > '2f'xb
             and substr(f.fieldstatistics,6,1) < '3c'xb
                 then (case substr(f.fieldstatistics,6,1)
                            when '30'xb then 4800
                            when '31'xb then 4900
                            when '32'xb then 5000
                            when '33'xb then 5100
                            when '34'xb then 5200
                            when '35'xb then 5300
                            when '36'xb then 5400
                            when '37'xb then 5500
                            when '38'xb then 5600
                            when '39'xb then 5700
                            when '3a'xb then 5800
                            when '3b'xb then 5900
                            end
                      )
            else null
            end
       )

    + (case when substr(f.fieldstatistics,7,1) < '10'xb
                 then (case substr(f.fieldstatistics,7,1)
                            when '00'xb then 00
                            when '01'xb then 01
                            when '02'xb then 02
                            when '03'xb then 03
                            when '04'xb then 04
                            when '05'xb then 05
                            when '06'xb then 06
                            when '07'xb then 07
                            when '08'xb then 08
                            when '09'xb then 09
                            when '0a'xb then 10
                            when '0b'xb then 11
                            when '0c'xb then 12
                            when '0d'xb then 13
                            when '0e'xb then 14
                            when '0f'xb then 15
                            end
                      )
            when substr(f.fieldstatistics,7,1) > '0f'xb
             and substr(f.fieldstatistics,7,1) < '20'xb
                 then (case substr(f.fieldstatistics,7,1)
                            when '10'xb then 16
                            when '11'xb then 17
                            when '12'xb then 18
                            when '13'xb then 19
                            when '14'xb then 20
                            when '15'xb then 21
                            when '16'xb then 22
                            when '17'xb then 23
                            when '18'xb then 24
                            when '19'xb then 25
                            when '1a'xb then 26
                            when '1b'xb then 27
                            when '1c'xb then 28
                            when '1d'xb then 29
                            when '1e'xb then 30
                            when '1f'xb then 31
                            end
                      )
            when substr(f.fieldstatistics,7,1) > '1f'xb
             and substr(f.fieldstatistics,7,1) < '30'xb
                 then (case substr(f.fieldstatistics,7,1)
                            when '20'xb then 32
                            when '21'xb then 33
                            when '22'xb then 34
                            when '23'xb then 35
                            when '24'xb then 36
                            when '25'xb then 37
                            when '26'xb then 38
                            when '27'xb then 39
                            when '28'xb then 40
                            when '29'xb then 41
                            when '2a'xb then 42
                            when '2b'xb then 43
                            when '2c'xb then 44
                            when '2d'xb then 45
                            when '2e'xb then 46
                            when '2f'xb then 47
                            end
                      )
            when substr(f.fieldstatistics,7,1) > '2f'xb
             and substr(f.fieldstatistics,7,1) < '3c'xb
                 then (case substr(f.fieldstatistics,7,1)
                            when '30'xb then 48
                            when '31'xb then 49
                            when '32'xb then 50
                            when '33'xb then 51
                            when '34'xb then 52
                            when '35'xb then 53
                            when '36'xb then 54
                            when '37'xb then 55
                            when '38'xb then 56
                            when '39'xb then 57
                            when '3a'xb then 58
                            when '3b'xb then 59
                            end
                      )
            else null
            end
       )     (integer, format '99:99:99')   (named stat_time)

  from dbc.dbase              d
     , dbc.tvm                t
     , dbc.tvfields           f

  where f.databaseid = d.databaseid
    and f.databaseid = t.databaseid
    and f.tableid = t.tvmid
   ;


     
  <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: 15 Jun 2023