To assign different temperatures to data loaded in empty primary, fallback, and compressible LOB subtables (XML, JSON, and character LOB) that have the AUTOMATIC setting, use the SET QUERY_BAND statement choosing the temperature values VERYHOT, HOT, WARM, or COLD for the following:
- TVSTEMPERATURE_PRIMARY: The temperature to assign to the primary row subtable, the primary row secondary index subtables, and the primary LOB subtables (excluding compressible LOBs).
- TVSTEMPERATURE_PRIMARYCLOBS: The temperature to assign to the primary row compressible LOB subtables.
- TVSTEMPERATURE_FALLBACK: The temperature to assign to the fallback row subtable, the fallback row secondary index subtables, and the fallback LOB subtables (excluding compressible LOBs).
- TVSTEMPERATURE_FALLBACKCLOBS: The temperature to assign to the fallback row compressible LOB subtable.
- TVSTEMPERATURE: The temperature to assign to all the subtables in the preceding bullets.
SET QUERY_BAND = 'TVSTEMPERATURE_FALLBACK = COLD;' FOR SESSION;
This indicates that the fallback data should be cold and the other subtables should use the system default temperature for the table type (as defined in DBS Control).
An example of using multiple specifications is:
SET QUERY_BAND = 'TVSTEMPERATURE_PRIMARY=HOT; TVSTEMPERATURE = COLD;' FOR SESSION;
This indicates that the primary row subtable should be hot and all other subtables should be cold. In this example, the TVSTEMPERATURE name takes precedence over any system default temperature settings.
You can specify multiple TVSTEMPERATURE-style names if the exact name is used only once.
There are a number of advantages to managing compression based on the data temperature.
- Large sets of historical data that are not frequently accessed are often kept uncompressed.
In some cases, you might not even realize that data is no longer being used. Temperature-based compression automatically locates such data and compresses it.
- Data in partitions of partitioned tables are often not frequently used.
Temperature-based compression enables you to compress such partition data automatically.
- By using temperature-based compression with date-partitioned tables where new partitions are constantly being added, but old partitions are retained, you are relieved of determining which partitions are old enough to have become infrequently used.
For more information, see SQL Data Definition Language.