16.10 - SHOWCOMPRESS - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-26
dita:mapPath
zll1480972831047.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval

Purpose

Lists subtables that have block-level compressed data blocks.

Syntax



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.
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 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 Database Design
BLOCKCOMPRESSION table attribute, CREATE TABLE, ALTER TABLE statements SQL Data Definition Language
Query bands for BLC and TBBLC SQL Data Definition Language
Compression and Storage field groups in DBS Control DBS Control (dbscontrol)