What's new in TimeSeries data for Informix, Version 12.10

Data type support in expressions in time series functions is enhanced in the following ways:

• You can now include BSON field values in expressions in the CountIf and Apply functions. BSON fields are extracted as FLOAT values by default, but you can explicitly cast BSON fields to an INTEGER, BIGINT, or LVARCHAR data type.
• You can now include INVERVAL values, DATETIME values, and Boolean values in expressions in the CountIf function.
• You can now define a calendar to specify the window interval for partitions in a rolling window container. The TSContainerCreate function allows a calendar name from the CalendarTable table in the window_interval argument.

Apply function

TSContainerCreate procedure

You can now use advanced analytics functions to analyze time series data for patterns or abnormalities:

• Quantify similarity, distance, and correlation between two sequences using the Lp-norm, Dynamic Time Warping, or Longest Common Subsequence method.
• Search based on specific measures like similarity, distance, and correlation. Find the portions of a sequence which are related to a given pattern.
• Detect anomalies within time series data. Given a long time series sequence, anomaly detection provides the ability to tell which part of the time series is dramatically different from the portion of data nearby in time order.

If you have a spatiotemporal subtrack table created for a time series, you can trigger the indexing of spatiotemporal data when you load time series data. Set the writeflag parameter to 512 or 1024 in the TSL_Commit, TSL_FlushAll, or TSL_Flush function to trigger spatiotemporal indexing as time series data is saved to disk.

TSL_FlushAll function

TSL_Flush function

You can now load JSON documents into Informix databases with the MQTT protocol by defining an MQTT wire listener. The MQTT protocol is a light-weight messaging protocol that you can use to load data from devices or sensors. For example, you can use the MQTT protocol to publish data from sensors into a time series that contains a BSON column. You can also use the MQTT protocol to publish data to relational tables or JSON collections.

You can now create a pattern match index or run a pattern match search on a field in a BSON document. The BSON document must be in a BSON column the TimeSeries subtype and the field must hold numeric data. Run the TSCreatePatternIndex function or the TSPatternMatch function and specify the BSON column and field name.

You can now extend the time range of an existing pattern match index to incrementally update the index. You can extend the index time range in either direction, or both directions, but the existing and new time ranges must overlap. Run the TSCreatePatternIndex function with new beginning or ending times.

TSPatternMatch function

You can limit the number of partitions for future dates in rolling window containers. Limiting partitions for future data protects the partition for current data from being destroyed. When you create rolling window containers with the TSContainerCreate function, you can now specify the maximum number of future partitions. You can limit future partitions for existing rolling window containers with the TSContainerManage function.

TSContainerManage function

You can now enter whole-second blocks of hertz records into a time series out of chronological order. For example, if a time series is missing data for an entire second in the past, you can enter the data. However, you must enter subsecond elements within a second in chronological order.

You can now store hertz data in rolling window containers.

The fragmentation scheme for virtual tables is adapted from the fragmentation scheme of the base table. In some cases, the resulting virtual table creation statement is significantly longer than the original table creation statement. If the virtual table creation statement fails because it exceeds the maximum length of an SQL statement, you can specify one of the two new fragmentation flags in the TSVTMode parameter of the TSCreateVirtualTab procedure. If the fragmentation clause for the base table has expressions that each map to a single fragment and does not have a REMAINDER clause, use the fragment_verbatim flag. If the fragmentation clause for the base table has ambiguous expressions or a REMAINDER clause, use the fragment_with_case flag.

You can now see the time series reference count in the metadata section of the output of the TSInfo function. The time series reference count is the number rows in time series tables that reference the same time series data in a container.

You can now specify NULL instead of a dbspace name when you create containers with the TSCreateContainer procedure. The container is created in the same dbspace as the time series table, or, if the table is in a tenant database, the dbspace for the tenant database catalogs.

You can now load data that is entirely in JSON documents directly into time series. Previously, you had to provide primary key values and time stamps in plain text format. Run the new TSL_PutJson() function to load pure JSON documents. For example, you can load the JSON documents that are generated by wireless sensor devices without preprocessing the data.

You can load JSON documents from a file or from a named pipe.

Example for JSON data: Create and load a time series with JSON documents

You can search time series data for matches to a specific pattern of values. For example, if you identify a sequence of four values that indicate a problem, you can search of other sequences of four values that are similar to the original sequence of values. You run the TSPatternMatch function to find pattern matches. You specify the margin of error and whether to search through consecutive sequences of values or through every possible subsequence of values.

You can create a pattern matching index to improve query performance by running the TSCreatePatternIndex function.

You can now quickly load files directly into the database by specifying a file path as the second argument to the TSL_Put function. Previously, the TSL_Put function accepted data as only LVARCHAR or CLOB data types, which require intermediate steps to process the data.

The time series data that you load with the TSL_Put function can now contain JSON or BSON documents as values for columns other than the primary key and time stamp columns. Unlike the TSL_PutSQL function, when you load JSON columns with the TSL_Put function, you do not need to create an external from which to load the data.

You can extract data between two timepoints in a time series and return a new time series that contains only the specified columns of the original time series. Run the new ProjectedClip function to clip time series data from only the columns of the TimeSeries data type that you specify.

The data that you load into your time series might be configured to store a null value when a value does not differ from the previous value. If you have a low frequency of nonnull values, you can replace null values with the previous nonnull values in the output time series:

• Replace only the first value for a column, if that value is null. Append (lf) to the column name in the column list to designate a low frequency column.
• Replace all null values with the corresponding previous nonnull values. Append (nn) to the column name in the column list to designate a column with no null return values.

When you run a time series loader program, you open a loader session for each table and TimeSeries column combination into which you load data. You can view a list of handles for active loader sessions by running the TSL_ActiveHandles function. The handle consists of the table name and the TimeSeries column name.

You can find the amount of storage space that is used by a time series by running the new TSInfo function. You can customize the level of detail of the information. For example, you can display details about element pages, such as the number of pages, the number of bytes, the amount of free space, and the number of null pages. You can also return information about other properties of a time series, such as the origin, the type of values, and containers.

If you write a loader program to load time series data, you can choose to retrieve loader messages from a queue instead of logging the messages in a message log file. Retrieving messages from a queue results in less locking contention than logging messages in a file.

You can retrieve queued messages as formatted message text in English by running the new TSL_GetFmtMessage function.

Alternatively, you can run the TSL_GetLogMessage function to return message numbers and then run the new TSL_MessageSet function to return the corresponding message text. This method is useful if you want to provide your own message text or if you want to retrieve message text on the client.

TSL_MessageSet function

TSL_GetLogMessage function

You can now create a new time series instance while loading data with a time series loader program. Previously, you had to insert primary key values and create time series instances before you loaded data with a loader program.

For a loader program, you can specify the definition of a time series instance by running the new TSL_SetNewTS function. You can specify whether the time series definition applies to the current loader session or to all loader sessions. When you load data with a TSL_Put function for a new primary key value, a new row is added to the table and a new time series instance is created based on the definition.

For a virtual table, you can create a new time series instance while quickly inserting elements into containers. In the TSCreateVirtualTab procedure, set the NewTimeSeries parameter and the elem_insert flag of the TSVTMode parameter.

You can automatically set the origin of any new time series instance to the day that the time series is created by including formatting directives for the year, month, and day. You can include formatting directives for the origin in the time series input string in an INSERT statement or in the NewTimeSeries parameter in the TSL_SetNewTS function and the TSCreateVirtualTab procedure.

The TSVTMode parameter

Time series input string

When you write a Java application with the time series Java class library, now you can define time series objects with the new builder classes. Previously, you defined time series objects with string representations of SQL statements. Builder classes reduce the possibility of errors and improve usability. The methods in the Java class library run faster than in previous releases.

The time series Java class library has the following enhancements for creating time series objects:

• You can now determine whether the definitions of two calendars or calendar patterns are the same.
• You can create calendar patterns and calendars with new IfmxCalendarPattern.Builder and IfmxCalendar.Builder classes.
• You can create and manage containers with the new TimeSeriesContainer and TimeSeriesContainer.Builder classes.
• You can create TimeSeries row types with the new TimeSeriesRowType and TimeSeriesRowType.Builder classes.
• You can create a simpler custom type map that uses a PatternClassMap instead of individual entries for each data type with the new TimeSeriesTypeMap and TimeSeriesTypeMap.Builder classes.

The IfmxTimeSeries class has the following enhancements for managing time series data:

• You can insert data into a time series with the new IfmxTimeSeries.Builder class.
• You can easily modify data and process query results because the results of queries on time series data are now JDBC updatable result sets.
• You can distinguish between case sensitive and case insensitive databases and make multiple updates within a row.
• You can convert the time series data to the appropriate time zone on the client.
• You can select and update data by specifying similar data types instead of the exact data types. Data is implicitly cast during read and write operations. Previously, transactions that did not specify the exact data types failed.

You can include JSON documents that are associated with timestamps in time series. For example, weather monitoring sensors that return 2 - 50 values in JSON documents through the REST API every 10 minutes. You store JSON documents with time series data as BSON documents in a BSON column in the TimeSeries data type.

Example for JSON data: Create and load a time series with JSON documents

If you have applications that handle time series data, you can now create and manage a time series with the REST API or the MongoDB API. Previously, you created a time series by running SQL statements. For example, you can program sensor devices that do not have client drivers to load time series data directly into the database with HTTP commands from the REST API.

You create time series objects by adding definitions to time series collections. You interact with time series data through a virtual table.

Create time series through the wire listener

You can now replicate hertz and compressed time series data with Enterprise Replication.

You can save disk space by packing multiple time series records in each element. If your data is recorded with a regular subsecond frequency, you can define a hertz time series to pack records for a second of data in each time series element. If all the columns in your TimeSeries data type are numeric, you can define a compressed time series to pack and compress up to 4 KB of records in each time series element.

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

Example for compressible data: Create and load a compressed time series

Time series SQL routines that you include in the WHERE clause of SELECT statements return results faster when they run in parallel. If you fragment the table that contains the time series data and enable PDQ, time series SQL routines run in parallel.

You can limit the number of partitions of a rolling window container that can be destroyed in an operation. You control how many partitions are destroyed and whether active partitions can be destroyed when the number of partitions that must be detached is greater than the size of the dormant window. When you create a rolling window container, set the destroy_count parameter to a positive integer and the window_control parameter to 2 or 3 in the TSCreateContainer function. You can change the destroy behavior of an existing rolling window container by including the wcontrol parameter in the TSContainerManage function.

TSContainerManage function

You can monitor groups of containers that have similar names. Include the wildcard characters for the MATCHES operator in the parameter for the container name in the TSContainerUsage, TSContainerTotalPages, TSContainerTotalUsed, TSContainerPctUsed, TSContainer, and TSContainerNElems functions.

TSContainerTotalPages function

TSContainerTotalUsed function

TSContainerPctUsed function

TSContainerNElems function

Access methods that are created through the virtual table interface now process IN conditions in query predicates that operate on simple columns. Processing through a virtual table interface is generally faster than SQL processing. For example, queries with IN conditions that you run on time series virtual tables now run faster than in previous releases.

You accelerate queries on time series data by creating data marts that are based on time series virtual tables.

You can define virtual partitions so that you can quickly refresh the data in part of the data mart or continuously refresh the data. You can make queries faster by limiting the amount of data in the data mart to specific time intervals.

Performance of queries on virtual tables

You can run queries in parallel on a virtual table that is fragmented. The virtual table must be based on a time series table that is fragmented by expression. Include the fragment flag in the TSVTMode parameter when you create the virtual table.

You can include the flags for the TSVTMode parameter as a set of strings instead of as a number.

The TSVTMode parameter

You can now replicate time series data with all types of high-availability clusters. Previously, you could replicate time series data only with High-Availability Data Replication (HDR) clusters, and not with shared-disk secondary and remote stand-alone secondary clusters. Secondary servers must be read-only.

You can include a TimeSeries column in an ORDER BY clause of an SQL query. The ORDER BY clause sorts the results from the TimeSeries column by the time series instance ID.

You have new options for how you flush time series data to disk when you write a loader program. You can flush time series elements for all containers to disk in a single transaction or in multiple transactions. If you want your client application to control transactions, run the TSL_FlushAll function. The TSL_FlushAll function flushes time series elements to disk in one transaction. If you want the loader program to control the size of your transactions, run the TSL_Commit function. The TSL_Commit function flushes time series elements to disk in multiple transactions, based on the commit interval that you specify.

You can view the results of the data flushing function by running the TSL_FlushInfo function.

You can specify that no duplicate elements are allowed when you flush time series data to disk.

TSL_Commit function

TSL_FlushInfo function

You can aggregate an interval of time series data faster by including start and end dates in the TSRollup function. Previously, you selected an interval of time series data with the Clip or similar function and passed the results to the TSRollup function.

You can control the amount of time-series data that is stored in containers by specifying when to delete obsolete data. You create a rolling window container that has multiple partitions that are stored in multiple dbspaces. You configure a rolling window container to define the time interval for each partition and how many partitions are allowed: for example, 12 partitions that each store a month of data. When you insert data for a new month, a new partition is created, and if the number of partitions exceed the maximum that is allowed, the oldest partition becomes dormant. You specify when to destroy dormant partitions. Previously, you had to delete obsolete data manually.

Rules for rolling window containers

If you load time-series elements into containers in a single transaction, you can save time by specifying a reduced amount of logging. By default, every time-series element that you insert generates two log records: one for the inserted element and one for the page header update. However, you can specify that page header updates are logged for each transaction instead. For example, you can insert a set of daily meter readings for a meter in one transaction and reduce the amount of logging by almost half.

Run one or more of the PutElem, PutElemNoDups, PutNthElem, InsElem, BulkLoad, or PutTimeSeries functions with the TSOPEN_REDUCED_LOG (256) flag or the TSL_Flush function with the 257 flag within a transaction without other functions or SQL statements. If you insert data through a virtual table, run the TSCreateVirtualTab procedure with the TS_VTI_REDUCED_LOG (256) flag, and then insert data within a transaction without other types of statements.

The TSVTMode parameter

You can replicate time-series data with Enterprise Replication. For example, if you collect time-series data in multiple locations, you can consolidate the data to a central server.

By default, multiple sessions can now write to a time-series container simultaneously. However, you can limit the number of sessions to one. Data is loaded faster if only one session writes to the container. Use the TSContainerLock procedure to control whether multiple sessions are allowed. Previously, you wrote your application to prevent more than one session from writing to a container at one time.

You can use time-series SQL routines to write a custom program that loads time-series data into the database. You can load data in parallel in a highly efficient manner by controlling what data is loaded into which containers. You can include a custom loader program in your application.

When you use the Informix TimeSeries Plug-in for Data Studio, you can load time-series data into an database directly from another database. You do not have to export the data into a file. When you create a table definition, specify a connection to a database and a query to return the data that you want to load. You can preview the returned data to validate the query. You can also set other properties of the load job within the plug-in.

You can return the timestamp of the first or last element in a time series by running the GetFirstElementStamp function or the GetLastElementStamp function. You can choose whether the element can be null or must contain data. For example, you can return the first element that has data to determine the number of null elements between the origin and the first element that has data.

GetLastElementStamp function

Queries on time series virtual tables now run faster because qualifiers to a WHERE clause that contain multiple column, constant, or expression parameters are processed through the virtual table interface instead of through SQL processing.