16.20 - FeatureNames_TBF - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Purpose

Use the FeatureNames_TBF table function to perform an easy lookup when aggregating feature use counts.

Table function FeatureNames_TBF was added as part of the Feature Use Logging feature, and is created automatically. The table helps answer the following queries when joined with the DBC.DBQLogTbl.FeatureUsage column using BIT manipulation functions:
  • How many times was a particular feature used in the past week?
  • How many times was a feature used per day?
  • How many times was a feature used in a given period of time?
  • What is the most-used feature in Teradata Database?
  • What percentage of the requests used a certain feature?
  • When was the last time a particular feature was used?

For example, to find what percentage of the requests in DBC.DBQLogTbl use ‘Block Level Compression,’ use:

SELECT CAST((b.FeatureName as char(50)), cast( cast(NULLIFZERO(sum(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos)))) as FLOAT)/count(a.QueryID)*100 as FLOAT FORMAT '----,---,---,---,--9.999' ) as FeatureUsePercent  from DBC.dbqlogtbl a, DBC.QryLogFeatureListV b group by b.FeatureName where b.FeatureName = 'Block Level Compression';


FeatureName                                               FeatureUsePercent
-------------------------------------------------- ------------------------
Block Level Compression                                              65.164

To find the usage percentage of all the features, use:

SELECT CAST(b.FeatureName as char(50)), cast( cast(NULLIFZERO(sum(GetBit(a.FeatureUsage,(2047 - b.FeatureBitpos)))) as FLOAT)/count(a.QueryID)*100 as FLOAT FORMAT '----,---,---,---,--9.999' ) as FeatureUsePercent from DBC.dbqlogtbl a, DBC.QryLogFeatureListV b group by b.FeatureName order by 2 desc;

Syntax



SYSLIB.
Name of the database where the function is located.