SHOWBLOCKS | Ferret Utility | Analytics Database (SQL Engine) | Teradata Vantage - SHOWBLOCKS - Analytics Database - Teradata Vantage

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.

Syntax

{ SHOWBLOCKS | SHOWB }
  [ /S | /M | /L ]
  [ COMPRESSTLA = { AUTOTEMP | DEFAULT | MANUAL | ALWAYS | NEVER } ]
  [ PERVPROC ]

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, it 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 failure that requires a system restart. If SHOWBLOCKS encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.