Example for regular data: Create and load a regular time series

To create the TimeSeries data type and table:

1. 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.
2. 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;

To create regular, empty time series:

1. 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; 

2. 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.
3. 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.

To create the data load file:

1. 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.
2. 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;

To load time series data:

1. 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.
2. From the main menu, choose Help > Cheat Sheets, expand the TimeSeries Data category, choose Loading from a File, and click OK.
3. Open the TimeSeries perspective.
4. Create a project area named my_test.
5. 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
6. Define the following subcolumns for the raw_reads column and then save the project:
   • timestamp: choose the Timestamp type
   • data: choose the Numeric type
7. 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
8. 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.
9. Create a connection profile to the Informix database server named my_ifx.
10. 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.

To create a virtual table based on the time series table:

EXECUTE PROCEDURE TSCreateVirtualTab("my_vt2", "my_ts_data", 
   "calendar(ts_15min), origin(2010-11-10 00:00:00.00000)");

SELECT * FROM my_vt2 WHERE timestamp = "2010-11-10 00:00:00.00000";