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 theelementId#pseudocolumn to determine the element from which you accessed the row. This pseudocolumn returns aNOTNULLTT_INTEGERdata type. -
replicaSetId#: Use this pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns aNOTNULLTT_INTEGERdata type. -
dataspaceId#: Use this pseudocolumn to determine the data space in which the copy of the row resides. This pseudocolumn returns aNOTNULLTT_INTEGERdata type.
Note:
-
For DML operations, use
replicaSetId#instead ofelementId#. This is also true forSELECT...FOR UPDATE. -
For
SELECToperations, usereplicaSetId#unless you want to select rows from a specific element. In this case, use theTT_GridQueryExec (GLOBAL)optimizer hint with yourSELECTstatement. 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