Example for compressible data: Create and load a compressed time series

This example shows how to create, load, and query a time series that stores compressed numeric data.

About this task

In this example, you create a time series that contains numeric data that is compressed. You can create the time series table in any database that you choose. The following table lists the time series properties that are used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Calendar interval	Second
When elements are valid	Always
Data in the time series	The following data: Timestamp Three columns with INTEGER values
Time series table	The following columns: An ID column of type INTEGER A TimeSeries data type column
Origin	2014-01-01 00:00:00.00000
Regularity	Irregular
Compression	Three different compression definitions
Metadata	No metadata
Where to store the data	In a container that you create
How to load the data	The InsElem function
How to access the data	A virtual table

Procedure

To create, load, and view a compressed time series:

Create a TimeSeries subtype that is named ts_data_c in a database by running the following SQL statement:
```
CREATE ROW TYPE ts_data_c(
      tstamp  datetime year to fraction(5),
      ts1     int,
      ts2     int,
      ts3     int
);
```
You can include only certain numeric data types in your TimeSeries subtype.

Create a time series table that is named tstable_c by running the following SQL statement:

CREATE TABLE IF NOT EXISTS tstable_c(
   id  int not null primary key,
   ts  timeseries(ts_data_c)
) LOCK MODE ROW;

Create a container that is named container_c by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSContainerCreate('container_c', 'rootdbs', 'ts_data_c', 512, 512);
```
You can choose to create the container in a different dbspace than the root dbspace.

Create a calendar by running the following SQL statement:

INSERT INTO CalendarTable(c_name, c_calendar)
   VALUES('ts_1sec',
      'startdate(2014-01-01 00:00:00.00000),
       pattern({1 on}, second)');

You cannot include off periods in the calendar for a compressed time series.

Create a compressed time series with compression definitions for the three data columns in the ts_data_c data type by running the following SQL statement in an explicit transaction:
```
BEGIN;
Started transaction.

INSERT INTO tstable_c VALUES(50,
   TSCreateIrr('ts_1sec', '2014-01-01 00:00:00.00000', 'container_c', 
               'q(2,1,100),lb(20),ls(20)'));
1 row(s) inserted.

COMMIT;
```
The first INTEGER column has a compression type of quantization with a compression size of 2 bytes, a lower bound of 1 and an upper bound of 100. The second INTEGER column has a compression type of linear boxcar and a maximum deviation of 20. The third INTEGER column has a compression type of linear swing door and a maximum deviation of 20.

Insert five records for the same element into the time series by running the following SQL statements:

BEGIN;
Started transaction.

UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:00.00000', 
            1, 201, 99991)::ts_data_c) WHERE id = 50;
1 row(s) updated.

UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:01.00000', 
            2, 202, 99992)::ts_data_c) WHERE id = 50;
1 row(s) updated.

UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:02.00000', 
            3, 203, 99993)::ts_data_c) WHERE id = 50;
1 row(s) updated.

UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:03.00000', 
            4, 204, 99994)::ts_data_c) WHERE id = 50;
1 row(s) updated.

UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:04.00000', 
            5, 205, 99995)::ts_data_c) WHERE id = 50;
1 row(s) updated.

COMMIT;
Data committed.

You must insert records in chronological order.

Create a virtual table that is named vt_tstable_c that is based on the compressed time series by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSCreateVirtualTab('vt_tstable_c', 'tstable_c',  4096, 'ts');
```

Query the virtual table to view the compressed data by running the following SQL statement:

SELECT * FROM vt_tstable_c;

         id tstamp                            ts1         ts2         ts3 

         50 2014-01-01 00:00:00.00000           1         201       99991
         50 2014-01-01 00:00:01.00000           2         202       99992
         50 2014-01-01 00:00:02.00000           3         203       99993
         50 2014-01-01 00:00:03.00000           4         204       99994
         50 2014-01-01 00:00:04.00000           5         205       99995

5 row(s) retrieved.