TBBLC ( Temperature based block level compression ) in TERADATA

Share via:
TBBLC ( Temperature based block level compression ) in TERADATA

Dear Readers.

In this article ,we will see TBBLC ( Temperature based block level compression ) in TERADATA


You can specify that data is automatically compressed if it is COLD (infrequently accessed) and automatically decompressed when it becomes WARM, HOT, or VERYHOT (more frequently accessed). By default, COLD data is the 20% of the data that is the least often accessed, and HOT and VERYHOT data is the 20% of the data that is the most often accessed. WARM data is anything in between.

Scope of Temperature-based Block-level Compression

After cylinders have been identified as being below the threshold, those cylinders are scanned and only those data blocks from the eligible tables are compressed.

Temperature-based compression can be specified for all user tables, or controlled at the individual table level. When it is applied to an individual table, it will impact all of the subtables and structures associated with that table that qualify for compression.

Below in the “Included list” are the constructs that are impacted by the behavior described earlier in this chapter.  Cylinders supporting the included constructs are candidates for automatic compression, based on their associated base table being identified as a candidate. However, temperature-based compression only applies to cylinders containing permanent tables, and specifically excludes the items in the “Excluded list below.”

Included in temperature-based BLC automatically

Primary data

Fallback data

Index fallback subtables

CLOB subtables

Excluded from temperature-based BLC

Primary copies of index subtables

Tables in database DBC

Spool tables

Permanent journals

Global temporary tables

MultiLoadwork tables

Cylinder indexes

BLOB subtables

Write-ahead Log (WAL)

This means that if you set the appropriate parameters to enable temperature-based compression on a table, the temperature management is automatically extended to include any fallback copies of the base table, and any fallback for index subtables defined on the base table.

Note: The temperature-based compression does not apply to spool tables projected from compressed tables or MultiLoadwork tables that might be utilized to load into the table.

AutoTempComp Task

Temperature-based BLC will rely on a background task called AutoTempComp. This task takes the information provided by the TVS routines and determines which cylinders should be compressed and which should be uncompressed. The task, as with all file system activity, runs independently on each AMP. This means that until a steady state is reached across the system, different portions of the same table are compressed or not compressed on different AMPs.

The AutoTempComp task is responsible for finding the cylinders that are candidates for compression or uncompression based on their current temperature. Within these candidate cylinders, only blocks from eligible tables and their qualifying subtables are compressed. AutoTempComp does this by reading the master index, specifically looking at the cylinder IDs and the appropriate ranges of table IDs. For example, cylinders that contain the primary data from secondary index subtables are recognized as such within the master index, and will be skipped.

AutoTempComp tries to compress a contiguous range of cylinders within a single table so that compressed data blocks, which are smaller, can more easily be consolidated onto fewer cylinders. Once AutoTempComp finds a starting point by reading the master index where data can be operated on, it continues to process adjacent data of the same table as long as the cylinders qualify. After all the cylinders for one table or all the subtables within a cylinder have been processed by AutoTempComp, the task sleeps for 10 minutes.

Compressing Data Loaded into Empty Subtables Set to AUTOTEMP

To assign different temperatures to data loaded in empty primary, fallback, and CLOB subtables 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 CLOBs).

TVSTEMPERATURE_PRIMARYCLOBS : The temperature to assign to the primary row CLOB subtables.

TVSTEMPERATURE_FALLBACK : The temperature to assign to the fallback row subtable, the fallback row secondary index subtables, and the fallback LOB subtables (excluding CLOBs).

TVSTEMPERATURE_FALLBACKCLOBS : The temperature to assign to the fallback row CLOB subtable.

TVSTEMPERATURE                                   : The temperature to assign to all the subtables in the preceding bullets.

For example

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

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.

Restrictions and Limitations of Temperature-Based Block-Level Compression

Only applies to cylinders containing permanent tables

Can make the data have a warmer temperature than it had when it was uncompressed because after compression and cylinder consolidation, each cylinder contains more data than it previously had

Because Teradata Virtual Storage measures temperature at the cylinder level, a greater number of data blocks could mean a higher cylinder access count

How does ATC Scan works

ATC starts scanning through the MI after a database restart and it will start with the cylinders of the biggest table in the MI (i.e. using most cylinders on the given AMP) even if that is not an AutoTemp table.

When ATC finishes processing a cylinder it marks that cylinder’s corresponding cylinder index descriptor (CID) with an AUTOOK flag in the master index (MI) before proceeding to the next cylinder.

In some cases ATC may skip processing a cylinder (due to deadlock and or similar concurrency issues) and in such a case ATC proceeds to the next cylinder without setting the AUTOOK flag in the MI.

An ATC cycle is not considered complete until the AUTOOK flag is set for all cylinders, which means that sometimes it takes multiple passes through the MI to have all the cylinders checked in an ATC cycle.


Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment