The information provided applies to the new Informix Server and IWA Version 12.10.FC2.
The latest Release 12.10.xC2 of Informix Server and the Informix Warehouse Accelerator (IWA) a few days ago contains two new features. Here is a brief description:
For detailed information on how to use external tables in a data mart, please refer to the topic Load data from external tables in the manual.
New functions in the IWA Admin SQL-API to handle TimeSeries data
The amount of TimeSeries data often is huge, and new data gets added almost continuously (e.g. new smart meter readings may arrive as frequently as every 15 minutes). But often it is not necessary to have the complete history of TimeSeries data available for analysis. Many times, only a certain period of time (e.g. the last 24 or 12 months, the last quarter, the days of the current year, etc.) is of interest. For the data mart, this means that new data should be added frequently, and perhaps old data should be removed at the same time.
The new functions in the IWA Admin SQL-API use the concept of a 'time window' that limits the TimeSeries data to be included in a data mart. This 'time window' can be defined either by using a TimeSeries calendar, or by manually specifying begin and end times. Based on the defined 'time window' the TimeSeries data can be refreshed in the data mart. The new functions are:
For detailed information on these functions, please refer to the topic SQL administration routines sorted by task in the manual.
Besides the above reference descriptions, the manual also contains two extensive examples that demonstrate how these functions can be used in practice:
Support for external tables in a data Mart
Data that is interesting to analysis might not always be stored in the data warehouse or the OLTP database that serve as basis for data marts. Structured data from diverse sources can be integrated by using the external table capabilities of Informix. With Veriosn 12.10.xC2 it is now possible, to use such data in external tables in an IWA data mart for query acceleration. This has several advantages:
- Data in existing external tables does not need to be loaded into normal tables.
- Structured data in ASCII-delimited flat files can be loaded across platforms.
- When loading a data mart, data of a single external table can be read in parallel from multiple files.
- By using named pipes for external tables, storing the data in a file (on disk) can be avoided.
- When using named pipes, additional pre-processing of the data (transforming, cleansing, etc.) can be done without storing the intermediate results on disk.
- Utilization of external and normal tables can be mixed in a data mart as well as in queries.
- Accelerated queries can access more than only one external table in a single query.
Creating a data mart by workload analysis and query probing (aka "SmartMart" feature) is supported for external tables. Performance of loading data from external tables into a data mart equals the load performance with normal tables.
Also supported is the functionality to load partitions of tables into an existing data mart. This can be used with external tables based on pipes to load new data. From the external side, just the new data is fed into the pipe. As only the new data appears in (the pipe of) the external table, ifx_loadPartMart() can be called to load all the data of the external table, which thus will add the new data to the data mart.
With the external data actually loaded into the data mart, IWA has all the data available for query execution. This means that IWA is not restricted to queries that use only a single external table, as it is the case for the Informix server. Instead, the whole data mart can be created from external tables only, and acceleratable queries are free to use all the external tables as if they were normal tables in the Informix server, joining them seamlessly. In fact, this makes IWA capable of universially accelerating analytic queries on structured data from almost any source, without the requirement to actually store the data on disk (in the Informix server).
Informix server and IWA used to accelerate queries on external data
Because Informix Server has no control over the data in an external table and how it may change, some restrictions apply when using external tables in a data mart:
- Uniqueness of the primary key values in a parent table (a prerequisite for 1:n references) cannot be guaranteed by the Informix server. The responsibility for this is with the DBA, who can create a disabled index on external tables. This will then permit 1:n references in the data mart.
- The Informix optimizer needs to determine the fact table of queries, both for query probing as well as query matching for acceleration. The optimizer might need the number of data rows in a table for this task (provided by the table statistics). To facilitate determining the fact table for external tables, the (approximate) number of rows must be specified when creating the external table, using the NUMROWS option. (Alternatively, the fact table can be specified explicitly for queries using optimizer directives.)
- There is no locking for external tables. Therefore data mart load operations must use the lockmode NONE if an external table is part of the data mart.
- If columns of data types DATE or MONEY of an external table are to be included in a data mart, then the format of these columns must be specified when creating the external table. This is necessary for the data mart loading operation to correctly interpret the data from the external table. E.g. for a DATE column with values of the format 2013-06-30 in the external table, DBDATE 'Y4MD-' must be specified as table option.
- Because the Informix server has no control on the data in an external table, it cannot collect any statistics on how it is changed. Therefore, features automatic data mart refresh and trickle feed are not supported for data marts containing data from external tables.
Reminder: The URL to the now updated manual IBM Informix Warehouse Accelerator Administration Guide, Version 12.10 is: