HCL Informix TimeSeries Part 2 – How to setup HCL Informix TimeSeries

                                                                                                            Updated: 8/30/18
​
This is the second part of this multi-part series. In this part we will take a closer look at setting up, configuring, and inserting data into HCL Informix TimeSeries.

Create Database Structures 
We’ll start by creating a database executing the following SQL statement to create a working database.  To keep things simple, we will create everything in the default dbspace (rootdbs). 

​A row type must be created and is used to describe and store the timeseries data.  It describes what type of data is stored in the HCL Informix TimeSeries. It is this HCL Informix extended data type that allows the concept of the table-inside-a-table.  Essentially a table in each row of a traditional database as shown in Part 1 of this series.
 
The following SQL statement is used to create a row type, with this row type supporting a single integer value.

The row type must have a timestamp column defined as a datetime year to fraction(5).  This is necessary for HCL Informix TimeSeries.  The rest of the columns defined here in the row type describe the structure of the HCL Informix TimeSeries data.
 
Next you create a table using the newly defined row type.

Create a Virtual Table Interface (VTI)
HCL Informix TimeSeries data is stored differently and accessed differently than traditional relational database tables.  HCL Informix provides a mechanism to interact with timeseries data as if it were relational data.  We will use the virtual table interface  which makes your HCL Informix TimeSeries data look and behave like a relational table. It also allows you to query and insert data as if the timeseries is a relational table.
 
Use the following SQL statement to create a virtual table interface for the blog table with timeseries data.

Insert TimeSeries Data
We now have a timeseries table created and a virtual table interface for the same table. 
 
Now insert a few rows of data into the HCL Informix TimeSeries table.  We’ll do this through the VTI table for convenience.
 
Run the following SQL insert statements.

Running a select against the blog_vti table will now produce results that look similar to what is shown below. 

One thing you may notice here is that there are 5 insert statements listed above, yet we see only 4 values that got inserted.  Notice that insert with the time 2017-07-01 00:19:00.00000 did not get inserted.  This is because the timeseries that we had created is a regular timeseries on 15 minute intervals.  We will talk more about this in a future blog post.
 
For more detail on the commands shown in this article see the HCL Informix Knowledge Center for 12.10.  https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.welcome.doc/welcome.htm

In the next part, we will learn about Regular and Irregular TimeSeries in greater detail.

​Darin Tracy
Software Engineer
HCL Informix
Connect with me on LinkedIn

Informix is a trademark of IBM Corporation in at least one jurisdiction and is used under license.