Purpose
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. For more information, see "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.
- /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)
- /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)
- 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.
- 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.
Teradata Database 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, Teradata Database 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)