15.00 - LIST STATUS proc-id - 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

LIST STATUS proc-id

Purpose  

The LIST STATUS command with the proc-id option specified as mmmm provides additional detailed information about the recovery process of a specific AMP.

Syntax  

 

Syntax element...

Provides...

STATUS

transaction recovery information and AMP recovery information for unavailable AMPs.

  • The displayed row count of a large sized table will be rounded to the nearest thousand.
  • The recovery build records specify rebuild operations separately for each index of a table, which causes a rebuild of both the primary and fallback data for that index. Hence, the displayed sector count is not the total size for the table as derived from other methods.
  • The sector count that is displayed is the sum of the estimated number of sectors for both primary and fallback rows to be copied over from the other AMPs in the cluster or recreated Non Unique Secondary Index (NUSI) indexes for all index subtables of the table which have an OJ build record. This includes the OJ build records of both the current pass and the next pass.
  • proc-id

    additional detailed information about the recovery process of a specific AMP including the list of tables needing to be rebuilt.

    The LIST STATUS proc-id option is only allowed for processors that are in down AMP recovery (offline catchup) or the AMPs listed in the LIST STATUS display. No additional detail information is provided for AMPs that are in online catchup, since no build records exist for these AMPs.

    Usage Notes  

    The following additional information is reported:

  • Status
  • Pass number
  • Current pass count
  • Next pass count
  • A list of tables that need to be rebuilt, which also includes the following information:
  • Total rows
  • Total bytes
  • Rebuild speed in bytes per second
  • Estimated rebuild time
  • Note: All tables that need to be rebuilt will display in the list.

  • The tables for which an OJ build record exists
  • An estimated sector count of that table
  • The LIST STATUS proc-id command is only allowed for processors that are in down AMP recovery (offline catchup), or the AMPs listed in the LIST STATUS display. No additional detail information is provided for AMPs that are in online catchup since no build records exist for these AMPs.

    The following table shows the information contained in the columns under the TABLES TO BE REBUILT report.

     

    Data

    Description

    Row Count

    The number of rows in the table to be rebuilt.

    Status

    Displays up to six possible states where the recovery process may be when LIST STATUS proc-id is executed.

  • Blank
  • Indicates that the table might be or will be rebuilt.

  • MultiLoad Target Table in Apply
  • Indicates that the table is a target table of a MultiLoad job. In this case the OJ build record is discarded since the likelihood is that the AMP will be online before the MultiLoad job finishes. If this is the case, MultiLoad will force a rebuild of the table while the AMP is online. If the MultiLoad job completes before the AMP is online, a new OJ build record is generated, meaning the rebuild will occur in a later pass.

  • Locked
  • Indicates that the table is currently locked by an EXCLUSIVE lock and that a valid sector count cannot be taken. Unless the lock is part of a drop table operation, the table will eventually be rebuilt in the next pass.

  • Non-Fallbacked
  • Indicates that a non-fallback table has been marked for a rebuild operation. Since all non-fallback tables are deleted during rebuild operation, the sector count associated with this table is meaningless.

  • Table Rebuild
  • Indicates that this OJ build record is for a table that is currently being rebuilt. This OJ build record will be discarded.

  • Restore
  • Indicates that a table may have an OJ build record for it, but it is currently the target of a (not necessarily active) restore operation. In this case, the OJ build record is discarded.

    Name

    The name of the table to be rebuilt.

    Note: If the LIST STATUS proc-id command is executed while creating an index on a table that is to be rebuilt, then the time to rebuild the index is not added to the estimated rebuild time for that table.

    Example  

    This example shows a typical down AMP recovery status report, which displays the total rows, total bytes, and the rebuild speed in bytes per second.

    DOWN AMP RECOVERY STATUS AT 12:27:25 99/10/13
     
    AMP to be               Current Pass                Next Pass
    caught up    Pass   OJ           CJ            OJ           CJ
    ---------    ----   -----------  -----------   -----------  -----------
     00001          0             0            0           107       16,531
         - AMP Status: Not in recovery
         - Down
     
    TABLES TO BE REBUILT

    Row Count      Status                            Name
    ------------   -------------------------------   -------------------
         897,598                                     "RESCRIBE"."Rescribe11"
         897,598                                     "RESCRIBE"."Rescribe12"
         897,598                                     "RESCRIBE"."Rescribe13"
         897,598                                     "RESCRIBE"."Rescribe14"
    --------------------------------------------------------------------
       3,590,392   total rows.
       2,582,240KB total bytes.
           7,680KB is the rebuild speed in bytes per second.
    The estimated rebuild time is 5.60 minutes.

    Example  

    This example shows the output returned when the first table rebuild has not completed and an estimate rebuild time cannot be given due to insufficient data.

    TABLES TO BE REBUILT

    Row Count      Status                            Name
    ------------   -------------------------------   -------------------
         161,216                                     "RESCRIBE"."Rescribe10"
         161,216                                     "RESCRIBE"."Rescribe30"
         161,216                                     "RESCRIBE"."Rescribe20"
         161,216                                     "RESCRIBE"."Rescribe12"
         161,216                                     "RESCRIBE"."Rescribe32"
         161,216                                     "RESCRIBE"."Rescribe22"
         161,216                                     "RESCRIBE"."Rescribe14"
    --------------------------------------------------------------------
       1,128,512   total rows.
    There is insufficient data to estimate the rebuild time.