LIST STATUS proc-id - Advanced SQL Engine - Teradata Database

Database Utilities
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
xha1591998860283.ditamap
dita:ditavalPath
xha1591998860283.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

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

LIST STATUS [ proc-id ] ;

Syntax Elements

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. This causes a rebuild of both the primary data and fallback data for that index, so the displayed sector count will differ from the total size for the table as derived using 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
      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.
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: Down AMP recovery status report for specified AMP

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.