SHOWBLOCKS | Ferret Utility | Advanced SQL Engine | Teradata Vantage - 17.10 - SHOWBLOCKS - 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)

The SHOWBLOCKS command displays statistics about data block size, number of rows per data block, and information about the compression status of data blocks and tables for all the tables defined by the SCOPE command. SHOWBLOCKS can also display WAL log statistics.

The CreateFsysInfoTable and PopulateFsysInfoTable macros provide a way to generate SHOWBLOCKS-like file system information to an SQL table. See also the information about file system information macros and functions in Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

Syntax

{ SHOWBLOCKS | SHOWB }
  [ /S | /M | /L ]
  [ COMPRESSTLA = { AUTOTEMP | DEFAULT | MANUAL | ALWAYS | NEVER } ]
  [ PERVPROC ]
The online help lists the display options (/S, /M, /L) as /dispopt.

Syntax Elements

/S
For each primary data subtable, display the following:
  • A histogram of block sizes
  • The minimum, average, and maximum block size per subtable
  • Block compression information (status, estimated compression ratio, estimated uncompressed)
This is the default display.
/M
Display what the /S option shows, and additionally display the table name, indicate if it is a foreign table, show the name of the map the table uses for data distribution, and display statistics for all subtables. For each subtable, display the BLOCKCOMPRESSION value set when the table was created or altered.
/L
For each subtable, for each block size, display the following:
  • Table name
  • Indicator if table is a foreign table
  • Map that the table uses for data distribution
  • Number of blocks
  • The minimum, average, and maximum number of rows per data block size
  • Block compression information (status, estimated compression ratio, estimated percent uncompressed)
Displays the statistics for all subtables.
COMPRESSTLA
SHOWBLOCKS output is filtered to show only information for tables with BLOCKCOMPRESSION set to the specified value, one of the following: AUTOTEMP, DEFAULT, MANUAL, ALWAYS, or NEVER.
BLOCKCOMPRESSION can be set for a table in a CREATE TABLE or ALTER TABLE statement.
PERVPROC
Displays data block statistics per AMP vproc within the current scope.
The CreateFsysInfoTable and PopulateFsysInfoTable macros do not support the PERVPROC option.

The Ferret ABORT command can be used to halt this operation during command execution.

Usage Notes

If the scope is set to both vproc and map, SHOWBLOCKS displays information for those specified vprocs that are in the specified map.

The output of the long display is one line for every size data block from every subtable of every table in the scope.

The output can be lengthy; therefore, consider using the OUTPUT command to redirect the output to a file.

This command can be used with the DATABLOCKSIZE option of the CREATE TABLE and ALTER TABLE SQL statements to determine the best data block size for tables based on performance requirements.

Vantage can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows ("region") in a data or index subtable. In these cases, Vantage marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart. If SHOWBLOCKS encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

Although disk space allocated for TJ and WAL records is charged against table 0 26, no actual TJ or WAL records are found in the subtables of this table. Instead, these records are in the WAL log. The only row that exists in any subtable of table 0, 26 is the table header in subtable 0.

Example: SHOWBLOCKS short display

The following is a portion of the output of the showblocks command using the /s short display option:

+--------+------+----------+------------+--------------------------------------------------------------------------+---------------+------+---------+
|        |Compr-|Estimated |Estimated % |                    Distribution of data block sizes                      |  Data block   |Total | Total   |
|Table ID|ession| Compres- | of Blocks  |                     (by range of number of sectors)                      |size statistics|Number| Number  |
|        |Status|  sion    |   Un-      +----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+   (sectors)   |  of  |  of     |
|        |      |  Ratio   | compressed | 1- | 2- | 4- | 8- | 16-| 32-| 48-| 64-| 80-| 96-|112-|128-|160-|192-|224-|-----+----+----+ Data |Cylinders|
|        |      |          |            | 1  | 3  | 7  |15  | 31 | 47 | 63 | 79 | 95 |111 |127 |159 |191 |223 |255 | Min | Avg| Max|Blocks|         |
+--------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+-----+----+----+------+---------+
| 0   256|   N  |          |            |    |    |    | 88%| 12%|    |    |    |    |    |    |    |    |    |    |   9 |  13|  19|     8|       8*|
| 0   265|   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |    |    |    |   1 |   1|   1|     3|       3*|
| 0   266|   N  |          |            |    |100%|    |    |    |    |    |    |    |    |    |    |    |    |    |   2 |   2|   3|     5|       5*|
| 0  1804|   C  |   50%    |    20%     |    | 64%|  2%| 34%|    |    |    |    |    |    |    |    |    |    |    |   2 |   6|  15|  4974|      12*|
| 0  1805|  PC  |   88.14% |    81.69%  |    |    |    | 18%|    |    |    |    |    | 26%| 56%|    |    |    |    |   4 |  94| 121|  4008|      20*|
| 0  2087|   U  |          |            |    |    |  1%| 99%|    |    |    |    |    |    |    |    |    |    |    |   1 |  13|  15|   689|       8*|
| WAL LOG|   N  |          |            |  7%| 83%|  3%|  1%|  5%|  1%|    |    |    |    |    |    |    |    |    |   1 |   4| 255|  2475|      25 |
+--------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+-----+----+----+------+---------+
* Reported in units of Large Cylinders
(A Large Cylinder is 6 times the size of a small cylinder)
"Compression Status" :  C = Fully Compressed
                       PC = Partially Compressed
                        U = Fully Uncompressed
                        N = Not Compressible

Example: SHOWBLOCKS medium display

The following is a portion of the output of the showblocks command using the /m medium display option:

Showblocks has been started on all AMP vprocs in the SCOPE.
Type 'ABORT' to stop the command before completion
+------------+------+----------+------------+-----------------------------------------------------------------------------+-----------------+------------+-----------+
|            |Compr-|Estimated |Estimated % |                      Distribution of data block sizes                       |   Data block    |   Total    |   Total   |
|            |ession| Compres- | of Blocks  |                       (by range of number of sectors)                       | size statistics |   Number   |   Number  |
|  Table ID  |Status|  sion    |   Un-      +----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+    (sectors)    |     of     |     of    |
|            |      |  Ratio   | compressed |  1-|  9-| 25-| 65-|121-|169-|217-|257-|361-|457-|513-|761-|1025-|1305-|1633-|-----+-----+-----+    Data    | Cylinders |
|            |      |          |            |  8 | 24 | 64 |120 |168 |216 |256 |360 |456 |512 |760 |1024|1304 |1632 |2048 | Min | Avg | Max |   Blocks   |           |
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0     0 | DBC.FIRSTPERMTABLE (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                   |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0     7 | DBC.RCConfiguration (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                  |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    25 | DBC.Global (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         1  |         1*|
|       2048 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   1 |         1  |         1*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    26 | DBC.TransientJournal (DEFAULT)(Map: TD_GLOBALMAP)                                                                                         |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    27 | DBC.Owners (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   2 |         3  |         3*|
|       2048 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   2 |         3  |         3*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    30 | DBC.ErrorMsgs (DEFAULT)(Map: TD_DATADICTIONARYMAP)                                                                                        |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
|       1024 |   N  |          |            |    |    |    |    |    |    |100%|    |    |    |    |    |     |     |     | 239 | 244 | 248 |         4  |         4*|
|       2048 |   N  |          |            |    |    |    |    |    |    |100%|    |    |    |    |    |     |     |     | 239 | 244 | 248 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    32 | DBC.ChangedRowJournal (DEFAULT)(Map: TD_GLOBALMAP)                                                                                        |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0    36 | DBC.SavedTransactionStatusTable (DEFAULT)(Map: TD_GLOBALMAP)                                                                              |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   2 |   2 |   2 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    WAL LOG | WAL LOG.WAL LOG                                                                                                                           |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    WAL LOG |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   1 |   1 |   3 |        43  |         7*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)

 "Compression Status" :  C = Fully Compressed
                         U = Fully Uncompressed
                         N = Not Compressible

Example: SHOWBLOCKS medium display scoped to a foreign table

For more information about foreign tables, see CREATE FOREIGN TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Ferret  ==>
> scope table 0 3123 0

The SCOPE has been set 
 
Ferret  ==>
> showblocks /m


+------------+------+----------+------------+-----------------------------------------------------------------------------+-----------------+------------+-----------+
|            |Compr-|Estimated |Estimated % |                      Distribution of data block sizes                       |   Data block    |   Total    |   Total   |
|            |ession| Compres- | of Blocks  |                       (by range of number of sectors)                       | size statistics |   Number   |   Number  |
|  Table ID  |Status|  sion    |   Un-      +----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+    (sectors)    |     of     |     of    |
|            |      |  Ratio   | compressed |  1-|  9-| 25-| 65-|121-|169-|217-|257-|361-|457-|513-|761-|1025-|1305-|1633-|-----+-----+-----+    Data    | Cylinders |
|            |      |          |            |  8 | 24 | 64 |120 |168 |216 |256 |360 |456 |512 |760 |1024|1304 |1632 |2048 | Min | Avg | Max |   Blocks   |           |
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|    0  3123 | EXTFSDB1.newsensordata (FOREIGN TABLE)(Map: TD_MAP1)                                                                                      |           |
|            +------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
|          0 |   N  |          |            |100%|    |    |    |    |    |    |    |    |    |    |    |     |     |     |   3 |   3 |   3 |         4  |         4*|
+------------+------+----------+------------+----+----+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+------------+-----------+
* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)

 "Compression Status" :  C = Fully Compressed
                         U = Fully Uncompressed
                         N = Not Compressible

Example: SHOWBLOCKS long display

The following is a portion of the output of the showblocks command using the /l long output option.

+------------+-------+-----------+--------------+-----------+--------------+------------+-----------------------+-----------+
|            | Compr-| Estimated | Estimated %  |           |              |            |    Statistics of      |   Total   |
|            |ession | Compres-  |  of Blocks   |   Data    |  Number of   | Cumulative |    Number of Rows     |   Number  |
|            |Status |  sion     |Un-compressed |   Block   | Data Blocks  | Percent of |    Per Data Block     |     of    |
|  Table ID  |       |  Ratio    |              |   size    | of indicated |   Total    +-------+-------+-------+           |
|            |       |           |              | (sectors) |    size      |Data Blocks |  Min  |  Avg  |  Max  | Cylinders |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0     0 | DBC.FIRSTPERMTABLE(Map: TD_DATADICTIONARYMAP)                                                    |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       1   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0     7 | DBC.RCConfiguration(Map: TD_DATADICTIONARYMAP)                                                   |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    25 | DBC.Global(Map: TD_DATADICTIONARYMAP)                                                            |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       1024 |   N   |           |              |       1   |          1   |  100.00%   |     1 |     1 |     1 |         1*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       2048 |   N   |           |              |       1   |          1   |  100.00%   |     1 |     1 |     1 |         1*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    26 | DBC.TransientJournal(Map: TD_GLOBALMAP)                                                          |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    0    27 | DBC.Owners(Map: TD_DATADICTIONARYMAP)                                                            |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       2   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       1024 |   N   |           |              |       1   |          2   |   66.67%   |     1 |     2 |     2 |         3*|
|            |   N   |           |              |       2   |          1   |  100.00%   |    34 |    34 |    34 |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|       2048 |   N   |           |              |       1   |          2   |   66.67%   |     1 |     2 |     2 |         3*|
|            |   N   |           |              |       2   |          1   |  100.00%   |    34 |    34 |    34 |           |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|49152     0 | DBC.FIRSTSPOOLTABLE(Map: TD_MAP1)                                                                |           |
|------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|          0 |   N   |           |              |       1   |          4   |  100.00%   |     1 |     1 |     1 |         4*|
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    WAL LOG | WAL LOG.WAL LOG                                                                                  |           |
|            +-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
|    WAL LOG |   N   |           |              |       1   |         48   |   85.71%   |     0 |     2 |     4 |         8*|
|            |   N   |           |              |       2   |          1   |   87.50%   |     0 |     6 |     6 |           |
|            |   N   |           |              |       3   |          6   |   98.21%   |     0 |     7 |     9 |           |
|            |   N   |           |              |       5   |          1   |  100.00%   |     0 |     1 |     1 |           |
+------------+-------+-----------+--------------+-----------+--------------+------------+-------+-------+-------+-----------+
* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)