17.10 - SHOWFSP - 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 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 ]

Syntax Elements

/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.

Example: SHOWFSP output

The following example shows the basic output of SHOWFSP.

Wed Mar 01, 2017  20:33:07 : ShowFsp has been started
                           On all AMP vprocs


vproc  0 (0000)  response

There are  4 tables larger than 0 cylinders on amp 0

Database                       Table                          Map                            fsp   Recoverable Current
Name                           Name                           Name                           %     Cylinders   Cylinders
-----------------------------  -----------------------------  -----------------------------  ----  ----------- ---------
DBC                            TVFields                       TD_DATADICTIONARYMAP             85           1*        2*

* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)



vproc  2 (0002)  response

There are  4 tables larger than 0 cylinders on amp 2

Database                       Table                          Map                            fsp   Recoverable Current
Name                           Name                           Name                           %     Cylinders   Cylinders
-----------------------------  -----------------------------  -----------------------------  ----  ----------- ---------
SYSLIB                         FixCurrentSpace                TD_DATADICTIONARYMAP             64           1*        2*

* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)



vproc  3 (0003)  response

There are  4 tables larger than 0 cylinders on amp 3

Database                       Table                          Map                            fsp   Recoverable Current
Name                           Name                           Name                           %     Cylinders   Cylinders
-----------------------------  -----------------------------  -----------------------------  ----  ----------- ---------
SYSLIB                         FixCurrentSpace                TD_DATADICTIONARYMAP             64           1*        2*

* Reported in units of Large Cylinders
  (A Large Cylinder is 6 times the size of a small cylinder)




1 of 4 vprocs responded with no tables fitting the criteria
3 of 4 vprocs responded with the above tables fitting the criteria
ShowFsp has completed

Examples: SHOWFSP command syntax examples

These examples demonstrate how you can use SHOWFSP to answer different questions.

The following SHOWFSP command shows all tables that would free up or consume any amount of cylinders per AMP if executing a PACKDISK command with an FSP of 10%.

showfsp -a -d 10

The following SHOWFSP command shows all tables that would free up more than one cylinder per AMP if executing a PACKDISK with an FSP of 10%.

showfsp -d 10 -r 1

The following SHOWFSP command shows all tables that would free up or consume any amount of cylinders per AMP if executing a PACKDISK command with an FSP of 5%. It considers only tables which currently occupy more than 10 cylinders per AMP and have a current FSP greater than 5%.

showfsp -a -d 5 -c 10 -m 5

The following SHOWFSP command shows all tables that would free up more than three cylinders per AMP if executing a PACKDISK command with an FSP of 7%. It considers only tables with a current FSP of more than 10%, and those that currently occupy more than three cylinders per AMP.

showfsp -d 7 -c 3 -m 10 -r 3