Purpose
Lists subtables that have block-level compressed data blocks.
Syntax
{ SHOWCOMPRESS | SHOWCOMP } [ /S | /L ]
The online help lists the display options (/S and /L) as /dispopt.
- /S
- For compressed or compressible tables, displays table ID, table name, and subtable ID.
- /L
- Displays compression state, compression algorithm name, compression level, exact compression ratio, exact percentage of blocks compressed, exact percentage of data compressed, exact number of used GB, table ID, table name, and subtable ID. Uncompressed subtable information is displayed only when the table has compressed or compressible sub-tables.This display option requires longer to display than the /S option because all of the data blocks of the tables in scope must be examined.
Usage Notes
Block-level compression (BLC) compresses table data at the File System level to save storage space. SHOWCOMPRESS lists all tables that have data blocks compressed using BLC.
SHOWCOMPRESS shows compression data only for table types eligible for BLC, even if the Ferret scope is set to encompass a wider set of table types.
BLC can be controlled at many levels of Teradata Database:
- The Compression and Storage field groups in the DBS Control utility determine whether Teradata Database applies BLC automatically to different categories of tables. You can use these settings to have data compressed automatically when the tables are loaded or updated, to have some categories of tables never be compressed, or to tell Teradata Database to compress only infrequently accessed ("cold") data using temperature-based block-level compression (TBBLC).
- You can manually set BLC for individual tables using the BLOCKCOMPRESSION option of the CREATE TABLE and ALTER TABLE SQL statements.
- You can use the BLOCKCOMPRESSION query band to override other BLC settings for an entire Teradata session or as part of a data load script.
- Use the Ferret COMPRESS and UNCOMPRESS commands to compress and uncompress tables on-demand.
SHOWCOMPRESS shows table-level BLC compression information. For block-level BLC compression information, use the SHOWBLOCKS command.
Example: SHOWCOMPRESS /L output
In the following example:
- Table tab1 has the primary and fallback subtables compressed with IPPZLIB algorithm.
- Table tab2 has the primary subtable compressed with the IPPZLIB algorithm, and the fallback subtable compressed with the ZLIB algorithm.
- Table tab3 has primary and fallback subtables both compressed with the IPPZLIB algorithm. They are compressed to different compression levels (degrees).
Ferret ==> showcompress /L SHOWCOMPRESS has been started on all AMP vprocs. +-----------+--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | Table ID | Tablename (BLC option, BLC ALG, BLC LEVEL) | Mapname | Compression | Compres | Compres| Exact | Exact | Exact | Exact | | | | | state | -sion | -sion | compression| % of | % of | used GB | | | | | | algorithm| level | ratio | blocks | data | | +-----------+--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0 2930| EXACT_COMPSTATS.tab1 (MANUAL, DEFAULT, | TD_MAP1 | | | | DEFAULT) | | | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | | | | COMPRESSED | IPPZLIB | 6 | 98.70% | 98.58% | 90.42% | 0.001 | | | | | DISQUALIFIED | N/A | N/A | 0.00% | 0.57% | 4.60% | 0.000 | | | | | UNCOMPRESSED | N/A | N/A | 0.00% | 0.85% | 4.98% | 0.000 | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1024| | | COMPRESSED | IPPZLIB | 6 | 98.96% | 100.00% | 100.00% | 0.001 | | 1028| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1032| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1792| | | COMPRESSED | IPPZLIB | 6 | 77.84% | 100.00% | 100.00% | 0.000 | | 1794| | | COMPRESSED | IPPZLIB | 6 | 74.26% | 100.00% | 100.00% | 0.000 | | 2048| | | COMPRESSED | IPPZLIB | 6 | 98.97% | 100.00% | 100.00% | 0.001 | | 2052| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2056| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2816| | | COMPRESSED | IPPZLIB | 6 | 77.84% | 100.00% | 100.00% | 0.000 | | 2818| | | COMPRESSED | IPPZLIB | 6 | 74.26% | 100.00% | 100.00% | 0.000 | +-----------+--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0 2932| EXACT_COMPSTATS.tab2 (MANUAL, DEFAULT, | TD_MAP1 | | | | DEFAULT) | | | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | | | | COMPRESSED | ZLIB | 1 | 98.67% | 49.25% | 45.37% | 0.001 | | | | | COMPRESSED | IPPZLIB | 6 | 98.69% | 49.32% | 45.09% | 0.001 | | | | | DISQUALIFIED | N/A | N/A | 0.00% | 0.57% | 4.59% | 0.000 | | | | | UNCOMPRESSED | N/A | N/A | 0.00% | 0.85% | 4.96% | 0.000 | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1024| | | COMPRESSED | IPPZLIB | 6 | 98.96% | 100.00% | 100.00% | 0.001 | | 1028| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1032| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1792| | | COMPRESSED | IPPZLIB | 6 | 77.35% | 100.00% | 100.00% | 0.000 | | 1794| | | COMPRESSED | IPPZLIB | 6 | 73.69% | 100.00% | 100.00% | 0.000 | | 2048| | | COMPRESSED | ZLIB | 1 | 98.96% | 100.00% | 100.00% | 0.001 | | 2052| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2056| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2816| | | COMPRESSED | ZLIB | 1 | 76.35% | 100.00% | 100.00% | 0.000 | | 2818| | | COMPRESSED | ZLIB | 1 | 71.92% | 100.00% | 100.00% | 0.000 | +-----------+--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0 2934| EXACT_COMPSTATS.tab3 (MANUAL, DEFAULT, | TD_MAP1 | | | | DEFAULT) | | | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | | | | COMPRESSED | IPPZLIB | 1-6 | 98.97% | 40.00% | 47.73% | 0.000 | | | | | DISQUALIFIED | N/A | N/A | 0.00% | 40.00% | 29.55% | 0.000 | | | | | UNCOMPRESSED | N/A | N/A | 0.00% | 20.00% | 22.73% | 0.000 | | +--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+ | 0| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1024| | | COMPRESSED | IPPZLIB | 6 | 98.97% | 100.00% | 100.00% | 0.000 | | 1028| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1032| | | UNCOMPRESSED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1792| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 1794| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2048| | | COMPRESSED | IPPZLIB | 1 | 98.97% | 100.00% | 100.00% | 0.000 | | 2052| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2056| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2816| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | | 2818| | | DISQUALIFIED | N/A | N/A | 0.00% | 100.00% | 100.00% | 0.000 | +-----------+--------------------------------------------+---------+----------------+----------+--------+------------+---------+---------+-------------+
Related Topics
| For more information on… | See… |
|---|---|
| Block-level compression | Teradata Vantage™ - Database Design, B035-1094. |
| BLOCKCOMPRESSION table attribute, CREATE TABLE, ALTER TABLE statements | Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. |
| Query bands for BLC and TBBLC | Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 . |
| Compression and Storage field groups in DBS Control | DBS Control (dbscontrol). |