15.00 - SHOWFSP - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

SHOWFSP

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  

     

    Syntax Element

    Description

    /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

    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 fsp is 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.
  • Note: 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 fsp option in conjunction with the other options.

    -M fsp

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

    The default is 0.

    Note: 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.

    Examples  

    The following example shows the basic output of SHOWFSP.

    Ferret  ==>
    showfsp
     
    Tue Dec 08, 2009  16:10:04 : ShowFsp will be started
                               On All AMP vprocs 
    Tue Dec 08, 2009  16:10:04 : to find all the tables specified greater than 0 cylinders
    Tue Dec 08, 2009  16:10:04 : which could free up more than 0 cylinders
    Tue Dec 08, 2009  16:10:04 : only tables with a current FSP greater than  0%
    Tue Dec 08, 2009  16:10:04 : will be considered
            Do you wish to continue based upon this scope?? (Y/N)
    y
     
    Tue Dec 08, 2009  16:10:05 : ShowFsp has been started
                               On All AMP vprocs 
     
     
    vproc  0 (0000)  response
     
    There are  103 tables larger than 0 cylinders on amp 0
     
           Database                         Table                   fsp  Recoverable Current
           Name                             Name                    %    Cylinders   Cylinders
    ------------------------------ ------------------------------  ----- --------- -------
    TY100INS                       emp2                             48          1*         4*
    TY100INS                       emp16                            34          1*         3*
    TY100INS                       emp21                            39          1*         3*
    TY100INS                       emp31                            39          1*         3*
    TY100INS                       emp36                            42          1*         4*
     
    * Reported in units of Large Cylinders
      (A Large Cylinder is 6 times the size of a small cylinder)
     
     
     
    vproc  1 (0001)  response
     
    There are  102 tables larger than 0 cylinders on amp 1
     
           Database                         Table                   fsp  Recoverable Current
           Name                             Name                    %    Cylinders   Cylinders
    ------------------------------ ------------------------------  ----- --------- -------
    TY100INS                       emp15                            36          1*         3*
    TY100INS                       emp17                            35          1*         3*
    TY100INS                       emp18                            40          1*         4*
    TY100INS                       emp28                            42          1*         3*
    TY100INS                       emp29                            37          1*         3*
    TY100INS                       emp32                            34          1*         3*
     
    * Reported in units of Large Cylinders
      (A Large Cylinder is 6 times the size of a small cylinder)

     

    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