15.00 - Specifying a WHERE clause - Teradata Database

Teradata Database Support Utilities

prodname
Teradata Database
vrm_release
15.00
category
Configuration
featnum
B035-1180-015K

Specifying a WHERE clause

The WHERE clause can be supplied to the DISPLAY, WAL, and WREC commands to restrict selection based on row/record type or content. The constraint is in addition to the ridrange, ordinalrange and wlsnrange constraints. For more information about these constraints, see the description of the DISPLAY, WAL, or WREC command. Most of the constraints in the WHERE clause apply to WAL log records only, and not to physical rows in normal tables.

Syntax

 

Syntax Element

Description

kind

Types of WAL log records to display. The following are the valid values.

  • ALLOCSECT
  • BACKOUTSECT
  • BEFORECHECKSUM
  • CHECKPOINT
  • DBD
  • DIAGNOSTIC
  • FREECYL
  • FREELINEAGE
  • FSP
  • HDRMIGINFO
  • HDRONLY
  • NEWDB
  • NONTJ
  • ROW
  • ROWCOPY
  • ROWEXISTCOMPDB
  • ROWNEWSECT
  • SRD
  • TJRECORD
  • UNFSE
  • action

    Indicates the action to be redone in certain NONTJ records. The following are the valid values.

  • INSERT
  • UPDATE
  • DELETE
  • flags

    The following are the derived flags values that are valid in the WHERE clause.

  • NTA
  • NTABEGIN
  • NTAEND
  • ntastate

    The following are the valid values.

  • OPEN
  • CLOSED
  • tjkind

    Types of Transient Journal (TJ) records to display. The following are the valid values.

  • NILL
  • TIP
  • HSTSTRT
  • BEG
  • END
  • CLRDMP
  • FALL
  • DELTBL
  • BEFROW
  • DELROW
  • NEWROW
  • DELIDX
  • NEWIDX
  • MODTBL
  • DBLOK
  • PJTRNBEG
  • PJROW
  • DELPJ
  • DELALL
  • INSETB
  • TIPLOK
  • TIPAPJ
  • TBLCACTION
  • SESSEP
  • 2PCVOTE
  • 2PCABRT
  • 2PCSTAT
  • IDT
  • REQABT
  • NEWTMPTBL
  • BEGSAVEPT
  • ENDSAVEPT
  • SAVEPTABT
  • NEWLOB
  • DELETELOB
  • DELPART
  • tjkind (continued)

  • INSPART
  • QTNEWROW
  • QTDELROW
  • FATMODTBL
  • REPACT
  • MARKER
  • NUSIDEL
  • NUSIINS
  • BEFTHROW
  • BEFTHROWFLOAD
  • CRESNAPSHOT
  • REMSNAPSHOT
  • RELCHN
  • NESTEDDONE
  • NEWOLJ
  • TJTABLE tid

    tid of the table referenced inside a TJ record. Some TJ records contain the subtable identifier (tid) and row identifier for the applicable physical row (the row to which the TJ record will be applied during rollback). This is displayed as a 4-byte table number and 2-byte typeandindex. However, as a TJTABLE argument, this is supplied in the normal Filer tid format, using two numbers for the halves of the table number, and a third number for the typeandindex. For more information on tid formatting, see “Subtable Identifiers (tids)” on page 79.

    TJROW rowid

    rowid of the row referenced inside a TJ record. This is the RowID of the database row that the TJ record applies to. For example, this identifies the physical row whose update caused the TJ record to be created.

    TJTRANS procnum transuniq

    TJ record transaction identification. procnum and transuniq are both numbers.

    trxstate

    For the TJ records, display only the TJ records for given transaction states. The following are the valid values.

  • OPEN
  • ABORTED
  • CLOSED
  • TJHOST number

    TJ record host number.

    TJSESSION number

    TJ record session number.

    TJREQUEST number

    For the TJ records, display only the TJ records for these requests.

    TJRCVSESS number

    For the TJ records, display only the TJ records for these recovery sessions.

    Usage Notes

    You can enter Keyword arguments in any order. Separate Keyword arguments (some of which contain lists) with a space. Within a list, separate items with a comma.

    KIND=TJRECORD implies a WAL type of TJ. The kind value NONTJ does not represent an actual WAL log record kind, but is provided as a convenience.

    When specifying the KIND keyword, a mix of NONTJ and TJRECORD kinds is allowed. In qualifying records for display, if the record is a NONTJ kind, the NONTJ selectors (kind, action, flags, and ntastate) are used. If the record is a TJRECORD kind, then the TJ selectors (tjkind, tid, etc.) are used.

    The NTASTATE, TRXSTATE, and TJRCVSESS arguments require special processing because these values are not recorded in the log records. The log is read backwards to determine transaction states or to find recovery session boundaries. It is then read forwards to display selected log records, which can be further filtered by other arguments.

    The WHERE clause provides selection from the WAL log in a single, integrated display stream, displaying both NONTJ records and TJ records in time sequence when used with Filer commands such as WAL and WREC.

    Example  

    The following example selects TJ BEG and END records for host 7, and checkpoint records.

    wal /m where kind=checkpoint ; tjkind=beg,end ; tjhost=7

    Example  

    The following example finds a TJ END record and deletes it. The output is not shown, but the desired TJ END record with WLSN 43e72 is assumed to be found amongst the TJ END records returned.

    wal /L where tjkind=end        // display TJ END records
    wrec /L 43e72                  // set context to record found
    delete                         // request delete of selected record
    y                              // confirm delete request

    Example  

    The following example finds all TJ records for open transactions from host 7.

    wal /L where tjhost=7 trxstate=open

    Example  

    The following example displays WAL insert records and TJ insert records.

    wal /l where action=insert and tjkind=newrow
    s