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 SQL Functions, Operators, Expressions, and Predicates.
Syntax
The online help lists the display options (/S, /M, /L) as /dispopt.
- /S
- For each primary data subtable, display the following:
- /M
- Same as the /S option, but display the table name, map that the table uses for data distribution, and 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:
- COMPRESSTLA=, AUTOTEMP, DEFAULT, MANUAL, ALWAYS, or NEVER
- SHOWBLOCKS output should be filtered to show information only for tables that have their BLOCKCOMPRESSION set to the specified value, AUTOTEMP, DEFAULT, MANUAL, ALWAYS, or NEVER. The BLOCKCOMPRESSION value can be set when the table is created or altered.
The Ferret ABORT command can be used to halt this operation during command execution.
Usage Notes
The output of the long display is basically one line for every size data block from every subtable of every table in the scope.
The output can be quite 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 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)
"Compression Status" : C = Fully Compressed
U = Fully Uncompressed
N = Not Compressible