17.10 - BlockCompression Reserved Storage Management Query Bands - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
As a general rule, customer-written applications should not use these query band names. They are reserved for use by Teradata-written applications and applications written by third party Teradata partners.

The primary and fallback subtables of a table can have independent block-level compression attributes.

You can use the BlockCompression query band with tables whose effective BLOCKCOMPRESSION attribute is MANUAL. In that case, the query band indicates if the data being loaded into an empty primary or fallback table (or both) should be compressed at the data block level.

The specifications apply to permanent or global temporary table data, but do not apply to Spool data or Permanent Journal data.

The valid BlockCompression values apply, as noted, to primary data tables, fallback data tables, and work tables.

BlockCompression indicates if the data being loaded into an empty primary or fallback table (or both) should be compressed at the data block level. See Adding, Modifying, or Dropping Block-Level Compression.

Value Description
ALL Compress all new primary and fallback table data at the data block level.

This option is equivalent to specifying YES.

FALLBACK Compress new fallback data, but do not compress primary table data.
FALLBACKANDCLOBS Compress fallback data, including fallback and primary LOBs that are eligible for compression.
  • NO
  • NONE
These options are equivalent and specify not to compress new primary and fallback table data at the data block level.
ONLYCLOBS Compress only new LOB data that is eligible for compression.
WITHOUTCLOBS Compress all data, except for LOB data.
YES Compress all new primary and fallback table data at the data block level.

This option is equivalent to specifying ALL.

You can also control block-level compression using the FERRET utility and the compression fields of the DBS Control record. For more information about these DBS Control parameters, see Teradata Vantage™ - Database Utilities, B035-1102.

You can use the BlockCompression query band and the DBS Control flags in the following list, or both to control block-level compression.
  • CompressPermPrimaryDBs
  • CompressPermFallbackDBs
  • CompressPermPrimaryCLOBDBs
  • CompressPermFallbackCLOBDBs
  • CompressGlobalTempPrimaryDBs
  • CompressGlobalTempFallbackDBs
  • CompressGlobalTempPrimaryCLOBDBs
  • CompressGlobalTempFallbackCLOBDBs

If you use both, the BlockCompression query band setting overrides the DBS Control setting for any affected subtables unless the DBS Control setting is NEVER.

The following table summarizes the effects on various data of the BlockCompression query band settings.

Value Primary Table Fallback Table Primary Subtable Compressible LOBs Fallback Subtable Compressible LOBs
None Default Default Default Default
ALL Compressed Compressed Compressed Compressed
FALLBACK Not compressed Compressed Not compressed Compressed
  • NO
  • NONE
Not compressed Not compressed Not compressed Not compressed
ONLYCLOBS Not compressed Not compressed Compressed Compressed
WITHOUTCLOBS Compressed Compressed Not compressed Not compressed
YES Compressed Compressed Compressed Compressed