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

BLCINFO

Purpose  

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

Note: 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  

     

    Syntax Element

    Description

    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 SQL Data Definition Language.

    DEFAULTTABLEMODE
    DEFAULT

    Specifies a valid setting of DefaultTableMode in DBS Control. For more information on DefaultTableMode, see the DBS Control chapter in Utilities.

    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.

    Note: 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  

    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.