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;