BLCINFO - 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

Displays the expected initial compression status of data in rows added to empty tables for specified combinations of block-level compression (BLC) settings.

This command displays the initial subtable compression status that would result from different combinations of compression settings. It is for planning purposes only, and does not actually change any compression settings on the system.
Compression status is listed for four types of subtables:
  • Primary data subtable
  • Fallback data subtable
  • Primary CLOB data subtable
  • Fallback CLOB data subtable

Syntax

{ BLCINFO | BLCI } {

  [ALL] { BLOCKCOMPRESSIONTLA | BLOCK } =
    { AUTOTEMP | MANUAL | ALWAYS | NEVER | DEFAULT }
    { DEFAULTTABLEMODE | DEFAULT } =
    { AUTOTEMP | MANUAL | ALWAYS | NEVER | USEDBSCONTROL } |

  { FLOWDIAGRAM | FLOW } |

  { ALGORITHM | ALGO }
}
BLOCKCOMPRESSIONTLA
BLOCK
Specifies a valid setting of the BLOCKCOMPRESSION table level attribute. The table level attribute is the setting applied when the table was created using CREATE TABLE or altered using ALTER TABLE. For more information on this CREATE TABLE and ALTER TABLE option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
DEFAULTTABLEMODE
DEFAULT
Specifies a valid setting of DefaultTableMode in DBS Control. For more information on DefaultTableMode, see the description of DBS Control in Teradata Vantage™ - Database Utilities , B035-1102 .
USEDBSCONTROL means to use the value of DefaultTableMode that is currently set in DBS Control.
ALL
Given the selected options for BLOCKCOMPRESSIONTLA and DEFAULTTABLEMODE, shows which subtable data will be compressed for all combinations of the remaining compression settings and query bands.
FLOWDIAGRAM
FLOW
Displays a flow diagram that describes the decisions that determine whether a subtable is compressed or uncompressed based on several BLC settings.
ALGORITHM
ALGO
Displays the pseudo code that describes the algorithm used to determine whether a subtable is compressed or uncompressed based on several BLC settings.

Usage Notes

The command prompts for DBS Control and query band settings as necessary to describe a unique set of BLC conditions.

Compression status of each of the four subtable types is indicated by a C for compressed or U for uncompressed.

The listed compression status represents only the initial compression status of data added to an empty table. If temperature-based BLC (TBBLC) is enabled, the compression status can change with time for tables with a BLOCKCOMPRESSION value of AUTOTEMP.

The BLOCKCOMPRESSION = AUTOTEMP table level attribute can be explicitly specified when a table is created or altered. If DefaultTableMode in DBS Control is set to AUTOTEMP, BLOCKCOMPRESSION is automatically set to AUTOTEMP for tables that do not otherwise specify a value for that attribute. For AUTOTEMP tables, data is automatically compressed or uncompressed based on the frequency of data access.

Example: Using BLCINFO to show expected compression status of newly loaded data

The following example shows that, given the specified combination of DBS Control compression settings, and using a Query Band of BLOCKCOMPRESSION = FALLBACK when data is loaded into a new, empty table that was created with table-level attribute of BLOCKCOMPRESSION = MANUAL, all primary data (regular and CLOB) will be loaded into the table in a non-compressed state, but all fallback data for the table will be compressed.

Ferret  ==>
blcinfo blockcompressiontla=manual defaulttablemode=manual

Enter BLOCKCOMPRESSION Query Band:
1. DEFAULT
2. YES
3. NO
4. ALL
5. NONE
6. FALLBACK
7. ONLYCLOBS
8. WITHOUTCLOBS
9. FALLBACKANDCLOBS
> 6

DBSControl Compression Group
----------------------------
Enter CompressPermFallbackDBs:
1. UNLESSQBNO
2. NEVER
3. ONLYIFQBYES
> 1

Enter CompressPermFallbackCLOBDBs:
1. UNLESSQBNO
2. NEVER
3. ONLYIFQBYES
> 1

----------------------------------------------------------------------------------------------------------------
|    Query Band    |                     DBSControl Tunables                      ||   Primary   |  Fallback   |
----------------------------------------------------------------------------------------------------------------
| BLOCKCOMPRESSION |  PrimaryDBs | PrimaryCLOBDBs | FallbackDBs | FallbackCLOBDBs || Base | CLOB | Base | CLOB |
----------------------------------------------------------------------------------------------------------------
|         FALLBACK |           * |              * |  UNLESSQBNO |      UNLESSQBNO ||    U |    U |    C |    C |
----------------------------------------------------------------------------------------------------------------
- C - This subtable will load compressed
  U - This subtable will load uncompressed

- The DBSControl Compression group field BlockLevelCompression must be set to ON otherwise
  no compression will take place.

- Fallback secondary indexes follow the compression state of the Fallback base subtable.
  Primary subtable secondary indexes are not eligible for compression.

- The result only applies to Permanent tables. To get the results
  for Global Temporary tables substitute CompressPerm___DBs
  with CompressGlobalTemp___DBs. Where ___ stands for Primary,
  Fallback, PrimaryCLOBs, FallbackCLOBs.