I'm traveling this week to talk to multiple people about TimeSeries. It should be a good week!
Big data in motion
I added a new video on the "Informix Smart Meter Central" wiki. It is a recording of a live demo accessing the TimeSeries data provided in the stores demo database in Informix 11.70.xC3 or higher. Some of you may have already installed that demo in their environment since the code is available on the wiki but there is a new twist. I added a section that shows the use of spatial and TimeSeries. It is demonstrated using a google map of the bay area (San-Francisco bay area in case you wonder). You can find this video with other videos on the collaterals page:
I think that makes for a nice addition to the demo. It will eventually make its way to the demo code on the wiki.
Take a look at the videos, I hope you enjoy them.
I'm traveling this week to talk to multiple people about TimeSeries. It should be a good week!
I've been silent for quite a while. That does not mean I have not been busy!
A lot of efforts has been put on TimeSeries over 11.70.xC3 and 11.70.xC4 and we are still going full steam ahead. We continue to improve its performance, scalability, usability and functionality.
I wanted to put together a repository of information so people can find it all (or most of it in one place. For this purpose, I put together a wiki on developerWorks that is dedicated to The smart meter support. It is still a work in progress but I believe it is a good start. you can find it using the tinyurl: tinyurl.com/InformixSmartMeterCentral.
Let me know what you think.
JacquesRoy 120000A2MS 1,230 Visits
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:
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
JacquesRoy 120000A2MS 1,666 Visits
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,
) 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
partitiondesc autopool00000000 datadbs 16 16 4194538
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:
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:
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:
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:
We can insert a row in a table with an empty TimeSeries as follows
We now have a row in the table with an empty TimeSeries column. This is different from
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:
This creates a virtual table called
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:
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
JacquesRoy 120000A2MS 1,274 Visits
In 11.70.xC3, we added some new time series capabilities. Why would you care?
Time series are found everywhere. It is simply data that is collected over time. It could be changes in stock price and transaction volumes. It could also be reading of your house electric meter. Readings could be done every 15 minutes for example to provide a much more accurate picture of how electricity is being used. Other time series examples include weather information, network traffic, thermal readings in a large data center, and so on.
One key characteristic of time series is that the processing always include a time component. For example, you want to get all the meter readings for one month for a specific customer. With this data, you can calculate daily consumption, running averages, etc. To do this type of processing, you need quick access to the specific range of data you want to analyze and you also need to get it in time order.
Informix provides a data type that is used specifically to optimize time series data. It also comes with a extensive set of functions used to manipulate these time series. The Informix TimeSeries provide three major benefits:
Informix TimeSeries also provides the ability to create relational views on top of your time series data. This opens the door to the use of standard off the shelf products to do things like reporting.
With this very brief introduction, we are now ready to talk about the improvements made in 11.70.xC3. This will have to wait until next time
JacquesRoy 120000A2MS 934 Visits
Informix often adds features in fixpacks and xC2 and xC3 are no exception. I strongly suggest that you take a look at the list of new features that are listed in the release notice. You can find it at:
Release notice 11.70.xC3
In my next few blog entries I will not cover all the new features. I will limit myself to two main areas:
For anything else, see the release notice and the Informix documentation. The easier way to do this is to use the information center that can be found at:
This release added a few compatibility function that makes it easier to move application to informix. They include:
Take a look at the details of these functions. I'm sure you will find a good use for them.
JacquesRoy 120000A2MS 779 Visits
July 1st was the 10 year anniversary of the IBM acquisition of Informix. Since the acquisition, Informix has releases version 9.3, 9.4, 10.0, 11.10, 11.15, and 11.70. A few days ago, we releases 11.70.xC3. Other recent addition include the Informix Warehouse accelerator that introduce game changing technology for the data warehouse/data mart area. Add to that the Informix-Genero for fast application development and mobile applications.
So much has happened in these 10 years. Go take a look at Informix. Download one of the free edition and give it a try. For people that think they know Informix, go take a look at the large number of improvements we've added to it over the years. Go visit www.ibm.com/software/data/informix and find out more about what is going on. The IBM Information on Demand conference is coming up. This is the best way to learn about the latest capabilities and network with Informix partners, customers, and IBMers. The conference is held in Las Vegas October 23-27.
Now that my major deliverables are done in xC3, I'll be back regularly to talk to you about these major improvements and how many people can take advantage of them.
Ten years with IBM and going strong. There is so much more to come. Stay tuned!
JacquesRoy 120000A2MS 1,046 Visits
Hello everyone, I've been buries in development activities for the last many months. That explains why I've neglected my blog lately. I think I see the light at the end of the tunnel. Hopefully it's not a train...
One thing that will make me come out of my cave is the IIUG Informix conference.That's a great event that takes place this year in Overland Park, KS between May 15 and May 18. As usual, this is the perfect opportunity to network with other Informix enthusiasts and the Informix lab people. The architects will be there in force, make sure to let them know what you like and what you want. Also, we should not forget the great sessions you can attend. A lot of effort has been put into this. I'm sure everyone will enjoy the conference.
While you wait for the conference to learn more abot Informix, you could also read some of the technical articles published recently such as:
There is more so stay tuned.
JacquesRoy 120000A2MS 1,107 Visits
It's hard to believe that we are already at the end of March. Seems like it should still be January. According to my blog, it still is January! I better get on with it!
Informix just came out with version 11.70.xC2. No big deal you may think. Wrong. It is a big deal! With xC2 we are making available a new edition: IBM Informix Ultimate Warehouse edition.
I'll be talking about this at the France Informix Users group next Monday. With this, queries that take hours now could take minutes. Some queries end up perfroming 100 times faster! For more information Look at:
And that's not all. We've been looking at ways to help our 4GL customers modernize their environment for years. We want customers to get more value out of their 4GL code and new application developments. the result: Informix Genero. Find out more at:
Stay tuned. The year is barely starting.
JacquesRoy 120000A2MS 1,878 Visits
2010 has been a great year with the release of Informix 11.70 and 2011 is lining up to be a busy year with plenty of activities and execution on the plans of v.next. I also hope that in 2011 we'll see even more participation from the Informix community to continue to make Informix and the solutions around it better and more exciting.
Part of making Informix and its ecosystem system better is to share ideas and be exposed to ideas that may or may not be related to Informix. If you remember, in my blog entry of October 9, I talked about where good ideas come from. It is time that I divulge the source of these comments. It came from a site called TED. That specific presentation is Where good ideas come from
Here are a few other presentations I enjoyed from www.ted.com:
There are many more interesting talks in there. I hope you'll enjoy these short presentations. Who knows, by exploring these presentations and others, you may come back with a new outlook on how we can use Informix to make our world a smarter planet.
Someone asked me the following question:
"How do I keep passwords in the database so nobody can get them?"
It means that we cannot keep the the passwords in plain text in the database. Informix has a few functions that can be used for encryption: ENCRYPT_AES and ENCRYPT_TDS. It would be easy to create a table and encrypt the column that contains the passwords.
The next statement that came up was: "..but, if someone has the encryption password, he can get all the passwords. We need to protect the passwords from internal access".
This means that we need to use a different password to protect each password in the table. The solution I proposed was to use the password to encrypt itself. Let's look at an example:
CREATE TABLE passwd (
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Jacques", "Jacques"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Lance", "Lance0"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Daniel", "Daniel"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Umut", "Umut01"));
The values inserted look as follow:SELECT * FROM passwd
I can now test f someone has the right password for user 1 by using the password value to decrypt itself:SELECT col1, DECRYPT_CHAR(col2, "Jacques") FROM passwd WHERE col1 = 1;
If I use the improper password, I receive an error:SELECT col1, DECRYPT_CHAR(col2, "Jacques") FROM passwd WHERE col1 = 3;
26008: The internal decryption function failed
One more thing. Note that the encryption password must be at least six-character long. This is why in the example I padded some encryption passwords. An easy way to work around it would be to always add padding to make sure we meet that minimum size. Keep in mind that the maximum size of an encryption key is 128 bytes.
With this approach, we can keep passwords in the database and keep them secret.
JacquesRoy 120000A2MS 2,746 Visits
Wednesday started with an Informix "eat and meet" breakfast followed by nine different Informix sessions spread throughout the day. My favorite session was: "How Hildebrand and IBM bring smart metering to homes across Britain". It was very interesting to see a real-time system where people can see their power consumption and compare it to a pool of similar housed to see how they are doing. The system does not only measure the total consumption at a home but can break it down to specific outlets. For example, some people were able to find out that their energy consumption was greatly impacted by their use of hair straitening devices. Another person could find out that they spent around 250 pounds per year to run their old refrigerator. Buying a new one for 200 pounds made it pay for itself pretty quickly.
Of course, the other presentations were also interesting. They covered areas such as building data warehouse, grid-based replication, Informix in the cloud and more.
An additional 11 sessions were held on Thursday to wrap up the conference.
The one thing that is hard to measure at a conference like this is the value of the interactions with other people. Discussions on different interests and new challenges, and also how Informix has been used. This ties into what I mentioned in this blog on Oct 9. Good ideas come form people interactions. The conference provided a good environment for that. This was a great conference and you can expect interesting things coming out of the Informix lab in the future. I'm sure we'll have a lot to say next time we meet: The International Informix Users Group (IIUG) conference in Overland Park, Kansas, that will be help between May 15 and 18, 2011.
JacquesRoy 120000A2MS 960 Visits
Another year, another conference. It has been so busy that I have not had the time to write a short blog entry for each day. Here is my quick update.< /p>
It all started Saturday morning with the business partner council and the customer advisory council on Saturday. I attended the customer advisory council and I found it interesting and full of good discussions.
The conference was kicked off with an opening reception on Sunday night and we were off to the race. There were eight Informix sessions on Monday including presentations on how IBM helps Cisco, open source, hands on lab on high-availability, another one on the new features of Informix 11.70, bests practices for virtual environments, and performance enhancements. Of course, the most popular session was from Jerry Keesee titled: "Informix at IBM: The next decade".
The day ended with an Informix reception at the Mandalay Bay beach casino for an Informix 11.70 launch celebration and to start looking forward to the next decade of Informix at IBM.
Tuesday started early with an Informix "eat and meet" breakfast at 7:00am, followed by nine Informix sessions throughout the day. The sessions covered areas such as upgrade, new features, Informix warehouse, application development, 4GL, embeddability, flexible grid, and more. It was also interesting to hear about how Informix is used to run a steel plant.
The day ended with a beach party reception. Now it is on the Wednesday with another full agenda.
Yes, a new version of Informix is now available: Informix 11.70.
There are a lot of great features in this release. I could talk about the flexible grid that allows you to manage many machines like one and support rolling upgrades. I could talk about the new analytics features where we've seen speed up of warehouse-type queries of around 50%. I could talk about storage provisioning, improved installation and embeddability features. Yes, I could talk about all this but at this time, I want to talk about some features that should interest application developers.
I have to admit I am a little biased since my group is called application development services. However, the features I want to talk about were either requested by customers or have had a very positive reception in early mention under non-disclosure or during the beta period.
The first one will facilitate porting schemas from other databases to Informix. Let me first show an example:
CREATE TABLE tab (
The first improvement is the ability to change the order of constraints and default values. Before Informix 11.70, the col1 definition would have returned an error since the default clause had to be located before the NOT NULL constraint.
The second improvement is the ability to explicitly say that a column can accept NULL values. Before, it was implied if the NOT NULL constraint was not there.
The last improvement shown in the example above shows that we can add "ON DELETE CASCADE" after the constraint name.
Another improvement in the DDL area is the ability to conditionally execute CREATE and DROP statements. Here are two examples:
CREATE TABLE IF NOT EXISTS tab ( . . .);
If, for example, you want to make sure a table is re-created, you could always say:
DROP TABLE IF EXISTS tab;
If you want to make sure that you keep the table if it already exists, then don't do the "DROP IF EXISTS" and simply use "CREATE TABLE IF NOT EXISTS".
Finally, here's another DDL feature that was in great demand. It is not really an application development feature but it has been requested a lot: The ability to define the EXTENT size in a CREATE INDEX statement:
CREATE INDEX myidx tab(col1) FIRST EXTENT 8 NEXT EXTENT 8;
Don't forget to read the release notice since there are many other improvements on the INDEX capabilities.
On the DML side, we are now able to use expressions in the COUNT aggregate function. This can be useful if you want multiple aggregates in one statement:
SELECT COUNT(*) total, count(CASE WHEN sex = 'M' then 1 else NULL) males
Without this capability, you would have to solve this problem with three separate statements. For example:
SELECT * FROM