Archives of the TeradataForum

Message Posted: Mon, 03 Dec 2001 @ 20:17:03 GMT


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

Subj:		Re: Source for DDL in SHOW TABLE

From:		Dieter N�th

Hi John,

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).

I modified it using position/index, so it's about 10kb smaller, but still brute-force ;-)) I'd like to get the rowcount, but it's stored as a double.

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.

No batch mode, only a print to file. But check the Teradata Manager's Statistics Tool: It now shows statistics, too. And if you really know what you do you're even able to modify statistics...

Dieter

     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)

     /** year: 1984 to 2016 should be enough **/
        ,10000 *
         (position(substr(f.fieldstatistics,1,1) in
             'c0c1c2c3c4c5c6c7c8c9cacbcccdcecf'XB ||
             'd0d1d2d3d4d5d6d7d8d9dadbdcdddedf'XB) + 83)
        +
     /** month **/
       100 *
         (
          position(substr(f.fieldstatistics,3,1) in
             '0102030405060708090A0B0C'XB)
         )
        +
     /** day **/
         (position(substr(f.fieldstatistics,4,1) in
             '0102030405060708090A0B0C0D0E0F'XB ||
             '101112131415161718191a1b1c1d1e1f'XB)
         ) (date) as stat_date,

     /** hour **/
       10000 *
         (
          position(substr(f.fieldstatistics,5,1) in
             '000102030405060708090A0B0C0D0E0F'XB ||
             '101112131415161718'XB) - 1
         ) +
     /** minute **/
       100 *
         (position(substr(f.fieldstatistics,6,1) in
             '000102030405060708090A0B0C0D0E0F'XB ||
             '101112131415161718191a1b1c1d1e1f'XB ||
             '202122232425262728292a2b2c2d2e2f'XB ||
             '303132333435363738393a3b'XB) - 1
         ) +
     /** second **/
         (position( substr(f.fieldstatistics,7,1) in
             '000102030405060708090A0B0C0D0E0F'XB ||
             '101112131415161718191a1b1c1d1e1f'XB ||
             '202122232425262728292a2b2c2d2e2f'XB ||
             '303132333435363738393a3b'XB) - 1
          ) (integer, format '99:99:99') as 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>>

Attachments

Library

Quick Reference