Example for regular data: Create and load a regular time series
This example shows how to create a TimeSeries data type, create a time series table, create a regular time series by running the TSCreate function, and load data into the time series through the Informix® TimeSeries Plug-in for Data Studio.
Before you begin
- Data Studio or Optim™ Development Studio must be running and the Informix® TimeSeries Plug-in for Data Studio must be installed. Data Studio can be installed on a different computer than the database server.
- The stores_demo database must exist. You create the stores_demo database by running the dbaccessdemo command.
About this task
Time series property | Definition |
---|---|
Timepoint size | 15 minutes |
When timepoints are valid | Every 15 minutes with no invalid times |
Data in the time series | The following data:
|
Time series table | The following columns:
|
Origin | All meter IDs have an origin of 2010-11-10 00:00:00.00000 |
Regularity | Regular |
Metadata | No metadata |
Amount of storage space | Approximately 1 MB (8640 timepoints for each of the 28 rows) |
Where to store the data | In an automatically created container in the same dbspace as the stores_demo database, which is in the root dbspace by default |
How to load the data | The TimeSeries plug-in |
How to access the data | A virtual table |
Creating a TimeSeries data type and table
About this task
Procedure
To create the TimeSeries data type and table:
- Create a TimeSeries subtype named my_meter_data in
the stores_demo database by running the following SQL statement:
CREATE ROW TYPE my_meter_data( timestamp DATETIME YEAR TO FRACTION(5), data DECIMAL(4,3) );
The timestamp column contains the time of the meter reading and the data column contains the reading value. - Create a time series table named my_ts_data by running
the following SQL statement:
CREATE TABLE IF NOT EXISTS my_ts_data ( meter_id BIGINT NOT NULL PRIMARY KEY, raw_reads TIMESERIES(my_meter_data) ) LOCK MODE ROW;
Creating regular, empty time series
About this task
Procedure
To create regular, empty time series:
- Create an unload file named my_meter_id.unl that
contains the meter IDs from the loc_esi_id column of the ts_data table
by running the following SQL statement:
UNLOAD TO "my_meter_id.unl" SELECT loc_esi_id FROM ts_data;
- Create a temporary table named my_tmp and load the
meter IDs into it by running the following SQL statements:
CREATE TEMP TABLE my_tmp ( id BIGINT NOT NULL PRIMARY KEY); LOAD FROM "my_meter_id.unl" INSERT INTO my_tmp;
You use this table in the next step to create a time series for each meter ID with one SQL statement instead of running a separate SQL statement for each meter ID. - Create a regular, empty time series for each meter ID that
uses the pre-defined calendar ts_15min by running the following
SQL statement, which uses the time series input function:
INSERT INTO my_ts_data SELECT id, "origin(2010-11-10 00:00:00.00000),calendar(ts_15min), threshold(0),regular,[]" FROM my_tmp;
Because you did not specify a container name, the time series for each meter ID is stored in a container in the same dbspace in which the table resides. The container is created automatically and is a member of the default container pool.
Creating the data load file
About this task
Procedure
To create the data load file:
- Create a virtual table based on the raw_reads time
series column of the ts_data table by running the following
SQL statement:
EXECUTE PROCEDURE TSCreateVirtualTab("my_vt", "ts_data", 0, "raw_reads");
You use the virtual table to create a data load file. - Unload the data from the tstamp and value columns
from the virtual table into a file named my_meter_data.unl by
running the following SQL statement:
UNLOAD TO my_meter_data.unl SELECT loc_esi_id, tstamp, value FROM my_vt;
Loading the time series data
About this task
Procedure
To load time series data:
- If you are using Data Studio or Optim™ Development Studio on a different computer, move the $INFORMIXDIR\my_meter_data.unl file to that computer and start Data Studio or Optim™ Development Studio.
- From the main menu, choose Help > Cheat Sheets, expand the TimeSeries Data category, choose Loading from a File, and click OK.
- Open the TimeSeries perspective.
- Create a project area named my_test.
- Create the Informix® table
definition and define the columns of the table. Name the table definition my_table and
save the definition in the my_test project directory. Define
the following table columns:
- meter_id: choose the Big Integer type and specify that it is the primary key
- raw_reads: choose the TimeSeries type
- Define the following subcolumns for the raw_reads column
and then save the project:
- timestamp: choose the Timestamp type
- data: choose the Numeric type
- Create a record format and define the format of the data
file. Name the record format definition my_format and save
it in the my_test project directory. Define the following record
formats:
- meter_id: choose the Big Integer type and specify the | (pipe) delimiter
- timestamp: choose the Timestamp type and specify the | (pipe) delimiter
- data: choose the Numeric type and specify the | (pipe) delimiter
- Create a table map named my_map and map the data formats of the data file to the columns of the Informix® table and then save it in the my_test project directory.
- Create a connection profile to the Informix® database server named my_ifx.
- Define and start a load job. Specify the following values:
- File format file: my_format.udrf
- Table definition file: my_table.tbl
- Mapping file: my_map.tblmap
- Data file: my_meter_data.unl
- Connection profile: my_ifx
When you click OK, the load job starts and you see the status.
Accessing time series data through a virtual table
About this task
Procedure
To create a virtual table based on the time series table:
Use the TSCreateVirtualTab procedure
to create a virtual table named my_vt2 that is based on the my_ts_data table
by running the following SQL statement:
EXECUTE PROCEDURE TSCreateVirtualTab("my_vt2", "my_ts_data",
"calendar(ts_15min), origin(2010-11-10 00:00:00.00000)");
Results
SELECT * FROM my_vt2 WHERE timestamp = "2010-11-10 00:00:00.00000";