TID Function

Used in a select list, the TID function returns the database address of a row (or rows for BULK SELECT) of a table or an updatable view. Used in a WHERE clause, the TID function takes a row address as input and allows direct access to a single row of a table or an updatable view.

Scope

SQL Data Manipulation Statements

SQL Syntax

  TID([ [Owner.]TableName
        [Owner.]ViewName
        CorrelationName   ])

Parameters

TID

is an 8 byte value representing the database address of a row of a table or an updatable view. A TID contains these elements:

Table 8-2 SQLTID Data Internal Format

Content	Byte Range
Always = 0	1 thru 2
File Number	3 thru 4
Page Number	5 thru 7
Slot	8

()

indicates that the row address is to be obtained from the first table or view specified (in the FROM clause of a SELECT statement or in an UPDATE statement).

Owner

indicates the owner of the table or view.

TableName

indicates the table from which to obtain the row address.

ViewName

indicates the updatable view from which to obtain row address.

CorrelationName

indicates the correlation name of the table or view from which to obtain the row address.

Description

The TID function can be used with user tables and updatable views and with system base tables and system views. It cannot be used with non-updatable views (those containing JOIN, UNION, GROUP BY, HAVING, or aggregate functions) nor on system pseudotables.

In order to assure optimization (through the use of TID access) the expressions in the WHERE clause of a single query block must be ANDed together. No OR is allowed. In addition, only the following TID expressions can be optimized:

  TID([ [Owner.]TableName
        [Owner.]ViewName
        CorrelationName    ]) = 
  {Constant
   HostVariableName [[INDICATOR]:IndicatorVariable]
   ?
   :LocalVariable
   :ProcedureVariable                                }

Only equal and not equal comparison operators are supported.
The TID function cannot appear in an arithmetic expression.
The TID function can be used in a restricted set of SELECT statements. A valid SELECT statement must not specify the following:
- An ORDER BY or GROUP BY on the TID function.
- A HAVING clause containing the TID function.
- The TID function in the select list when a GROUP BY or HAVING clause is used.
- An aggregate function on the TID function.
- Any TID function along with an aggregate function in the select list.

Example

   isql=> SELECT tid(), PartNumber
   > FROM PurchDB.Parts;
 
   select tid(), PartNumber from PurchDB.Parts;
   -----------------------+----------------
   TID                    |PARTNUMBER
   -----------------------+----------------
                     3:3:0|1123-P-01
                     3:3:1|1133-P-01
                     3:3:2|1143-P-01
                     3:3:3|1153-P-01
                     3:3:4|1223-MU-01
                     3:3:5|1233-MU-01
                     3:3:6|1243-MU-01
                     3:3:7|1323-D-01
                     3:3:8|1333-D-01
                     3:3:9|1343-D-01
                    3:3:10|1353-D-01
                    3:3:11|1423-M-01
                    3:3:12|1433-M-01
                    3:3:13|1523-K-01
                    3:3:14|1623-TD-01
                    3:3:15|1723-AD-01 
---------------------------------------------------------------------
   First 16 rows have been selected.
   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >

TID Function

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description

Example