Example: Using tdheatmap to Create a View - Teradata VantageCloud Lake

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 following view 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 must 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;