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.
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.
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.
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 (
col1 int NOT NULL default 0,
col2 int NULL,
col3 integer REFERENCES tab1(col1) CONSTRAINT tab1_c1
ON DELETE CASCADE
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 ( . . .);
DROP PROCEDURE IF EXISTS my_proc();
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
COUNT(CASE WHEN sex = 'F' then 1 else NULL) female FROM tab;
Without this capability, you would have to solve this problem with three separate statements. For example:
SELECT * FROM
(SELECT COUNT(*) AS total FROM tab ),
(SELECT COUNT(*) AS male FROM tab WHERE sex ="M"),
(SELECT COUNT(*) AS female FROM tab WHERE sex="F");
These are just a small part of the new improvements in Informix 11.70. Make sure you read the release notice to learn more about Informix 11.70 at:
I listened to a presentation on this subject recently.
What I found interesting is that the research found that good ideas do not come from a Eureka moment. For example Darwin recounts his Eureka moment in his auto-biography. Further study of his personal journals show that Darwin had the full theory of natural selection many months before his stated Eureka moment.
According to research, most good ideas come from discussions:
- The rise of coffee houses is credited for the Enlightenment period in England
- In research labs most ideas come from the weekly lab meeting when people share their mistakes, issues, etc.
Another interesting point was that many good ideas come from the connection of people that share their thoughts to form a complete idea that is worth pursuing.
How can we generate good ideas we can act upon and make our environment better?
We need to interact with people in a situation that is conducive to generating these ideas. We have such an opportunity in just a few week: The Information on Demand (IOD) conference in Las Vegas, October 24-28.
Think about it: we will be with a bunch of people that have technical problems to solve around the use of technology in general and Informix in particular. We'll listen to presentation on new features, solutions in different industries, best practices, bird of a feather sessions, mingling in social settings such as the Informix celbration on Monday night.
Let's take advantage of this great opportunity! See you in Las Vegas!
Here's another one:
This whitepaper summarizes a comparison study in which the total cost of ownership of IBM Informix is shown to be 31+ percent less than Microsoft SQL Server. This is of great interest to mid-size businesses that face considerable challenges in their IT environments to reduce costs while gaining competitive advantage.
You can download the report here: http://ht.ly/2Ln0d
A new report is now available on the economic impact of using Informix. The study is based on a large international retailer with over 4,000 instances of Informix.
The report concludes that over a three year period, the retailer saved over $1 million dollars. The study was done in May 2010. I am convinced it would be even better if they were to do it again with Panther. You can find the report at:
There is so much going on!
As you surely know, we've been doing a closed beta of the next version of Informix. We have received a lot of great feedback and we keep on working on this release.
We still can't talk about it. It is just a matter of time before we can do so stay tuned.
On other fronts, I am working on a follow up to my Application development short book. I've received a lot of positive feedback on this book and I am excited about continuing on the subject. When will it be ready? I'm hoping sometime this year.
Finally, do you realize that we are barely more than a month away from the Information on Demand (IOD) conference? I hope to see you there.
As you know, the IIUG has a page devoted to open source products that can run on Informix. There has been a new addition recently. It is a patch to have Drupal version 6.16 run on Informix.
Drupal is a popular product that provides capabilities to publish, manage and organize a wide variety of content on a web site. To run it with Informix, you need:
- Informix version 11.50 or higher
- PDO_IBM (and the IBM common drivers to compile it)
You still need to download the code from the drupal site (www.drupal.org) and then apply the changes provided at www.iiug.org/opensource.
In my blog entry of February 17, 2010, I had to put out a retraction about the common drivers. As I said then, I had to start lobbying for their inclusion with Informix.
I am happy to report that the Informix client SDK version 11.50.xC7 includes these drivers. Note that they are not included in Informix 11.50.xC7. I would expect that future releases of Informix will include a CSDK that has the common drivers.
There is a bit of work to do after the CSDK installation to complete the common drivers installation. I'll cover that in an other blog entry later. If you need information on how to use the common drivers, I suggest you download:
"Informix Dynamic Server Application Development: Getting Started":
If you are interested in application development, the following URL is also of interest:
Until next time...
Here's something you may want to act on:
IBM Informix Survey for Continuous Availability White Paper
Complete the Survey to win an Apple iPad! - 1 week left
Every response matters. - Start Survey here!
IBM Informix is the database software voted #1 in customer satisfaction. Clients choose Informix because it is reliable, low cost, and hassle free. Solution providers choose Informix for its best-of-breed embeddability.
Yet, there are some people that still don't 'get' Informix, or realize the many benefits of deploying it. Help us gather data to support this claim.
- Informix is exceptionally hardware efficient, which means that (in the REAL world) you need to spend MUCH less on hardware to get the same performance as other products.
- Informix is exceptionally reliable, which means that (in the REAL world) you don't need to pay lots of people to make sure it stays 'up'.
- Informix is exceptionally scalable, which means that (in the REAL world) it can be idling one moment and then processing thousands of transactions the next with no apparent stress.
Advanced DataTools is working with Oninit, to gather data about what happens in the REAL world to support these assertions with empirical evidence in the REAL world.
The data collected will be used to compile a report that will be made available to every CTO, IT Director and IT Manager. Along with this, they will receive a list of all the major application vendors that are now porting their applications to Informix V11.5 and a document outlining the key reasons to choose Informix.
Every response matters. - Start Survey here!
Win an Apple iPad. One randomly selected participant in the data collection phase of this research will win an Apple iPad, provided by Advanced DataTools Corporation, an Advanced IBM Informix Business Partner.
Note: Public Sector Employees are not eligible
I recently ran into the mention of INT8 and, by association, SERIAL8 by Informix engineers and a recent redbook. I want to make a quick comment on that.
These two types were added a long time ago to support the eight-byte integers (64 bits). They are defined as being a 10-byte structure that includes two "standard" integers. It was done this was so eight-byte integers could be supported on 32-bit operating systems. Now it appears that most operating systems support a native 64-bit integer. For this reason, new data types were added to Informix version 11.50 (fixpack 1). The new types, BIGINT and BIGSERIAL, take less space and perform better. Here is what the release notice says:
Improved Query Performance for Large Integers and Serial Data
The BIGINT and BIGSERIAL data types, which are provided as alternatives to the INT8 and SERIAL8 data types. can provide better performance than the INT8 and SERIAL8 data types.
So, let's forget about INT8 and SERIAL8 and let's use BIGINT and BIGSERIAL available in Informix 11.50.
Just a quick note to say that I'll resume blogging soon.
I've had some medical issues that took me out for over a month but I'm coming back.
Great things are hppening with Informix. Stay tuned!
The day started with a Q&A with IBM excutives: Alys Passarelli, Inhi Cho Suh and Rob Thomas. There were a number of good questions and it was an opportunity for the excutives to state their commitment to Informix and describe many of the efforts in progress.
Once again, a good mix of sessions on subjects from customer case studies, database administration, security, best practices, and use of tools such as Eclipse.
The day ended at 4:30 after the delivery of 25 sessions.
This was a great conference with lots of good information and fantatic networking.
I did forget to mention the key note presentations of the evening of Monday. Rob Thomas gave us his view of the Informix business and a glimpse at his plan for continuing successes. It was followed by a presentation by dr. Arvind Krishna, general manager of IBM Information Management. Great information!
Tuesday, sessions started at 8:00 AM. I was the moderator for the session on disk level encryption. It was about a flexible product that can protect your database data at rest. It works for file-based dbspaces (cooked files) or raw devices. All that, transparently to Informix. Mark Jamison did a great job at presenting the product in a clear and concise manner.
Theere were many sessions on application development in cluding sessions on Groovy, Perl, Python, PHP. Of course, there were also more sessions on duifferent aspects of tuning and also presentation on embeddability, replication , warehouse and so on.
With the sessions starting at 8:000 AM, we had a total of 35 sessions for the day.
This was followed by a casino night. What a full day!
The day started with a keynote speach by Jerry Keesee. I believe the key to the presentation is that the Informix is taking the next step to insure continuing growth of the Informix product. There are some exciting things happening on that front.
The first session I attended was "Data Modeling" given by Jack Parker. There were a lot of interesting examples how a data model greatly impact your production system. I have to agree with Jack that the greatest performance gain in with the data model, not tunig the database after the fact. This is why database experts should be involved at the beginining of a project. They can then also take advantage of database extensibility; something I could talk about for a long time...
There were five sessions going at once for a total of 30 sessions for Monday. There were a lot of good sessions on subjects including database administration, application development, programming, security and more. there wer demos of all sort and great conversations to be had. Someone told me that one of the benefits of this conference is that they can solve in minutes problems that have benn bugging them for weeks.
Of course the evening event was also worth attending with a bunch of engineers coming from the lab just to talk to everyone. All in all a fantastic day!
Sunday was the beginning of the conference. Even though there were tutorials during the day. The real beginning was the welcome reception. The exhibitors booths were ready and the attendees were cheerful. IT was a great networking opportunity with good food and plenty to drink.
I have to admit I missed a lot during the reception because I was in multiple intense discussions.
All in all, a great start to the conference.
I ended my last entry with the IBM statement about smarter planet: instrumented, interconnected, intelligent. I'd like to comment a little more on the instrumented part.
In my last entry, I mentioned the utilization of active RFID to monitor a large data center. Of course, this monitoring compares data points at a specific point in time. Most likely, the readings are done at specific time intervals. The same would be true if you were monitoring the energy consumption and temperature of houses in different neighborhood in a city, traffic information (cars or packets), or any other thing you'd want to collect.
There are two main concerns in this type of processing:
- How do we quickly ingest the large amount of information generated into a database?
- How do we efficiently process the information?
The first concern is to be able to ingest the information without falling behind and with cycles to spare for analysis. In most cases, the information must be kept for further analysis or for historical comparisons and analysis.
The second one is about the analysis itself. Informix has addressed these issues with time-based data with the TimeSeries datablade. It provides an efficient way to store and process large amount of data very quickly. If the ingestion rate is a concern, Informix also has the TimeSeries Real-Time Loader. As its name implied, you can ingest a large amount of data and make it available for analysis virtually immediately.
For a smarter planet, keep these efficient tools in mind. Informix is likely to be the answer to your needs.