Using Pseudocolumns

A pseudocolumn is an assigned value used in the same context as a column, but is not stored. Pseudocolumns are not actual columns in a table but behave like columns. You can perform select operations, but you cannot perform insert, update, or delete operations on a pseudocolumn.

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

See Pseudocolumns in TimesTen Scaleout in Oracle TimesTen In-Memory Database SQL Reference for information on the additional pseudocolumns supported in TimesTen Scaleout.

Examples include:

• Use replicaSetId# to Locate Data

• Use replicaSetId# with a Table That Has a Duplicate Distribution Scheme

Use replicaSetId# to Locate Data

This example issues a query on the customers table, returning the replica set in which the data is stored (as determined by replicaSetId#).

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

Use replicaSetId# with a Table That Has a Duplicate Distribution Scheme

This example first 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 to return the replicasetId#. The example then repeats the same query from a different connection. The example shows that the data returned is located on the replica set to which the application is connected and thus is present in every element in 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 *)

Query the dual table to return the replica set to which the application is connected. In this example, the replica set is 1.

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

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

Issue a second query from a different ttIsql session running on a different data instance:

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

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