FORCE Command | Specifying Data Temperatures | Teradata Vantage - FORCE - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Sets the cylinders occupied by a table or range of rows, to a specified data temperature, regardless of how frequently the data has been historically accessed.

Use the FORCE command only under the direction of Teradata Support Center personnel.

This command can have short- and long-term performance effects on both the targeted table and on other tables. Forcing a specific temperature can impact performance for tables that were not explicitly referenced in the FORCE command.

Syntax

FORCE tid [ rowspec [ TO rowspec ] ]
  TEMPERATURE = { HOT | WARM | COLD | VERYHOT }
tid
Identifies the subtable for which cylinder temperatures are set.
A tid consists of a unique identifier for the table plus an identifier for one subtable component of the table, such as the primary data subtable. The table can be uniquely identified in either of two ways:
  1. Specify the name of the database to which the table belongs and the table name, separated by a period. The names individually or together must be delimited. The following formats are valid:
    • "database_name.table_name"
    • "database_name"."table_name"
    • 'database_name.table_name'
    • 'database_name'.'table_name'
  2. Specify the unique numeric identifier of the table, which consists of two numeric values separated by a space. The unique table identifier is the first two numeric values returned by the TABLEID command. These two numbers are common to all subtables that comprise the table.
    The format of the input numbers depends on the current radix setting, which is displayed by the RADIX command.
The second part of the tid identifies the subtable, and is called a type_and_index. This value can be represented in a number of ways, which are explained in the documentation for the Ferret utility in Teradata Vantage™ - Database Utilities , B035-1102 . The primary data subtable has a numeric type_and_index value of decimal 1024 or hex 400. Specifying a 0 causes the FORCE command to be applied to all subtables of the table.
rowspec  [TO rowspec]
  • For a nonpartitioned NoPI table, the row or range of rows for which a data temperature is to be set.
  • For a partitioned table, the “combined partition number” that identifies the partition for which a data temperature is to be set (or the combined partition numbers that identify a range of logically contiguous partitions).

    The combined partition number is a calculated value that takes into account any column partitions and the different levels of partitioning in multilevel partitioned tables. It uniquely identifies every partition in the table.

    For tables with only a single-level partition without column partitioning, the combined partition number for any row is the value of the system-derived PARTITION column of the row.

    For more information about calculating combined partition numbers for other types of tables, see Teradata Vantage™ - Database Design, B035-1094.

VERYHOT
HOT
WARM
COLD
The data temperature that will be set for the cylinders that store row data for the specified table or rows, regardless of the historical frequency of access for that data.
  • VERYHOT cylinders are the most frequently accessed.
  • HOT cylinders are frequently accessed.
  • WARM cylinders are accessed with moderate frequency.
  • COLD cylinders store the least frequently accessed data.

Usage Notes

For new tables, Vantage does not yet have metrics on data access to use in assigning a temperature to the table data, so relies on the defaults set in the Storage group of DBS Control settings. The FORCE command can be used to force a non-default temperature on new tables without waiting for the system to collect data access statistics.

For tables with definitions that include the BLOCKCOMPRESSION = AUTOTEMP option (that is, tables using the temperature-based block-level compression feature), forcing uncompressed data to colder temperatures can cause the data to be compressed, and forcing compressed data to warmer temperatures can cause the data to be uncompressed. If the target of the FORCE command is a large amount of data, this could impact performance in the short term.

In addition to setting the temperature for whole tables, FORCE can be used to set the temperature for a row, range of rows, or specific partitions. For example:
  • When data in older rows of a nonpartitioned NoPI table is less frequently accessed than data in newer rows, you can use FORCE to set the older rows to COLD.
  • When one or more partitions of a partitioned table are expected to be more frequently accessed than other partitions, you can use FORCE to set the frequently accessed partitions to HOT.

When temperature-based block-level compression is enabled, using FORCE to set infrequently accessed tables or partitions to COLD causes them to be compressed sooner than they otherwise would be.

Data temperature can change over time from the temperature specified with the FORCE command if the measured frequency of actual data access differs from the temperature set using the FORCE command.

The temperature of boundary cylinders that store rows from more than a single table are not affected by the FORCE command if COLD is specified.

The FORCE command ignores any table scope that have been previously specified, and operates only on the table that is explicitly specified in the FORCE command.

Related Topics

For more information on… See…
nonpartitioned NoPI tables Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
PARTITION columns Teradata Vantage™ - Database Design, B035-1094.

Example: Using FORCE to change the temperature of a table

The following example changes the temperature of a table.

Ferret  ==>
> force 0 6CE 400 temperature = hot

FORCE command changed the temperature of table test.table1 to HOT.
The temperature of 117 cylinders have been changed to HOT.

Example: Using FORCE to change the temperature of a row partition

The following example sets the temperature for a subset of rows a partitioned table. In this case, the rows are those in the combined partition identified by an external partition number of 9.

Ferret  ==>
> force "test.ppi_t02" 400 9 temperature = hot

FORCE command changed the temperature of table test.ppi_t02
to HOT for the specified partition.
The temperature of 5 cylinders have been changed to HOT.