LIST STATUS - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage
The LIST STATUS command generates two reports:
  • Online Transaction Recovery Journal Counts
  • Down/Catchup AMP Recovery Status

The first reports transaction recovery information, and the second reports AMP recovery information for unavailable AMPs.



Syntax Elements

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.

Online Transaction Recovery Journal Counts

The Online Transaction Recovery Journal Counts allows the user to monitor transaction recovery processing. It displays information on the transient journal and transactions that were in progress during transaction recovery processing.

Report Information

This report displays a list of all active recovery sessions and the maximum number of transaction journal rows remaining to be processed for the AMP that has this maximum count.

Since all AMPs must complete processing of a given recovery session before the processing of the next session begins, this information is sufficient to calculate the worst-case count of transaction journal entries to be scanned. You can use this count as a rough guide to recovery processing time.

However, because this is only a rough guide, you must also take into consideration the following variables when estimating the time involved in the recovery process:
  • The amount of work required by recovery to process a given transaction journal row can vary by orders of magnitude; that is, some rows can be processed in a fraction of a second, whereas others can take hours to process.
  • The transaction journal may contain many rows which require no recovery processing.

The online transaction recovery journal counts are updated by each AMP every time a checkpoint is taken.

Thus, every time an AMP performs checkpoint, its online transaction recovery journal count is decreased by 1000, and a later LIST STATUS command may display different results.

If no recovery sessions are active, the report title is printed without any contents.

Example: Online transaction recovery journal counts report

Recovery             AMP
Session      Count   W/Count
-------  ----------  -------
      1         765       12
      2      38,432        5
      3       1,388       20
A new recovery session is created for each restart that is not a COLDWAIT restart.

Down/Catchup AMP Recovery Status

The example report contains three sets of data lines (together with a column header) for each AMP participating in the down AMP recovery process. The first data line is explained by the column headers. The second and third data lines indicate the status.

The following table describes the Header and first-line data.

Data Description
AMP to be caught up Indicates the AMP number to be caught up.

In addition to the processor number, this column might contain one of the following notations:

  • * (asterisk)

    Indicates that this AMP may be placed in online catchup if the Teradata system restarts. If the displayed information is no longer valid by the time the Teradata system restarts, the AMP might remain in offline catchup.

  • [1]

    Indicates that one or more host utility locks are held in the cluster of the recovering AMP and that the data on the AMP cannot be recovered because there is a conflict between the host utility lock and the recovery locking requirements.

  • [2]

    Indicates that one or more 2 Phase Commit (2PC) in-doubt sessions exist within the cluster of the recovering AMP.

Pass Indicates the number of the recovery pass. At the beginning of a recovery pass all rows in the OJ and CJ are extracted. This is the total number of rows to be processed during that pass. If additional changes are sent to the AMP during the processing of the current pass, they are queued up for the next pass. When the processing of rows of the current pass is complete, the pass number is incremented and the rows for the next pass are extracted from the OJ and CJ and processed.
Current Pass The OJ column contains the number of rows in the ordered Teradata system change journal to be processed during the current recovery pass. The CJ column contains the number of rows in the changed row journal to be processed during the current recovery pass.
Next Pass The OJ column contains the number of rows in the ordered Teradata system change journal to be processed during the next recovery pass. The CJ column contains the number of rows in the changed row journal to be processed during the next recovery pass.

If there are transactions that are updating user tables, these counts will go up as a result of those updated transactions. Subsequent displays could show an increase in the count.

The following table describes the second data line (AMP Status).

Data Description
Online Catchup This AMP is online during recovery processing and accepts new work. Locks are applied against all objects that need to be updated before new work is accepted. In this status, the OJ count is usually zero and no new OJ or CJ entries are created.
Offline Catchup This AMP is logically (not physically) offline during recovery processing. No new work is accepted by this AMP. Read locks are applied on the online AMPs in the cluster only against the specific data of an object that has to be updated.
Not In Recovery This AMP is not running down AMP recovery; it is physically offline.

The following table describes the third data line (AMP Recovery Status Line).

Data Description
Transaction Recovery: ZZZ,ZZZ,ZZ9 TJ Rows Specifies the number of rows in the transaction recovery journal to be processed in this recovery pass. The transaction recovery journal contains the before images of all change objects affected by every transaction. Transaction recovery is the first step of the first recovery pass only. Each Z represents a digit and 9 represents any non-zero value.
Rebuilding Table [DBase.Table]: Z9% complete in this pass Specifies the name of the table being rebuilt and the percentage of completion. Tables are rebuilt as a result of DDL changes (that is, drop or add a column) to a table, or a MultiLoad operation has affected a table while an AMP was down.
Change Row Recovery: Z9% complete in this pass This is caused by DML changes to the tables. This step of recovery is entered when DML (for example insert update, or delete) changes have been made to tables in a down AMP. While an AMP is down, the other AMPs in the cluster modify their fallback rows for the down AMP, and also modify their own rows for which the down AMP has fallback responsibility.
Between Passes The state of between passes arises since there is a five minute pause between passes. Primarily, this is intended to allow current operations, including any new work creating CJ and OJ entries, to finish and release their locks, so that recovery will not compete with online operations.
Miscellaneous OJ Processing Indicates that time is spent processing the various short running OJ entries, for example, releasing host utility locks.
Down Indicates that this AMP is not involved in the recovery process. However, statistics are maintained to indicate how much accumulated work remains to be recovered before the AMP can become operational.
Not currently executing recovery Indicates that the AMP has not yet reached the point of starting Transaction Recovery or extracting OJ logs. The AMP is neither at the very early stage in recovery or has not yet started it. (The AMP might be down.)

Example: Down AMP recovery status report

The Down AMP Recovery Status report generates the information shown in the following screen example.

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