16.10 - Specifying a WHERE Clause - Teradata Database

Teradata Database Support Utilities

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Configuration
featnum
B035-1180-161K

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
  • DUMMYSNAPMI
  • 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
  • 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).
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: Using the WHERE clause to restrict Filer actions

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: Using the WHERE clause to delete a TJ END record

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: Using the WHERE clause to find open transactions on a specified host

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

wal /L where tjhost=7 trxstate=open

Example: Using the WHERE clause to find WAL and TJ insert records

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

wal /l where action=insert ; tjkind=newrow