17.10 - SHOWCOMPRESS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

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.

Syntax Elements

/S
For compressed or compressible tables, displays table ID, table name, and subtable ID.
This is the default display.
/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.
The output shows blocks that are compressed, uncompressed, and "disqualified". Disqualified blocks are those that did not qualify for compression for one of the following reasons:
  • Some data blocks fall below BLC compression threshold levels, which are set in the Compression group of DBS Control fields. For more information about these compression settings, see DBS Control (dbscontrol).
  • Some data blocks are too small to be compressed.
  • Some special subtables are never compressed.

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 Vantage:
  • The Compression and Storage field groups in the DBS Control utility determine whether Vantage 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 Vantage 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 Information

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).