15.00 - FORCE - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

FORCE

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.

Syntax  

 

Syntax Element

Descriptions

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.

    Note: 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 Ferret chapter of Utilities and Filer chapter of Support Utilities. 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 PARTITON column of the row.

    For more information on calculating combined partition numbers for other types of tables, see Database Design.

    HOT

    WARM

    COLD

    VERYHOT

    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, Teradata Database 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.

    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 will cause 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 might 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

    SQL Data Definition Language.

    PARTITION columns

    Database Design.

    rowspecs

    the Filer chapter of Support Utilities.

    Example  

    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  

    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.