 |
 |
Archives of the TeradataForum
Message Posted: Thu, 29 Nov 2001 @ 17:41:16 GMT
| 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
;
| |
