The TSVTMode parameter

The TSVTMode parameter configures the behavior and display of the virtual table for time series data.

You use the TSVTMode parameter with the TSCreateVirtualTab procedure to control:

How data is updated in the base table when you insert data into the virtual table
Whether NULL time series elements are displayed in a virtual table
Whether to fragment the virtual table so that queries can be run on the virtual table in parallel
Whether updates to existing rows in the base table require accurate values for columns that are not part of the primary key
Whether existing values in columns other than the TimeSeries column or the primary key columns can be updated.
Whether NULL values can be used in the INSERT statement for columns other than the primary key columns.
Whether hidden time series elements are displayed in a virtual table
Whether data selected by time stamp exactly matches the specified time stamps or includes the last rows that are equal to or earlier than the specified time stamps.
Whether to quickly insert elements into time series instances that are stored in containers.
Whether to reduce how many log records are generated when you insert data.

You use the TSVTMode parameter with the TSCreateExpressionVirtualTab procedure to control:

Whether NULL time series elements are displayed in a virtual table
Whether hidden time series elements are displayed in a virtual table
Whether data selected by time stamp exactly matches the specified time stamps or includes the last rows that are equal to or earlier than the specified time stamps.

The default value of the TSVTMode parameter, 0, sets the default behavior of the virtual table. Each of the other values of the TSVTMode parameter reverses one aspect of the default behavior.

You can set the TSVTMode parameter to a combination of the values. You can specify values for the TSVTMode parameter in the following formats:

Numeric: Sum the numeric values of the flags that you want to include. For example, if you want both null and hidden elements to be displayed in the virtual table, set the TSVTMode parameter to 514 (512 + 2). You can also specify the numeric value as a hexadecimal number.
String: List the flag names that you want to include, separated by one of the following delimiters: plus sign (+), pipe (|), or comma (,). For example, if you want both null and hidden elements to be displayed in the virtual table, set the TSVTMode parameter to 'scan_hidden+show_nulls'.

Table 1. Settings for the `TSVTMode` parameter
Flag name	Value	Description
putelemnodups	0	Default. The virtual table has the following behavior: Multiple elements for the same timepoint are not allowed. Updates to the underlying time series update existing elements for the same timepoint. Uses the PutElemNoDups function. Null elements are not included in the virtual table. The virtual table is not fragmented. If the base table has a primary key, the primary key is used to find the row to update and updates to the base table do not require accurate values for columns that are not part of the primary key. If the base table does not have a primary key, all columns in the table except the TimeSeries column are used to identify the row to be updated and updates to the base table require accurate values for every column in the base table other than the TimeSeries column. NOT NULL constraints are included in the virtual table for the primary key columns and other columns that have NOT NULL constraints in the base table. For updates to existing rows, only the TimeSeries column can be updated. Hidden elements are not included in the virtual table. When you select data from a virtual table by time stamps, the rows whose time stamps are closest to being equal to or earlier than the time stamps specified in the query are returned. If the time series is irregular, the returned rows show the same time stamps as specified in the query, regardless if the actual time stamps are the same. See Default behavior
putelem	1	Multiple elements for the same timepoint are allowed. Updates to the underlying time series insert elements even if elements exist for the timepoints. Uses the PutElem function. See Duplicate timepoints.
show_nulls	2	Null elements are displayed in the virtual table. Hidden elements are displayed as null elements, unless the value 512 is also set. See Null and hidden elements.
fragment	4	This setting is only valid if the base table is fragmented by expression. The virtual table is fragmented and queries on the virtual table are run in parallel if PDQ is enabled. The virtual table creation statement contains a fragmentation clause that is rewritten to resolve ambiguous expressions and the REMAINDER clause with successive NOT operations. Successive NOT operations quickly increase the length of the statement that creates the virtual table. If the fragmentation scheme on the base table is complex, the virtual table creation statement might fail because it exceeds the character limit for SQL statements. Same as the fragment_with_negate (262144) setting. See Fragment the virtual table.
disable_not_null_constraints	16	For existing rows, you can specify NULL values for columns that are not part of the primary key, regardless if those columns have NOT NULL constraints in the base table. NOT NULL constraints are not included in the virtual table, but are enforced in the base table. For new rows, you can specify null values for columns that are not part of the primary key and do not have NOT NULL constraints.
update_nonkey_not_nulls	32	This setting is valid only if the base table has a primary key. You can update the value of columns in an existing row that are not part of the primary key. You can specify NULL for non-primary key columns that you do not want to update. All columns that have non-NULL values in the INSERT statement are updated in the base table, except the primary key columns. See Update values that are not in the primary key.
update_nonkey_include_nulls	64	This setting is valid only if the base table has a primary key. You can update the value of all the columns in an existing row that are not part of the primary key, including setting null values for columns that allow null values. Columns that are not part of the primary key are updated to the value included in the INSERT statement. Columns that allow null values can be set to NULL. See Update values that are not in the primary key and allow null values.
elem_insert	128	You can quickly insert elements directly into containers given the following constraints: The base table has a primary key Base table columns besides the primary key columns are not being updated The threshold for the time series is 0. Cannot be combined with the settings 16, 32, or 64. Can be combined with the `NewTimeSeries` parameter: If the primary key value does not exist in the base table, a new row is added to the base table. The primary key value is inserted and a new time series instance is created with the supplied values. Data is inserted into only the primary key columns and the TimeSeries columns, regardless of whether values for other columns are included in the data. The other columns in the base table are filled with default values, if defaults are defined, or else null values. If any of the other columns has a NOTNULL constraint and no default value defined, the insert fails. If the row for the primary key value exists but the TimeSeries column is NULL, a new time series instance is created with the supplied values.
reduced_log	256	Reduces how many log records are generated when you insert elements into containers. By default, every element that you insert generates two log records: one for the inserted element and one for the page header update. If this flag is set, page header updates are logged per transaction instead of per element. The INSERT statements must be run within a transaction without other types of SQL statements. The elements that are inserted are not visible by dirty reads until after the transaction commits.
scan_hidden	512	Hidden elements are displayed in the virtual table. See Null and hidden elements.
scan_discreet	1024	When you select data from a virtual table by time stamps, only rows whose time stamps are exactly equal to the time stamps specified in the query are returned.
fragment_verbatim	65536	This setting is only valid if the base table is fragmented by expression. The virtual table is fragmented and queries on the virtual table are run in parallel if PDQ is enabled. The fragmentation scheme for the base table must have expressions that each map to a single fragment and must not have a REMAINDER clause. The virtual table creation statement uses the same fragmentation clause as the base table. See Fragment the virtual table.
fragment_with_case	131072	This setting is only valid if the base table is fragmented by expression. The virtual table is fragmented and queries on the virtual table are run in parallel if PDQ is enabled. The virtual table creation statement contains a fragmentation clause that is rewritten to resolve ambiguous expressions and the REMAINDER clause with CASE operations. The virtual table creation statement is not much longer than the base table creation statement. This fragmentation setting can occasionally result in unexpected query plans, such as sequential scans. See Fragment the virtual table.
fragment_with_negate	262144	Same as the fragment (4) setting. This setting is only valid if the base table is fragmented by expression. The virtual table is fragmented and queries on the virtual table are run in parallel if PDQ is enabled. The virtual table creation statement contains a fragmentation clause that is rewritten to resolve ambiguous expressions and the REMAINDER clause with successive NOT operations. Successive NOT operations quickly increase the length of the statement that creates the virtual table. If the fragmentation scheme on the base table is complex, the virtual table creation statement might fail because it exceeds the character limit for SQL statements. See Fragment the virtual table.

The following examples illustrate some of the settings for the TSVTMode parameter. The examples use a base table with columns for the account number, the meter identifier, the time series data, the meter owner, and the meter address. The account number and meter identifier columns are the primary key. The TimeSeries column contains columns for the time stamp, energy, and temperature. The owner column has a NOT NULL constraint. Each of the virtual tables that is created in the examples has the following initial one row that represents one times series element:

acct_no      6546
meter_id     234
t            2011-01-01 00:00:00.00000
energy       33070
temperature  -13.0000000000
owner        John
address      5 Nowhere Place

1 row(s) retrieved.

Default behavior

The following statement creates a virtual table named smartmeters_vti_nn with the TSVTMode parameter set to 0:

EXECUTE PROCEDURE TSCreateVirtualTab('smartmeters_vti_nn', 
        'smartmeters', 'origin(2011-01-01 00:00:00.00000), 
        calendar(ts_15min), regular,threshold(20), container()', 0);

The following statement inserts a new row into the virtual table and a new element in the time series in the base table:

INSERT INTO smartmeters_vti_nn(acct_no,meter_id,t,energy,temperature,owner,address)
 VALUES(6546, 234,
        '2011-01-01 00:45:00.00000'::datetime year to fraction(5),
        3234, -12.00,
        'Ignored_value', 'Ignored_value');
1 row(s) inserted.

The values of the primary key columns match the original row. The values of the owner and address columns are ignored; they are not used to identify the row that must be updated and those values are not updated in the base table. After the INSERT statement, the virtual table contains two rows, and each contains the original values of the owner and address columns:

SELECT * FROM smartmeters_vti_nn;


acct_no      6546
meter_id     234
t            2011-01-01 00:00:00.00000
energy       33070
temperature  -13.0000000000
owner        John
address      5 Nowhere Place

acct_no      6546
meter_id     234
t            2011-01-01 00:45:00.00000
energy       3234
temperature  -12.0000000000
owner        John
address      5 Nowhere Place

2 row(s) retrieved.

Fragment the virtual table

The fragmentation scheme for virtual tables is adapted from the fragmentation scheme of the base table. Because the virtual table is not physically fragmented, each fragmentation expression must be unambiguous so that every value maps to only one fragment. When you run the TSCreateVirtualTab procedure, the fragmentation clause is rewritten, if necessary, to resolve ambiguous expressions, including the REMAINDER clause. For example, the following fragmentation clause contains ambiguous expressions:

FRAGMENT BY EXPRESSION
   (meter_id < 100) IN dbs1,
   (meter_id < 200) IN dbs2,
   REMAINDER IN dbs3

These expressions are ambiguous because any meter_id value that is less than 100 is also less than 200.

Choose the best fragmentation flag for your fragmentation scheme by following these rules:

If the fragmentation clause for the base table has expressions that each map to a single fragment and does not have a REMAINDER clause, use the fragment_verbatim flag. Include the value 65536 in the TSVTMode parameter. The virtual table creation statement uses the same fragmentation expressions as the base table.
If the fragmentation clause for the base table has ambiguous expressions or a REMAINDER clause, use the fragment_negate flag. Include the value 262144 in the TSVTMode parameter. The virtual table creation statement uses NOT operations to rewrite ambiguous expressions. The virtual table fragmentation clause for the previous example is:
```
FRAGMENT BY EXPRESSION
  PARTITION PART_0 (meter_id < 100) IN dbs1,
  PARTITION PART_1 ((meter_id < 200) AND (NOT(meter_id < 100)) IN dbs1,
  PARTITION PART_2 ((NOT (meter_id < 100)) and (NOT(meter_id < 200))) IN dbs1 
```
Each successive expression contains NOT operations for all previous expressions. If the TSCreateVirtualTab procedure fails because the virtual table creation statement is too long, use the fragment_with_case flag.
If the fragmentation clause for the base table is very long and has ambiguous expressions or a REMAINDER clause, use the fragment_with_case flag. Include the value 131072 in the TSVTMode parameter. The virtual table creation statement uses CASE expressions to rewrite ambiguous expressions. The virtual table fragmentation clause for the previous example is:
```
FRAGMENT BY EXPRESSION
   PARTITION PART_0 (CASE WHEN (meter_id < 100) THEN 0 WHEN (meter_id < 200) 
                     THEN 1 ELSE 2) = 0) in db1,
   PARTITION PART_1 (CASE WHEN (meter_id < 100) THEN 0 WHEN (meter_id < 200) 
                     THEN 1 ELSE 2) = 1) in db1,
   PARTITION PART_2 (CASE WHEN (meter_id < 100) THEN 0 WHEN (meter_id < 200) 
                     THEN 1 ELSE 2) = 2) in db1
```
This flag might result in queries that use sequential scans instead of the appropriate index.

When you run queries in parallel on fragmented virtual tables, set the isolation level to Dirty Read. Otherwise, when a parallel query on the virtual table and a query that modifies data on the base table coincide, the parallel query might fail with a -244 error. The Dirty Read isolation level can result in returning phantom rows from other sessions that are never committed. If you do not want to set the Dirty Read isolation level and you want to run a parallel query at a time when you know that the base table is being updated, you can disable PDQ for the transaction.

The results of parallel queries on fragmented virtual tables are not necessarily ordered by the primary key values. You can ensure that the results are ordered properly by including an ORDER BY clause in the query that specifies the primary key and the time stamp column. If you do not include an ORDER BY clause, the results are grouped in batches of up to 128 rows per primary key value. Within each batch, the results are ordered by time stamp. The batches for each primary key value are ordered chronologically, but interspersed with batches for different primary key values.

For example, suppose that the results of a query include 396 values for the primary key value meter1, 347 values for meter2, and 280 values for meter3. The results might be ordered in the following way:

128 rows of meter1
128 rows of meter2
128 rows of meter3
128 rows of meter1
128 rows of meter3
128 rows of meter1
128 rows of meter3
12 rows of meter1
24 rows of meter3
91 rows of meter2

Update values that are not in the primary key

The following statement creates a virtual table named smartmeters_vti_nn_nk_nn with the TSVTMode parameter set to 32:

EXECUTE PROCEDURE TSCreateVirtualTab('smartmeters_vti_nn_nk_nn', 
           'smartmeters', 'origin(2011-01-01 00:00:00.00000), 
           calendar(ts_15min), regular,threshold(20), container()', 32);

The following statement inserts a new row into the virtual table and a new element in the time series in the base table:

INSERT INTO smartmeters_vti_nn_nk_nn(acct_no,meter_id,t,energy,
                                     temperature,owner,address)
 VALUES(6546, 234,
        '2011-01-01 00:45:00.00000'::datetime year to fraction(5),
        3234, -12.00,
        'Jim', NULL);
1 row(s) inserted.

The value of the owner column is updated to Jim. The value of the address column is not changed because null values are ignored. The virtual table now contains two rows, each of which have the new value for the owner column and the existing value for the address column:

SELECT * FROM smartmeters_vti_nn_nk_nn;


acct_no      6546
meter_id     234
t            2011-01-01 00:00:00.00000
energy       33070
temperature  -13.0000000000
owner        Jim
address      5 Nowhere Place

acct_no      6546
meter_id     234
t            2011-01-01 00:45:00.00000
energy       3234
temperature  -12.0000000000
owner        Jim
address      5 Nowhere Place

2 row(s) retrieved.

Update values that are not in the primary key and allow null values

The following statement creates a virtual table named smartmeters_vti_nn_nk_in with the TSVTMode parameter set to 64:

EXECUTE PROCEDURE TSCreateVirtualTab('smartmeters_vti_nn_nk_in', 
         'smartmeters', 'origin(2011-01-01 00:00:00.00000), 
         calendar(ts_15min), regular,threshold(20), container()', 64);

The following statement inserts a new row into the virtual table and a new element in the time series in the base table:

INSERT INTO smartmeters_vti_nn_nk_in(acct_no,meter_id,t,energy,
                                     temperature,owner,address)
 VALUES(6546, 234,
        '2011-01-01 00:45:00.00000'::datetime year to fraction(5),
        3234, -12.00,
        'Jim', NULL);
1 row(s) inserted.

The value of the owner column is updated to Jim. The value of the address column is updated to a null value. The virtual table now contains two rows, each of which have the new value for the owner column and a null value for the address column:

SELECT * FROM smartmeters_vti_nn_nk_in;


acct_no      6546
meter_id     234
t            2011-01-01 00:00:00.00000
energy       33070
temperature  -13.0000000000
owner        Jim
address      

acct_no      6546
meter_id     234
t            2011-01-01 00:45:00.00000
energy       3234
temperature  -12.0000000000
owner        Jim
address      

2 row(s) retrieved.

Duplicate timepoints

By default, the database server uses the PutElemNoDups function to add an element to the underlying time series. If an element exists at the same timepoint, the existing element is updated. You can perform bulk updates of the underlying time series without producing duplicate elements for the same timepoints.

When the TSVTMode parameter includes the value 1, the database server uses the PutElem function to add an element to the underlying time series. The PutElem function handles updates to existing data in an underlying irregular time series differently than does the PutElemNoDups function.

Null and hidden elements

The TSVTMode parameter includes options to display null or hidden time series elements in the virtual table. By default, if a base table has a null element at a specific timepoint, the virtual table has no entries for that timepoint. You can use the TSVTMode parameter to display null elements as a row of null values, plus the time stamp column and any non-time-series columns from the base table.

If the TSVTMode parameter includes the value 2, null time series elements are displayed as null values in the virtual table. Hidden elements also show as null values. If the TSVTMode parameter does not include the value 2, null time series elements do not show in the virtual table.

If the TSVTMode parameter includes the value 512, hidden time series elements are displayed in the virtual table; otherwise, they do not.

The following statements create four virtual tables that are all based on the same base table, named inst, which contains the TimeSeries column named bars. Each of the tables uses a different value for the TSVTMode parameter. The inst_vt0 table does not show null or hidden elements. The inst_vt2 table shows null elements. The inst_vt512 table shows hidden elements. The inst_vt514 table shows null and hidden elements.

execute procedure TSCreateVirtualTab( 'inst_vt0', 'inst', 0);
execute procedure TSCreateVirtualTab( 'inst_vt2', 'inst', 2);
execute procedure TSCreateVirtualTab( 'inst_vt512', 'inst', 512);
execute procedure TSCreateVirtualTab( 'inst_vt514', 'inst', 514);

The following statement hides one element by using the HideElem function:

update inst set bars = HideElem( bars, 
    datetime(2011-01-18) year to day) where code = 'AA';
1 row(s) updated.

The following query shows that the inst_vt0 table does not contain the hidden element for 2011-01-18:

select * from inst_vt0
where code = 'AA'
and t between datetime(2011-01-14) year to day
and datetime(2011-01-19) year to day
order by t;

code AA
t     2011-01-14 00:00:00.00000
high  69.25000000000
low   68.37500000000
final 68.62500000000
vol   462.0000000000

code AA
t     2011-01-19 00:00:00.00000
high  69.62500000000
low   69.12500000000
final 69.62500000000
vol   96.69999700000
2 row(s) retrieved.

The following query shows that the inst_vt2 table contains null elements:


select * from inst_vt2
where code = 'AA'
and t between datetime(2011-01-14) year to day
and datetime(2011-01-19) year to day
order by t;

code AA
t     2011-01-14 00:00:00.00000
high  69.25000000000
low   68.37500000000
final 68.62500000000
vol   462.0000000000

code AA
t     2011-01-17 00:00:00.00000
high 
low
final
vol

code AA
t     2011-01-18 00:00:00.00000
high
low
final
vol

code AA
t     2011-01-19 00:00:00.00000
high  69.62500000000
low   69.12500000000
final 69.62500000000
vol   96.69999700000
4 row(s) retrieved.

The following query shows that the inst_vt512 table does contain the hidden element:

select * from inst_vt512
where code = 'AA'
and t between datetime(2011-01-14) year to day
and datetime(2011-01-19) year to day
order by t;

code AA
t     2011-01-14 00:00:00.00000
high  69.25000000000
low   68.37500000000
final 68.62500000000
vol   462.0000000000

code AA
t     2011-01-18 00:00:00.00000
high  69.75000000000
low   68.75000000000
final 69.62500000000
vol   281.2000100000

code AA
t     2011-01-19 00:00:00.00000
high  69.62500000000
low   69.12500000000
final 69.62500000000
vol   96.69999700000
3 row(s) retrieved.

The following query shows that the inst_vt514 table does contain the hidden element and the null element:

select * from inst_vt514
where code = 'AA'
and t between datetime(2011-01-14) year to day
and datetime(2011-01-19) year to day
order by t;

code AA
t     2011-01-14 00:00:00.00000
high  69.25000000000
low   68.37500000000
final 68.62500000000
vol   462.0000000000

code AA
t     2011-01-17 00:00:00.00000
high 
low 
final 
vol 

code AA
t     2011-01-18 00:00:00.00000
high  69.75000000000
low   68.75000000000
final 69.62500000000
vol   281.2000100000

code AA
t     2011-01-19 00:00:00.00000
high  69.62500000000
low   69.12500000000
final 69.62500000000
vol   96.6999970000
4 row(s) retrieved.