Programming with TID Data Access

Each row (tuple) in an ALLBASE/SQL table is stored at a database address on disk. This unique address is called the tuple identifier or TID. When using a SELECT statement, you can obtain the TID of any row. In turn, you can use this TID to specify the target row for a SELECT, UPDATE, or DELETE statement. TID functionality provides the fastest possible data access to a single row at a time (TID access) in conjunction with maximum coding flexibility. The following options are available:

Rapid read and write access to a specific row without the use of a cursor (less overhead).
Rapid update and delete capability based on TIDs returned by a nested query, a union query, a join query, or a query specifying sorted data.

Other ALLBASE/SQL functionality provides a method of processing a multiple row query result sequentially, one row at a time. This involves the use of a cursor with the UPDATE WHERE CURRENT, DELETE WHERE CURRENT, and REFETCH commands which internally utilize TID access. See the ALLBASE/SQL Reference Manual for more details.

The nature of your applications will determine how valuable TID functionality can be to you. It could be most useful for applications designed for interactive users and applications that must update a set of related rows as a group. See the programming examples at the end of this chapter.

A TID function and host variable data type are provided. The TID function is used in the select list and/or the WHERE clause of a SELECT statement and in the WHERE clause of an UPDATE or DELETE statement. The new host variable data type is used in an application program to hold data input to and output from the TID function.

Understanding TID Function Input and Output

The next sections describe how TID output is accessed via a select list and how you provide TID input via a WHERE clause. Topics discussed are as follows:

Using the TID Function in a Select List.
Using the TID Function in a WHERE Clause.
Declaring TID Host Variables.
Understanding the SQLTID Data Format.

Using the TID Function in a Select List

When using the TID function in a select list, specify it as you would a column name. In an application, you could use a statement like the following:

     SELECT TID(), VendorNumber, VendorName, PhoneNumber 

            INTO   :TidHostVar, :VendorNumber, 

                   :VendorName, :PhoneNumber;

            FROM   Purchdb.Vendors

            WHERE  VendorName = :VendorName

The resulting TID and column data is placed in the host variables, TidHostVar, VendorNumber, VendorName, PhoneNumber.

The next example illustrates how to obtain TID values for qualifying rows of a two table join. Correlation names are used.

     SELECT TID(sp), TID(o)

       INTO :SupplyPriceTID, :OrdersTID,

       FROM PurchDB.SupplyPrice sp,

            PurchDB.Orders o

      WHERE sp.VendorNumber = :VendorNumber 

        AND  o.VendorNumber = :VendorNumber

Using the TID Function in a WHERE Clause

When using the TID function in a WHERE clause, you provide an input parameter. For application programs, this parameter can be specified as a host variable, a constant, or a question mark (?) representing a dynamic parameter. The input parameter is a constant. For example:

   DELETE FROM PurchDB.Parts WHERE TID() = 3:3:30;

In an application, you could use a statement like the following to verify the data integrity of a previously accessed row:

     SELECT  PartNumber, PartName, SalesPrice 

       INTO  :PartNumber, :PartName, :SalesPrice

       FROM  PurchDB.Parts

      WHERE  TID() = :PartsTID

You might use the following statement in an application to update a row:

   UPDATE PurchDB.Parts 

      SET PartNumber = :PartNumber, 

          PartName = :PartName, 

          SalesPrice = :SalesPrice 

    WHERE TID() = :PartsTID

Declaring TID Host Variables

Host variables for TID function input and output must be declared in your application as SQLTID host variables. You would declare an SQLTID host variable as follows:

TIDVarName : SQLTID;

Understanding the SQLTID Data Format

The data in SQLTID host variables has its own unique format which is not compatible with any other ALLBASE/SQL data type. It is not necessary to know the internal format of SQLTID data to use the TID function. The information in this section is provided in case you require the TID value to be broken into its components.

For instance, you might want to know the page numbers of all TID's in a table in order to analyze data distribution. To do this, you must parse the SQLTID host variable.

ALLBASE/SQL does allow you to unload SQLTID data. However, you cannot use the LOAD command to load TID data back into a table. The TID is a unique identifier generated internally by ALLBASE/SQL, and cannot be assigned by users.

An SQLTID host variable consists of eight bytes of binary data and has the following format:

Table 13-4 SQLTID Data Internal Format

Content	Byte Range
Version Number	1 through 2
File Number	3 through 4
Page Number	5 through 7
Slot Number	8

The SQLTID version number is an optional input parameter. If not specified, the version number defaults to 0. If you do specify the version, it must always be 0. If a version other than 0 is specified, no rows will qualify for the operation.

TID function application output always contains a version number of 0.

Programming with TID Data Access

Technical documentation

» Table of Contents

» Index

Understanding TID Function Input and Output

Using the TID Function in a Select List

Using the TID Function in a WHERE Clause

Declaring TID Host Variables

Understanding the SQLTID Data Format