In the last few blog entries, I've been talking about TimeSeries. This time, I'd like to diverge a little for a change. Still there is a tie to TimeSeries

About a year ago, I went to a E&U conference. As you may know, Informix is making a push in this industry due to the advantages that TimeSeries can provide to this industry. In one of the sessions I attended, the presentor mentioned in passig the "Did you know?" video on youtube. Just the context when it was mentioned made me pay attention. I took a note and decided to look it up later. Last time I checked, it had had over 14 million viewing!

"Did you know?" starts with a global view of the world ("If you are 1 in a million in China, there are 1300 people just like you") and continues to talk about the evolution of the impact of technologies on our lives and its impact in the future.

Some other highlights:

• the top 10 in-demand job in 2010...did not exist in 2004
• (dated!) 200 million register users in MySpace would represent the 5th largest country in the world (What about facebook?!?)
• It too the radio 38 years to reach an audience of 50 million people, it toook television 13 year and facebook 2 years
• It is estimated that 4 exabytes of unique information will be generated this year. This is more than the previous 5000 years

Like it says in the video, we are living in exponential times.

Take a look at it, it's only 5 minutes of your time: http://www.youtube.com/watch?v=cL9Wu2kWwSY

We left off with an insert through the virtual table view. We created a container, a row type, a table, and a virtual table. What if we could simplify this? What if we could avoid creating a container?

One reason why you don't want to create containers could be that you have a lot of data to load and you would need a lot of containers. Would it be nice if Informix could help you with that? Informix can! In the Informix 11.70.xC3 release, we added a capability that does just that.

The new feature if referred to as auto create container. When you insert a new time series in a table and no container is specified, Informix will create one for you if needed. For example, let's take the following table:

CREATE TABLE jroy (
loc_esi_id char(20) NOT NULL PRIMARY KEY,
raw_reads timeseries(meter_data)
) LOCK MODE ROW;

WE can insert a new TimeSeries without specifying a container:

INSERT INTO jroy VALUES(1, "origin(2010-11-10 00:00:00.00000),calendar(tst15min),threshold(0),regular,[]");

If there is no container available, a container is created as we can see in the tscontainertable table:

SELECT * FROM tscontainertable

name autopool00000000
subtype meter_data
partitiondesc autopool00000000 datadbs 16 16 4194538
flags 1
pool autopool

This features goes a few steps further. If the table is partitioned over multiple dbspaces, Informix will create one container per dbspace and put them in a pool called autopool. It is possible to have the following inserts go through the pool in a round robin fashion to evenly distribute your time series over multiple container and dbspaces.

If you prefer to manage your containers tourseld, you can create your own containers and ut them in a specific pool so you can take advantage of a container pool. You can even create your own policy to decide where new time series should be located.

There is more to know about these capabilities. You can find out more in the information center starting at:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.tms.doc/ids_tms_368.htm?resultof=%22%63%6f%6e%74%61%69%6e%65%72%22%20%22%63%6f%6e%74%61%69%6e%22%20

Informix TimeSeries is a specialized storage and retrieval mechanism that optimizes the processing usually done on this type of information. For this reason, it includes specialized storage called "container". A container is created in a dbspace. In fact, multiple containers can be created in a dbspace. A container is created using the TSContainerCreate procedure:

EXECUTE PROCEDURE TSContainerCreate('meter_cont', 'datadbs','meter_data', 0, 0);

This command creates a new container called meter_cont in the datadbs dbspace. It is created specifically for time series elements of type meter_data (row type). Since we are talking about a row type, it could include anything a row type accepts. The only restriction is that the first column has to be a datetime year to fraction(5). Here's a simple row type that could be used:

CREATE ROW TYPE meter_data (
tstamp datetime year to fraction(5),
value decimal(14,3)
);

The last two arguments represent the initial space allocation and the growth space allocation. This is similar to initial extend and next extend. A value of 0 resolves to the default of 16KB.

With this in place, we can create TimeSeries in a table. Let's start with the following definition:

CREATE TABLE ts_data (
id int PRIMARY KEY NOT NULL,
raw_reads TimeSeries(meter_data)
);

We can insert a row in a table with an empty TimeSeries as follows

INSERT INTO ts_data VALUES(1, "origin(2011-07-27 00:00:00.00000),calendar(ts_15min),container(meter_cont),threshold(0),regular,[]");

We now have a row in the table with an empty TimeSeries column. This is different from NULL: The TimeSeries exist but has no elements in it.

Now, you may say: "Whoa! How do I insert data in that TimeSeries? Must be difficult".

The TimeSeries functionality includes a way to create a relational view on a table that contains a TimeSeries column. If the table were to include multiple TimeSeries column, you could create multiple "views", one for each TimeSeries column. This capability is provided through an Informix feature called the Virtual Table Interface (VTI). This is a capability that allows Informix users to make something look like a standard relational table. At this point, there is no need to describe this interface further. The Informix TimeSeries provides a stored procedure that facilitates the creation of that virtual table. For example, we can create a relational view on out ts_data table as follows:

EXECUTE PROCEDURE TSCreateVirtualTab('ts_data_v', 'ts_data',
'origin(2010-11-10 00:00:00.00000),calendar(cal15min),container(raw_container),threshold(0),regular', 0, 'raw_reads');

This creates a virtual table called ts_data_v on the table ts_data and referring to the TimeSeries column named raw_reads. The character string expression in the middle starting with origin provides information to the interface about what to do if a new row is inserted and a TimeSeries needs to be created. After the executio of the procedure, you will have this new table that effectively has the following definition:

CREATE TABLE ts_data_v (
id int PRIMARY KEY NOT NULL,
tstamp datetime year to fraction(5),
value decimal(14,3)
);

If you want to insert into a timeseries, you simply use a standard insert statement. If the row does not exist, it gets created, if it is there, the TimeSeries column gets updated. Here's a simple insert example:

INSERT INTO ts_data_v
VALUES (2, "2011-08-10 08:25:00.00000"::DATETIME YEAR TO FRACTION(5), 12.5);

A simple standard SQL insert... How easy can it be?

We have a lot more to talk about. Next time, we'll start introducing some 11.70.xC3 capabilities. This is starting to get exciting! See you next time