Controlling Block-Level Compression at the System Level | Teradata Vantage - Controlling BLC at the System Level Using the DBS Control Utility - Advanced SQL Engine - Teradata Database

Database Design
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

The DBS Control utility applies BLC at the system level, enabling or disabling compression globally, or for specific types of tables (for example, permanent tables or global temporary tables). Changes to DBS Control compression (block-level) settings have no effect on existing data.

System-Level Fields

The following DBS Control fields control the compression of data blocks at the system level.

DBS Control BLC System-Level Fields Description
BlockLevelCompression Indicates whether the block-level compression feature is enabled.

Other compression settings may separately enable or disable BLC for specific tables or table types.

BLC is disabled by default. When BlockLevelCompression is off, that includes temperature-based block-level compression. Disabling BLC also disables the Ferret COMPRESS command.
EnableTempBLC Enables temperature-based block-level compression. If this value is enabled, Teradata recommends that the DBS Control field DisableAutoCylPack be set to FALSE and AutoCylPackColdData be set to TRUE.

If you disable this value while BlockLevelCompression is enabled, tables using automatic temperature-based compression use MANUAL compression.

At any time, different portions of a table, such as different partitions, may exist in different states of compression depending on their temperatures.
DefaultTableMode Determines how BLC is managed for permanent tables that have the BLOCKCOMPRESSION option set to default or that do not have the BLOCKCOMPRESSION option specified when the table is created. The valid settings follow.
MANUAL: BLC is applied based on the settings of these DBS Control fields, which determine the default BLC for these table types at the time the table is created:
  • CompressPermPrimaryDBs
  • CompressPermFallbackDBs
  • CompressPermPrimaryCLOBDBs
  • CompressPermFallbackCLOBDBs

Tables can be compressed or uncompressed at any time after loading by using the Ferret COMPRESS and UNCOMPRESS commands.

Data inserted into existing tables inherits the current compression status of the table at the time the data is inserted.

Teradata Database manages spool, redrive, MultiLoad work, and permanent journal tables manually only, even if DefaultTableMode is set to NEVER or AUTOTEMP. See the descriptions of CompressSpoolDBs, CompressMloadWorkDBs, and CompressPJDBs in this table.

AUTOTEMP: BLC is automatically performed by Teradata Database based on the frequency of access to the table data (the data temperature). Temperature-based BLC (TBBLC) compresses infrequently accessed data, and uncompresses data that it is accessed frequently.

BLC is automatically performed by Teradata Database based on the frequency of access to the table data (the data temperature). Temperature-based BLC (TBBLC) compresses infrequently accessed data, and uncompresses data that it is accessed frequently.

ALWAYS: Table data is always compressed at the data block level.

NEVER: Table data is never compressed at the data block level.
MinDBSectsToCompress Specifies the minimum size data block to compress. Data blocks that do not meet the minimum size requirement are not compressed. The valid range is 2 through 255 sectors. The default is 16.
MinPercentCompReduction Specifies the minimum percentage by which the size of a data block must be reduced by compression. If compression cannot reduce the data block by at least this amount, the data block is not compressed. The valid range is 0 through 99 percent. The default is 20 percent.
CompressionAlgorithm Specifies the algorithm used to compress and uncompress data blocks and the compression method used (software or hardware-based):
  • ZLIB (Lempel-Ziv algorithm)

    Standard, software-based compression, the default

  • ELZS_H (Exar Lempel-Ziv_Stac algorithm)

    Hardware-based compression available only on systems with compression engine hardware installed on every node.

  • ELZS_S (Exar Lempel-Ziv_Stac algorithm)

    Software equivalent of hardware compression.

    If the setting is ELZS_H and the compression hardware malfunctions, the system automatically defaults to using ELZS_S. Use of ELZS_S is not recommended unless the hardware compression board is malfunctioning.
CompressionZLIBMethod Specifies the implementation of the ZLIB algorithm to use for compressing and uncompressing data blocks.
  • ZLIB (Lempel-Ziv algorithm). Standard ZLIB library.
  • IPPZLIB (Lempel-Ziv algorithm using optimization from the Intel Integrated Performance Primitives (IPP) library). This is the default. This implementation provides better compression performance on Intel platforms.
CompressionLevel Determines whether compression operations favor processing speed or degree of data compression.

The valid range is 1 through 9. A setting of 1 favors compression speed over degree of data compression. A setting of 9 favors degree of data compression over compression speed. The default is 6, which provides a balance.

CompressionLevel affects only software-based block-level compression. It does not apply to hardware-based compression or its equivalent software.
UncompressReservedSpace Specifies the minimum percentage of storage space that must remain available while DBs are uncompressed using the Ferret UNCOMPRESS command.

The UNCOMPRESS operation terminates if the threshold is exceeded. Set this field to represent the expected peak amount of required spool space.

The valid range is 1 through 90%. The default is 20%.
OverrideARCBLC
Determines whether database tables restored from archives have their data blocks compressed according to the effective BLOCKCOMPRESSION setting of each table when it is archived or according to the system-level BLC defaults when the tables are restored.
This field does not affect tables with effective BLOCKCOMPRESSION values of ALWAYS or NEVER or DEFAULT if the DefaultTableMode is set to ALWAYS or NEVER.

Block compression that is controlled by the BLOCKCOMPRESSION query band takes precedence over the OverrideARCBLC field setting.

Valid settings include:
  • TRUE: The system default settings for block-level compression are used to determine whether restored tables are block-level compressed.
  • FALSE: The effective table BLOCKCOMPRESSION value at the time of archiving determine whether restored tables are block-level compressed.

This field does not affect subtables for which the effective BLOCKCOMPRESSION is set to ALWAYS or NEVER on the target system to which the data is restored.

For permanent tables and global temporary tables, different subtables hold primary data, fallback data, and LOB data. BLC for these categories of data can be independently controlled using DBS Control Compression fields 14 - 21 (Compress...DBs fields). These subtable settings are effective only when the BLOCKCOMPRESSION setting for the corresponding base table is set to MANUAL, either explicitly, or as a result of being set to DEFAULT while the DefaultTableMode DBS Control field is set to MANUAL.

Fields Specific to Certain Types of Subtables

The following DBS Control fields control the compression of data blocks for specific types of subtables.

BLC Fields Specific To Certain Types of Tables Description
CompressPermPrimaryDBs Specifies if and when primary subtable data in permanent storage is compressed. This field applies only to tables using MANUAL compression.
CompressPermFallbackDBs Specifies if and when fallback subtables in permanent storage are compressed. This field applies only to tables using MANUAL compression.
CompressPermPrimaryCLOBDBs Specifies if and when primary compressible LOB (JSON, XML, character LOB) subtable data in permanent storage is compressed. This field applies only to tables using MANUAL compression.
CompressPermFallbackCLOBDBs Specifies if and when fallback compressible LOB (JSON, XML, character LOB) subtable data in permanent storage is compressed. This field applies only to tables using MANUAL compression.
CompressSpoolDBs Specifies the conditions under which spool, volatile, and redrive data blocks are compressed. Valid values include:
  • ALWAYS:The data blocks of a new spool table will be compressed.
  • NEVER: The data blocks of a new spool table will not be compressed. This is the default. The Ferret utility COMPRESS command will have no effect.
  • IFNOTCACHED: The DBs of a new table will be compressed if they are not being cached.
CompressMloadWorkDBs Specifies conditions under which data blocks from MultiLoad sort worktables and index maintenance worktables are compressed. MultiLoad worktables are temporary tables built during the acquisition phase of loading.
CompressPJDBs Specifies whether all permanent journal DBs are compressed. Valid values are:
  • ALWAYS: The DBs of a new PJ table will be compressed.
  • NEVER: The DBs of a new PJ table will not be compressed. The Ferret utility COMPRESS command will have no effect.
CompressGlobalTempPrimaryDBs Specifies if and when primary data subtables in global temporary storage are compressed.
CompressGlobalTempFallbackDBs Specifies if and when fallback data subtables in global temporary storage are compressed.
CompressGlobalTempPrimaryCLOBDBs Specifies if and when primary compressible LOB (JSON, XML, character LOB) subtables in global temporary storage are compressed.
CompressGlobalTempFallbackCLOBDBs Specifies if and when fallback compressible LOB (JSON, XML, character LOB) subtables in global temporary storage are compressed.

Valid Settings

Unless otherwise specified above, the valid settings for the preceding DBS Control fields are:

Valid Setting Description
ALWAYS The data blocks of a new table will be compressed regardless of query band options specified at load time.
ONLYIFQBYES The data blocks of a new table will not be compressed unless a query band used at load time specified to compress them.
ONLYIFQBNO The data blocks of a new table will be compressed unless a query band used at load time specified not to compress them.
NEVER The data blocks of a new table will not be compressed regardless of any query band used at load time. The Ferret utility COMPRESS command will have no effect.
Compression is subject to the BlockLevelCompression field setting, and to the criteria specified by the MinPercentCompReduction and MinDBSectsToCompress DBS Control fields. Data blocks not meeting these minimum criteria will not be compressed.

Temperature-Based BLC Fields

The following DBS Control fields control temperature-based block-level compression.

Temperature-based Compression Fields Description
TempBLCThresh Indicates the temperature threshold at which data is compressed in tables using temperature-based compression. Data at or colder than the specified temperature is compressed. Temperatures can be specified by keywords COLD, WARM, HOT, VERYHOT, or a percentage of the coldest allocated user data that should be compressed.
TempBLCSpread Exempts data within a user-specified percentage of the threshold TempBLCThresh from triggering automatic compression or decompression. Use this value to prevent data whose temperature is close to the threshold from being repeatedly compressed and uncompressed. For example, if the TempBLCThresh is defined as COLD and TempBLCSpread is 5%, then the data must be 5% colder than COLD to be compressed and 5% warmer than COLD to be uncompressed.
TempBLCInterval Specifies the time to wait after temperature-based compression/decompression before checking if other data needs to be compressed or decompressed.
TempBLCIOThresh Specifies the maximum number of I/Os on a node before the autocompression background task AutoTempComp sleeps for a short interval. This setting reduces the performance impact of temperature-based compression on foreground workloads. If the system remains busy, the task will sleep according to TempBLCInterval.
TempBLCPriority The priority level of AutoTempComp, the background task that automatically compresses and decompresses data. Valid values are: LOW (or BOTTOM), MEDIUM (or DEFAULT), HIGH, or RUSH (or TOP).
TempBLCRescanPeriod The number of days before the compression of cylinders using temperature-based compression is revalidated. This rescan is necessary because the temperature of a cylinder can change. At least one scan is performed after each startup.