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 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 Informix TimeSeries. It is this 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 Informix TimeSeries. The rest of the columns defined here in the row type describe the structure of the Informix TimeSeries data.
Next you create a table using the newly defined row type.
Create a Virtual Table Interface (VTI)
Informix TimeSeries data is stored differently and accessed differently than traditional relational database tables. 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 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 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 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.
Connect with me on LinkedIn