Example: Using tdheatmap to Create a View - Advanced SQL Engine - Teradata Database

Use the tdheatmap function to construct a view to periodically query and get snapshots of AMP-level data temperature changes over time. This allows you to see trends in the data access patterns.

The definition of the view below includes all of the columns returned by the heatmap table function, with the addition of the Date, Time, Database name, Table name and Cylinder ID fields. Replace target database name with the name of the designated view database, and replace amp_number with the number of the AMP the function needs to run on.

REPLACE view target database name.tdheatmap_v as
SELECT  date TheDate
      , time TheTime
      , t1.ampnumber
      , dbase.databasenamei  DatabaseName
      , tvm.tvmnamei  TableName
      , t1.starttableid
      , t1.starttableiduniq
      , t1.starttableidtypeandindex
      , t1.startpartition
      , t1.startrowid
      , t1.endtableid
      , t1.endtableiduniq
      , t1.endtableidtypeandindex
      , t1.endpartition
      , t1.endrowhash
      , to_byte(t1.cylinderidmsw) || to_byte(t1.cylinderidlsw) CylinderId
      , t1.cylinderidmsw
      , t1.cylinderidlsw
      , t1.temperature
      , t1.normalizedtempinfo
      , t1.requestedtempinfo
      , t1.veryhotcandidate
      , t1.veryhotcache
      , t1.tempwarmceiling
      , t1.tempwarmfloor
      , t1.tempveryhotfloor
      , t1.percentfull
      , t1.temppercentile
      , t1.grade
      , t1.mediatype
      , t1.storageclass from table (syslib.tdheatmap (amp_number))
   as t1 (
            AmpNumber,
            StartTableId,
            StartTableIdUniq,
            StartTableIdTypeAndIndex,
            StartPartition,
            StartRowId,
            EndTableId,
            EndTableIdUniq,
            EndTableIdTypeAndIndex,
            EndPartition,
            EndRowHash,
            CylinderIdMsw,
            CylinderIdLsw,
            Temperature,
            NormalizedTempInfo,
            RequestedTempInfo,
            VeryHotCandidate,
            VeryHotCache,
            TempWarmCeiling,
            TempWarmFloor,
            TempVeryHotFloor,
            PercentFull,
            TempPercentile,
            Grade,
            MediaType,
            StorageClass),
dbc.tvm tvm,
dbc.dbase dbase
where t1.starttableiduniq = tvm.tvmid
    and tvm.databaseid = dbase.databaseid
;

GRANT SELECT on dbc.tvm to target database name with GRANT option;
GRANT SELECT on dbc.dbase to target database name with GRANT option;
GRANT EXECUTE function on syslib.tdheatmap to target database name with GRANT option;

Using a view is useful for tracking data temperature trends over time. You can use the view to create a heatmap history table:

/* CREATE the history table with the first set of heatmap */
CREATE TABLE target database name.heatmap_history
AS
(SELECT * FROM target database name.tdheatmap_v)
WITH DATA
PRIMARY INDEX (TheDate, TheTime)
 

/* Load the subsequent heatmap data */
INSERT INTO target database name.heatmap_history
SELECT * from target database name.tdheatmap_v;