Purpose
Displays the expected initial compression status of data in rows added to empty tables for specified combinations of block-level compression (BLC) settings.
- 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 .
- 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 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.