Pseudocolumns in TimesTen Scaleout

Pseudocolumns are not actual columns in a table but behave like columns. A pseudocolumn is an assigned value used in the same context as a column, but is not stored.

You can perform select operations, but you cannot perform insert or update operations on a pseudocolumn.

Pseudocolumns in TimesTen Scaleout:

  • elementId#: An element stores a portion of the database. Use the elementId# pseudocolumn to determine the element from which you accessed the row. This pseudocolumn returns a NOT NULL TT_INTEGER data type.

  • replicaSetId#: Use this pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns a NOT NULL TT_INTEGER data type.

  • dataspaceId#: Use this pseudocolumn to determine the data space in which the copy of the row resides. This pseudocolumn returns a NOT NULL TT_INTEGER data type.

Note:

  • For DML operations, use replicaSetId# instead of elementId#. This is also true for SELECT...FOR UPDATE.

  • For SELECT operations, use replicaSetId# unless you want to select rows from a specific element. In this case, use the TT_GridQueryExec (GLOBAL) optimizer hint with your SELECT statement. See "TT_GridQueryExec Optimizer Hint" for more information.

These sections illustrate how to use pseudocolumns:

Using Pseudocolumns to Locate Data

This example illustrates how to use pseudocolumns to locate data. It determines the element to which the application is connected. It then issues a query on the customers table and returns the elementId#, replicaSetId#, and dataspaceId# where the data is located.

Command> SELECT elementid# FROM dual;
< 1 >
1 row found.

Command> SELECT elementId#,replicasetid#,dataspaceId#,cust_id,last_name,first_name
         FROM customers WHERE cust_id BETWEEN 910 AND 920
         ORDER BY cust_id, last_name, first_name;
< 3, 2, 1, 910, Riley, Tessa >
< 1, 1, 1, 911, Riley, Rashad >
< 1, 1, 1, 912, Riley, Emma >
< 1, 1, 1, 913, Rivera, Erin >
< 1, 1, 1, 914, Roberts, Ava >
< 1, 1, 1, 915, Roberts, Lee >
< 3, 2, 1, 916, Roberts, Clint >
< 5, 3, 1, 917, Robertson, Faith >
< 3, 2, 1, 918, Robinson, Miguel >
< 3, 2, 1, 919, Robinson, Mozell >
< 5, 3, 1, 920, Rodgers, Darryl >
11 rows found.

Working With Pseudocolumns and Duplicate Tables

This example illustrates how to use pseudocolumns with duplicate tables. It uses the ttIsql describe command on the account_status table to validate the table has a duplicate distribution scheme. The example then issues a query from a connection that has been connected to element 1. The example returns the elementId#, replicasetId#, and dataspaceId# so the location of the data can be determined. The example repeats the same query from a connection that has been connected to element 2. The example illustrates the data is located on the element to which the application is connected and thus is present in every element of the database (duplicate distribution scheme).

Command> describe account_status;
 
Table SAMPLEUSER.ACCOUNT_STATUS:
  Columns:
   *STATUS                          NUMBER (2) NOT NULL
    DESCRIPTION                     VARCHAR2 (100) INLINE NOT NULL
  DUPLICATE
 
1 table found.
(primary key columns are indicated with *)
Command> SELECT elementId# FROM dual; 
< 1 >
1 row found.

Command> SELECT elementId#,replicaSetId#,dataspaceId#, *
         FROM account_status;
< 1, 1, 1, 10, Active - Account is in good standing >
< 1, 1, 1, 20, Pending - Payment is being processed >
< 1, 1, 1, 30, Grace - Automatic payment did not process successfully >
< 1, 1, 1, 40, Suspend - Account is in process of being disconnected >
< 1, 1, 1, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.

Issue the same query from a connection to element 2.

Command> SELECT elementid# from dual;
< 2 >
1 row found.

Command> SELECT elementId#,replicaSetId#,dataspaceId#, *
         FROM account_status;
< 2, 1, 2, 10, Active - Account is in good standing >
< 2, 1, 2, 20, Pending - Payment is being processed >
< 2, 1, 2, 30, Grace - Automatic payment did not process successfully >
< 2, 1, 2, 40, Suspend - Account is in process of being disconnected >
< 2, 1, 2, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.

Using Pseudocolumns to Locate a Local Element

This example illustrates how to use pseudocolumns to return information for the element to which the application is connected. It assumes you have created a grid with six data instances and K-safety set to 2. The purpose of this example is to show you how to identify the element id, replica set, and data space group for the element to which the application is connected.

In this example, your connection is connected to element 1. Selecting from the dual table returns the element id, replica set id, and data space id of the current local connection. In this example, element 1 is in replica set 1 and data space 1.

Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual;
< 1, 1, 1 >
1 row found.

In this example, your connection is connected to element 3. Element 3 is in replica set 2 and in data space 1.

Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual;
< 3, 2, 1 >
1 row found.

Displaying the Element Id Associated With an Instance

This example illustrates how to use the ttGridAdmin dbStatus -element command to display the element id associated with each instance. This command also gives the status of each element. (You must issue this command from the active management instance and you must issue it as the instance administrator.)

See Monitor the Status of a Database (dbStatus) in the Oracle TimesTen In-Memory Database Reference for more information.

$ ttGridAdmin dbStatus -element
Database database1 element level status as of Thu Apr  5 12:57:44 PDT 2018
 
Host            Instance Elem Status Date/Time of Event  Message
--------------- -------- ---- ------ ------------------- -------
host1           instance1    1 opened 2018-04-05 11:15:33
host2           instance2   2 opened 2018-04-05 11:15:33
host3           instance3   3 opened 2018-04-05 11:15:33
host4           instance4   4 opened 2018-04-05 11:15:33
host5           instance5   5 opened 2018-04-05 11:15:33
host6           instance6   6 opened 2018-04-05 11:15:33