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.
- 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.
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.
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
ONLINE TRANSACTION RECOVERY JOURNAL COUNTS at 13:49:28 98/02/06 Recovery AMP Session Count W/Count ------- ---------- ------- 1 765 12 2 38,432 5 3 1,388 20
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.
|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:
|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 Database 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 Database 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).
|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).
|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.
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