Contents


Get started now with the Informix TimeSeries solution

Comments

The IBM Informix TimeSeries solution improves database functionality by extending support to store, manipulate, and access time series data. It gives you the freedom to manage time series data by choosing the time series interval and letting you specify where time series is stored.

Time series data is a set of data where each item is time-stamped and accessed in order by time and can be processed and analyzed in a temporal order. Key strengths of the Informix TimeSeries solution include the following.

  • Up to 80% less storage compared to traditional relation approach.
  • Performance improvement in orders of magnitude compared to traditional relational approach for handling temporal data.
  • Reduced application development cost with built-in routines.
  • Ease of administration through Open Admin tool.

This article describes the steps to deal with time series data. The steps for creating and loading a TimeSeries involves working with the following components.

  • Calendar
  • Calendar pattern
  • TimeSeries column
  • TimeSeries container

The article also shows you how to perform the following activities.

  • Create a TimeSeries
  • Load the data

To start working with TimeSeries you need to first create a buffered logged database.

TimeSeries architecture

The TimeSeries subtype is a collection of ROWs. A ROW data type is made up of columns of the same data type or different data type. While you define a TimeSeries, Informix can make use of its own calendar and TimeSeries starting date.

The overall architecture and different components of TimeSeries are described as follows. The two main components are TimeSeries Data Type and TimeSeries Subtype (Row Data Type). The TimeSeries data type acts as a type constructor for a TimeSeries subtype that is a collection of rows. A ROW data type consists of a group of named columns, of the same or different data types that you define, within a single database column.

The timestamp column must be the first column and of type DATETIME YEAR TO FRACTION(5). In the example shown in Figure 1, the table has columns called meter_id that contain the meter number and meter_data that contains the TimeSeries. Each table row consists of different TimeSeries. In this example, the three rows in the table have a TimeSeries of subtype meter_data. The structure of the elements is defined by the TimeSeries row subtype.

Figure 1. TimeSeries architecture
Figure shows TimeSeries Architecture
Figure shows TimeSeries Architecture

Calendar and CalendarPattern data types

The calendar defines when the TimeSeries data is expected. Before you start defining the table with TimeSeries column, you need to create the calendar according to the expected frequency of TimeSeries data. There are some built-in calendars available for quick use.

To set up the calendar you must create the calendar pattern and create the calendar.

Creating the calendar pattern

To create a calendar pattern, you must insert values into the CalendarPatterns table.

Listing 1 shows an example of how to create a calendar pattern called sm_15min. The pattern is in minutes, with each 15 minutes having a valid entry.

Listing 1. Calendar pattern creation
INSERT INTO CalendarPatterns values ( 'sm_15min', '{1 on , 14 off},
minute');

Creating a calendar

To create a calendar, you must insert values into the CalendarTable table. Listing 2 shows how to insert a calendar called cal_15min into the CalendarTable table.

Listing 2. Calendar creation
insert into CalendarTable(c_name, c_calendar)
values ('cal_15min', 'startdate(2012-01-01 00:00:00), 
pattstart(2012-01-01 00:00:00), pattname(sm_15min)');

This calendar starts on January 1, 2012. Its pattern also starts on January 1, 2012 at 00:00:00 o'clock, and it uses the pattern sm_15min which was created in the previous step.

TimeSeries subtype (row type)

In order to create a TimeSeries column you must first create a TimeSeries subtype (row type). The data held in each TimeSeries element is represented in a TimeSeries subtype. A row type is a collection of named columns of the same or different data types that you define within a single database column.

Listing 3 shows a subtype called meter_reading, that holds a timestamp as well as Vot real, Curr real, Pow real, and Avg real for a meter.

Listing 3. Row type creation
create row type meter_reading(
timestamp datetime year to fraction(5),
Vot real,
Curr real,
Pow real,
Avg real
);

Creating a table with TimeSeries data type

After creating the TimeSeries subtype, a table with a TimeSeries column of that subtype can be created using the CREATE TABLE statement.

Listing 4 shows how to create a table called smart_meter that contains a TimeSeries column meter_data of type TimeSeries(meter_reading).

Listing 4. TimeSeries table creation
create table smart_meter (
meter_id int,
meter_data TimeSeries(meter_reading));

TimeSeries containers

Container is the name given to the data structures that hold data for one or more TimeSeries. It guarantees that TimeSeries data is stored clustered and sorted on the disk. Containers are specialized logical storage spaces that are created inside a dbspace. Multiple containers can be created in a dbspace.

Listing 5 shows how to create a container named new_cont for the TimeSeries type meter_reading in the space datadbs.

Listing 5. TimeSeries container creation
execute procedure TSContainerCreate
('new_cont', 'datadbs','meter_reading', 0, 0);

Creating a TimeSeries

To store the time-stamped data in a database table, an empty time series must be created first. This is an additional step to creating a table with TimeSeries column.

You can use the TSCreate function to initialize an empty TimeSeries. Listing 6 shows an example of how to initialize empty TimeSeries.

Listing 6. TimeSeries container creation
INSERT INTO smart_meter VALUES(901, "origin(2012-01-01 00:00:00.00000), 
calendar(cal_15min), container(new_cont), threshold(0),regular,[]"); 
INSERT INTO smart_meter VALUES(902, "origin(2012-01-01 00:00:00.00000),
calendar(cal_15min), container(new_cont), threshold(0),regular,[]");
INSERT INTO smart_meter VALUES(903, "origin(2012-01-01 00:00:00.00000), 
calendar(cal_15min), container(new_cont), threshold(0),regular,[]");
INSERT INTO smart_meter VALUES(904, "origin(2012-01-01 00:00:00.00000), 
calendar(cal_15min), container(new_cont), threshold(0),regular,[]");
INSERT INTO smart_meter VALUES(905, "origin(2012-01-01 00:00:00.00000), 
calendar(cal_15min), container(new_cont), threshold(0),regular,[]");

TSCreate function creates empty TimeSeries using the following parameter values: calendar name cal_15min, origin timestamp 2011-01-01 00:00:00, threshold 0, and container name new_cont.

Virtual table interface

Creating a virtual table interface on top of the TimeSeries tables helps you to look at TimeSeries data similar to the way you would look at standard rational tables. This helps you to make minimal changes to existing applications while converting them to use TimeSeries. Also it helps with programs that cannot handle objects.

Though VTI's are much like views, which provides the interface to deal with TimeSeries tables and do not require any data movements, there is a small difference in the performance as compared to using the TimeSeries tables directly.

Informix TimeSeries provides the SQL interface to create the virtual table on top of the existing TimeSeries table. For example the table smart_meter created in Listing 4 could be converted to a virtual table using the SQL as shown in Listing 7.

Listing 7. Virtual table creation
execute procedure tscreatevirtualtab('virtual_smart_meter',
'smart_meter');

Loading data into an existing TimeSeries

Once a TimeSeries has been created, you need to choose the method to load the data in the TimeSeries. The method depends on the amount of data you would need to load and the format of the data that you have. Depending on your choice and data, you can use one of the following ways to load the data.

  • BulkLoad function to load the bulk data from a file.
  • PutElem, PutSet, InsElem, and InsSet functions to insert or alter one or more rows.
  • Populating data into the virtual table using SQL INSERT statements.

Loading data with BulkLoad function

You can use the BulkLoad function to insert bulk data in the existing TimeSeries. You need to pass TimeSeries name and the data file as the argument to this function. The filename argument is the data file with the row-type data. Listing 8 shows you an example of how to load the data in the TimeSeries created previously.

Listing 8. Loading data with BulkLoad function
update smart_meter set meter_data=BulkLoad(meter_data, 'data.unl') 
WHERE meter_id=901

The data file format follows the row type convention and each line of the data file contains all the data for one element of the TimeSeries. Listing 9 shows you the data file content for the smart_meter update used in the previous example.

Listing 9. data.unl
row(2012-01-01 00:00:00.00000, 6,8,8,10)
row(2012-01-01 00:15:00.00000, 8,10,12,10)
row(2012-01-01 00:30:00.00000, 8,9,12,10)
row(2012-01-01 00:45:00.00000, 7,6,12,9)

Loading or altering data using PutElem, PutElemNoDups, PutSet, InsElem, and InsSet functions

The PutElem, PutElemNoDups, PutSet, InsElem, and InsSet functions can be used to load small amount of data into a TimeSeries. These functions need to be used with the SQL UPDATE Statement. Listing 10 shows you the example of using PutElem and PutElemNoDups functions to insert the data in smart_meter table.

Listing 10. Load data using PutElem and PutElemNoDups functions
update smart_meter set meter_data = PutElem(meter_data,row
(NULL::datetime year to fraction(5),15,16,17,16)::meter_reading) 
 where meter_id = '902';
                
 update smart_meter set meter_data = PutElemNoDups(meter_data,row
 ('2012-01-01 00:45:00.00000',14,12,10,9)::meter_reading) 
 where meter_id = '902';

The row is appended with the next element in the TimeSeries according to the calendar interval. PutElemNoDups updates the row if the data already exists at the given time interval. Otherwise a new row is inserted.

Populating a virtual table using SQL INSERT statements

You can also insert the data using the virtual table interface. This approach allows you to make use of SQL Insert statements to load the data in the Informix TimeSeries. Listing 11 shows you the Insert statements to load the data in TimeSeries using virtual table.

Listing 11. Insert data via virtual tables
 insert into virtual_smart_meter values( 903, "2012-01-01 00:00:00"::
 DATETIME YEAR TO FRACTION(5),20,2,3,1);
 insert into virtual_smart_meter values( 904, "2012-01-01 00:00:00"::
 DATETIME YEAR TO FRACTION(5),20,2,3,1);
 insert into virtual_smart_meter values( 905, "2012-01-01 00:00:00"::
 DATETIME YEAR TO FRACTION(5),20,2,3,1);

TimeSeries new functions

DelRange

The Delrange function is the ideal function to use if you want to purge the TimeSeries data in any given range. DelRange removes the elements from the pages and also frees up the pages once they are empty.

The Deltrim function is similar to Delrange. The difference is that it removes the elements from the pages but doesn't free the pages once they are empty. Listing 12 shows the way to use the DelRange function.

Listing 12. Delrange function
 update smart_meter
 set  meter_data = DelRange(meter_data,'2012-01-01 00:00:00'::
 datetime year to fraction(5),'2012-01-01 00:15:00'::datetime 
 year to fraction(5))
 where meter_id =901

NullCleanup

NullCleanup is the ideal function to use if you want to free up the pages to which there are no elements written, or where elements have been deleted and pages have become empty. Listing 13 shows how to use the NullCleanup function.

Listing 13. NullCleanup function
 1)To free up the empty pages in given date time range  
 Update smart_meter set  meter_data =NullCleanup
 (meter_data,  '2012-01-01 00:00:00.00000'
 ::datetime year to fraction(5),
 '2012-01-01 00:15:00.00000'
 ::datetime year to fraction(5))
 WHERE meter_id = 901
                
 2) To free up the empty pages in total TimeSeries 
 Update smart_meter set  meter_data =
 NullCleanup(meter_data) 
 WHERE meter_id = 901

TsRollup

The TsRollup function allows you to summarize TimeSeries data. By using this function, you can aggregate TimeSeries data and make use of the returned TimeSeries value for analysis.

TstoXML

You can make use of the TSToXML function to view and export the TimeSeries data in XML format. The query to use TsToXML function and its output are shown in Listing 14.

Listing 14. TstoXML function
 SELECT TSToXML('meterdata', meter_id,
 Clip(meter_data,'2012-01-01 00:00:00'::datetime year to second,
 '2012-01-01 13:30:00'::datetime year to second ) )
 FROM smart_meter
 WHERE meter_id = '905';
                
 Output :
                
 (expression)  <meterdata>
               <id>905</id>
               <AllData>1</AllData>
               <meter_reading>
               <timestamp>2012-01-01 00:15:00.00000</timestamp>
               <vot>20.0</vot>
               <curr>2.0</curr>
               <pow>3.0</pow>
               <avg>1.0</avg>
               </meter_reading>
               </meterdata>

TimeSeries administration with Open Admin tool

Now you can use Informix Open Admin tool to easily administer TimeSeries. Informix Open Admin tool is the PHP-based, open source administration tool that allows you to administer local or remote databases. The TimeSeries plug-in of Open Admin tool allows you to view TimeSeries subtypes, containers, calendars, tables, and indexes that contain TimeSeries subtypes, as well as Virtual tables present in your database. It also allows monitoring space utilization by the container and creating and dropping containers, calendars, and virtual tables.

Use the following steps to work with the Informix TimeSeries plug-in of the Open Admin tool. The following figures use the demo database with the name stores. You can create a demo database using the command shown in Listing 15 at the command prompt.

Listing 15. Dbaccessdemo to create demo database
 dbaccessdemo stores -log;
  1. After installing Open Admin tool, open it in the browser by selecting All Programs -> Open Admin tool for Informix.
  2. Fill in the Informix Server name, Host Name, Port, Username, Password, and Protocol to connect to the Informix Server, and then click Login as shown in Figure 2.
    Figure 2. Login screen
    Figure shows Login Screen on OpenAdmin tool. It has                             Informix server details with  Informix server name, Host name, Port number, user name, password and Informix protocol entries which are required                       to plug in Informix server to Open Admin tool.
    Figure shows Login Screen on OpenAdmin tool. It has Informix server details with Informix server name, Host name, Port number, user name, password and Informix protocol entries which are required to plug in Informix server to Open Admin tool.
  3. Select TimeSeries under SQL Toolbox as shown in Figure 3.
    Figure 3. TimeSeries
    Figure shows TimeSeries Tab. To view the database(s) information that are having TimeSeries table(s), click the TimeSeries tab under SQL ToolBox tab on the left hand                      panel on the OpenAdmin tool
    Figure shows TimeSeries Tab. To view the database(s) information that are having TimeSeries table(s), click the TimeSeries tab under SQL ToolBox tab on the left hand panel on the OpenAdmin tool

    (View a larger version of Figure 3.)

  4. You now see the screen with databases having TimeSeries tables. Click the Stores database to see the Informix tables located inside it. Figure 4 shows you the TimeSeries subtypes, containers, calendar, tables, and index details of the Stores database. You can click on the individual tables to see the table details.
    Figure 4. TimeSeries details
    Figure shows TimeSeries Details. Click on the stores                             database to view TimeSeries tables that are already created. This screen would also list the details of TimeSeries subtypes, containers, calendar, tables and Indexes details of the stores database.                   To view the table(s)details click on individual tables
    Figure shows TimeSeries Details. Click on the stores database to view TimeSeries tables that are already created. This screen would also list the details of TimeSeries subtypes, containers, calendar, tables and Indexes details of the stores database. To view the table(s)details click on individual tables

    (View a larger version of Figure 4.)

  5. To create a container, click the Actions pull-down menu and select Create Container as shown in Figure 5.
    Figure 5. Actions
    Figure shows TimeSeries Action Tab. This provides the options to create and Drop Container, Calendar and virtual table
    Figure shows TimeSeries Action Tab. This provides the options to create and Drop Container, Calendar and virtual table

    (View a larger version of Figure 5.)

  6. Provide the container details that need to be added, as shown in Figure 6, and then click Next.
    Figure 6. Create container
    Figure shows TimeSeries Container Creation wizard. It                             needs the details like container name, Initial size, Increment size and TimeSeries Subtype
    Figure shows TimeSeries Container Creation wizard. It needs the details like container name, Initial size, Increment size and TimeSeries Subtype

    (View a larger version of Figure 6.)

  7. Figure 7 shows the dbspace details where the container needs to be added. Select the database and click Next.
    Figure 7. Dbspace details
    Figure shows Dbspace Details. It lists all the available dbspaces.
    Figure shows Dbspace Details. It lists all the available dbspaces.

    (View a larger version of Figure 7.)

  8. The SQL generated for review is shown in Figure 8. Click Finish to add the required container.
    Figure 8. SQL generated for review
    Figure SQL Generated For Review. It shows the TSContainerCreate procedure to create the Container.
    Figure SQL Generated For Review. It shows the TSContainerCreate procedure to create the Container.

    (View a larger version of Figure 8.)

  9. As shown in Figure 9, the container was added successfully. Click Done to go back to the main TimeSeries screen.
    Figure 9. Container is added successfully
    Screen shows container was added successfully
    Screen shows container was added successfully

    (View a larger version of Figure 9.)

  10. To view the individual tables under the Stores database, click the table name under the database. Figure 10 shows you the ts_data table details, as well as the virtual tables defined on this table.
    Figure 10. ts_data table details with virtual tables defined on this table
    Figure shows ts_data table details like owner of the table, in which dbspace it lies as well as virtual tables defined on this table
    Figure shows ts_data table details like owner of the table, in which dbspace it lies as well as virtual tables defined on this table

    (View a larger version of Figure 10.)

Performance considerations

The following guidelines may help you to improve performance of the TimeSeries feature.

  • Use one container per disk.
  • Put each container in a separate dbspace.
  • Create Row type with fewer columns so that they can occupy a single page.
  • Run Update Statistics for tsinstancetable and tsinstancetable(id) to improve the performance of subsequent load, insert, and delete operations.
  • After initializing TimeSeries, execute Update Statistics for best performance.
  • While inserting data into TimeSeries, set isolation to dirty read.
  • Set lock mode to row for TimeSeries and tsinstance table.

Conclusion

Native TimeSeries support in Informix simplifies the approach to deal with time-stamped data with the added advantage of storage savings and performance. This article has given you the information to understand basic Informix TimeSeries architecture, and showed you how to quickly start using it. It also covered the administration of TimeSeries using Open Admin tool.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=806949
ArticleTitle=Get started now with the Informix TimeSeries solution
publish-date=03292012