SHOWFSP - Advanced SQL Engine - Teradata Database

Purpose

The SHOWFSP command displays information about table storage space utilization. SHOWFSP provides the following information on a per-vproc (per-AMP) basis:

• Table name
• Free space percentage (FSP), the amount of unused storage space on the cylinders used by the table
• Number of cylinders currently occupied by tables meeting SHOWFSP filtering criteria
• Number of cylinders that would be occupied by each of these tables after a PACKDISK command

SHOWFSP shows the number of cylinders that will be recovered or consumed if PACKDISK is run to achieve a given percentage of free space on table cylinders.

The scope of SHOWFSP depends on the most recent options set by the SCOPE command, and can include one or more specific tables, one or more specific vprocs, or all tables in the system.

Syntax

{ SHOWFSP | SHOWF }
  [ /Y ]
  [ -A ]
  [ -C cyls ]
  [ -D fsp ]
  [ -M fsp ]
  [ -R fsp ]
  [ -V ]

/Y

Prevents Ferret from displaying a confirmation message.

-A

All tables option displays all tables meeting the criteria of -C, -D, and -M options. If you do not specify any of those options, they will use their default values. The -R option is always automatically overridden and set to the most negative integer known to Ferret.

A table must occupy at least one full cylinder or be the first table on a cylinder to be considered for display.

The -A option causes PACKDISK to considers both of these cases:

• Where PACKDISK would tighten the packing (and thus free up cylinders)
• Where PACKDISK would loosen the packing (and thus consume free cylinders).

If you do not specify the -A option, PACKDISK considers only tables that would tighten the packing.

The number of cylinders affected by the PACKDISK are displayed as a positive or negative value, where positive indicates cylinders are freed and negative indicates cylinders are consumed.

You can qualify tables further by specifying the -A option in conjunction with the other options.

-C cyls

The number of cylinders per AMP that must be exceeded by a table to be considered for display.

The -C cyls option follows this priority order:

• If -C cyls is specified, use it unless cyls is 0, in which case cyls defaults to 1 automatically.
• If -C cyls is not specified, it automatically defaults to 0.

You can further qualify tables by specifying the -C option in conjunction with the other options.

-D fsp

The desired FSP after packing.

The default follows this priority order:

• If -D fsp is specified, use it.
• If -D fspis not specified and a table-level FSP exists, use the table-level FSP.

  Otherwise, use the system-wide default FSP as defined in the DBS Control GDO.

This option allows SHOWFSP to display tables that would free up cylinders on each AMP if you ran PACKDISK with this FSP. If specified in conjunction with the -A option, SHOWFSP also considers tables that consume free cylinders. You can qualify tables further by specifying the -D fspoption in conjunction with the other options.

-M fsp

minimum current FSP a table must exceed to qualify for display.

The default is 0.

This option allows SHOWFSP to ignore tables whose current average FSP is under a certain FSP value.

The average is calculated as a floating point value, and the option is input as an integer. Therefore, often the FSP appears to act as a minimum. For example, an average FSP of 10.1 is greater than a minimum FSP of 10, whereas an average FSP of 10 is not greater than the minimum FSP of 10. Therefore, a table with an average FSP of 10.1 qualifies, whereas a table with an average FSP of 10 would not qualify.

You can qualify tables further by specifying the -M fsp option in conjunction with the other options.

-R cyls

the number of recoverable cylinders that must be exceeded to qualify the table for display.

The default follows this priority order:

• If the -A option is specified, set -R cyls to the most negative integer known to Ferret.
• If -R cyls is specified, use it unless cyls is 0, in which case cyls is defaulted to 1 automatically.
• If -R cyls is not specified, it is defaulted to 0 automatically.

You can qualify tables further by specifying the -R cyls option in conjunction with the other options, except for the -A option, in which case the -R cyls option is handled in the manner noted above.

-V

verbose mode.

The default is Off.

This is for debugging purposes and produces extremely detailed and voluminous output. Teradata does not recommend this option for normal use.

Usage Notes

All arguments and displays are on a per-AMP basis. Without parameters, SHOWFSP displays all tables with recoverable cylinders, assuming the defaults used by PACKDISK. For more details as to how the defaults of each option are determined, see their respective sections above.

SHOWFSP provides an approximation of the number of cylinders that can be freed or consumed by PACKDISK. The actual number of cylinders freed or consumed by a PACKDISK operation may be slightly different.