UDF Library: 'stat_dt'

Component: 'stat_values_howto.txt'

=== stat_values ===

This is a Table UDF that converts the FieldStatistics (or IndexStatistics)
byte string to a table of readable columns.

*** WARNING! ***
This code has been tested only on a Demo version of Teradata (12.00.00.04)
running under Windows XP. An earlier version was tested on another demo,
version V2R.06.01.01.17. It should work on other versions of Teradata, but
there are no guarantees, and you may need to make a few changes. Install
and use at your own risk.

TO INSTALL:

1. Copy the C code to an appropriate client or server location. No changes
   are necessary, unless you wish to change the error messages or SQLSTATEs
   used. (There are two error conditions.)

2. Modify the "create function" statement shown below for your environment.
   In particular, you may wish to install the function in SYSLIB rather than
   SYSDBA.

3. Create the function and test it.

4. When the function is tested to your satisfaction, you may wish to run
   it in unprotected mode.

TO INVOKE:

Syntax: sysdba.stat_values ( identifier  integer,
   FieldStatistics  varbyte(16400) )
returns table
  (StatIdent      integer
  ,CollectDate    date
  ,CollectTime    time(0)
  ,StatsVersion   smallint
  ,SampleSize     smallint
  ,NumAMPs        integer
  ,NumRows        float
  ,NumValues      float
  ,NumNulls       float
  ,ModeFreq       float  )

Since the function returns a table, it is not invoked like a scalar UDF.
Instead, it must be joined to another table that contains the raw data.
Moreover, there must be some way of matching the raw row to the returned
row. For this purpose, the first parameter passed to the function is an
integer that uniquely identifies each row to be converted. This integer
will be the first column returned by the function; you can then match
on that identifier. Here is an example:

select t1.*, t2.*
from
(SELECT DatabaseName,
  TableName,
  ColumnName,
  FieldStatistics,
  sum(1) over(order by DatabaseName, TableName, ColumnName
    rows unbounded preceding) as ident
  from DBC.ColumnStats
  where FieldStatistics is not null) t1,
 TABLE (sysdba.stat_values(t1.ident, t1.FieldStatistics)) as t2
 where t1.ident=t2.StatIdent
order by 1,2,3;

The function can also be passed a literal byte string, thus:

select *
from table(sysdba.stat_values(1,
'D2070808000914620200000000000000000000000D000080564945544E414D202020202020202020414C4745524941202020202020202020000000000000F03F000000000000394000000000000039404152474'xb)) a;

NULL HANDLING:

The function will correctly handle nulls in either input parameter.
However, it wouldn't make much sense to pass a null identifier, since
it would then be impossible to match the converted rows to the original
bytes.


CREATE FUNCTION STATEMENT:

drop function sysdba.stat_values;

create function sysdba.stat_values
 ( integer,
   /* The varbyte length can be shorter, perhaps; only the first 80
      bytes are actually used in the function. */
   varbyte(16400) )
returns table
  (StatIdent      integer
  ,CollectDate    date
  ,CollectTime    time(0)
  ,StatsVersion   smallint
  ,SampleSize     smallint
  ,NumAMPs        integer
  ,NumRows        float
  ,NumValues      float
  ,NumNulls       float
  ,ModeFreq       float  )
language C
no sql
parameter style sql
external name
 'CS!stat_values!C:\Documents and Settings\Geoffrey_adm\My Documents\UDFs\stat_values.c!F!stat_values';