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:
- Compatibility functions
- TimeSeries improvements
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:
Information center 11.70
This release added a few compatibility function that makes it easier to move application to informix. They include:
- CHARINDEX(substring, source_string, [start_position])
Search for a string in another one and return its starting position. You can start at an offset (start_position) in the source string
Convert a radian value to degrees
- INSTR(source_string, substring [,start [,count]])
Search for an occurence of a substring starting at a specific offset
- LEFT(source_string, len)
Return the "len" leftmost characters
Synonym for LENGTH
Convert a degrees value to radians
Return a string in reverse order
- RIGHT(source_string, len)
Reverse the "len" rightmost characters
Return a string of "len" spaces
- SUBSTRING_INDEX(string, delimiter [, occurence])
Returns the substring that ends at a specific occurence of a delimiter
Take a look at the details of these functions. I'm sure you will find a good use for them.
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:
If you're leaning against a wall, you have two choices to increase the distance between you and the wall. Either you push the wall away from you or you push yourself away from the wall.
Not long ago, I heard someone say that to increase performance, we should move the data to where the processing is. I did not want to get into an argument but I could have asked: What about moving the processing to where the data is?
This could be done looking back at user-defined aggregates as discussed in the previous blog entry.
So, which is the wall? Of course, the answer is: It depends. The key is to keep an open mind about where the processing should be. there could be many cases where putting the processing in the database is the right answer. Using user-defined aggregates could be the technology to use.
If you've read my last entry, you know what happened with my watch while I was in Strasbourg. Considering that I have had that watch for a long time, I decided that it was time to replace it with a newer model so I spent $35 for the latest version of the same watch. My watch had run its time.
You must be wondering why I'm talking about a watch in an Informix blog :-). Informix (IDS) being a software product keeps on running. The bits don't wear out. Or do they?IDS 7.31 end of service: September 2009IDS 9.40 end of service: April 2009!IDS 10.0 end of service: September 2010IDS 11.10 end of marketing: September 2009
So maybe the bits wear out... There are many other reasons why you'd want to go to IDS 11.50. They include the improvements in the areas of performance, ease of administration, business continuity, security, and application development. You also need to consider the new features and the integration with other IBM solutions.
To give you an idea about what you are missing, you can read the following slightly outdated document: Why upgrade to IDS 11.50
Staying up-to-date with IDS is another way to increase your business advantage. Look into it as soon as you can.[Read More]
There's a children book that I used to read to my kids. It was about a boy that was laying around on the grass when he say a fly go by. What followed was a bunch of animals chasing each other.
Lately, I took a break from blogging (I hope you've noticed!), like a little boy laying on the grass, enjoying a sunny day. During that time, IDS 11.50xC6 came out. Here are a few interesting features:
- External table: an SQL interface to files to allow for very fast load and unload.
- XA transactions on secondary servers
- Backup on the RSS server: You can make an archive of an instance on an RSS server
- Dynamic listener threads: You can start, stop, and restart listener threads for the soctcp or tlitcp protocols without interupting existing connections.
There are also enhancements to the MERGE statement and the attach/detach capability among other thing. You can find out more about the xC6 release in the release notice
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
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.
I recently met with a consultant friend of mine. He told me that he would like to have a new feature in the database server: a blocking select.
This feature would block on a SELECT until a new row is available. The SELECT statement could also have a condition (WHERE clause). If no rows are returned, the SELECT blocks until it could return at least one row. This way, an application could be event-driven without having to constantly check if there was anything new in the database. You can imagine the overhead of checking if there is something new every few seconds. A blocking SELECT could possibly reduce the pooling overhead and be more responsive depending on what the pooling interval is.
IDS does not have a blocking SELECT and as far as I know, nobody else has it either. I can think of two ways to achieve this:
- Use triggers on the table.
This could be an INSERT and an update trigger. The trigger would test the condition and then execute a user-defined routine (UDR) to send a signal to a process that would tell the process to read the table.
This approach could work when the table does not receive too many INSERTs and UPDATEs. The UDR could potentially find information from a file or from a configuration parameter stored in a table. It could also keep that data in memory to avoid getting the parameter for each trigger execution.
- Use the Change Data Capture API (CDC)
CDC provides an SQL interface to the database logs. You can configure your "log snooping" for a specific table. Each time a new log record is created, the function would return. The application then needs to look at the type of record and decide what to do. This approach should be very efficient and add virtually no overhead to the database server.
Another approach would be to use the InfoSphere Change Data Capture product to replicated specific records on a message queue and have the application subscribe to these messages.
Maybe a blocking SELECT would still be a neat feature to have but with all the different ways we can provide event-driven processing, maybe it is not needed after all.
I saw the cover of Computer world the other day with a title of "Swinging toward centralization". I'm not one to be jumping on trends but I think this idea has merit. To me, it ties into virtualization, possibly cloud computing, and also the IBM concepts of the smart planet.
Centralized IT could mean first the optimization of hardware resources. The best approach is to use virtualization so all the hardware resources can be used optimally. For example, instead of having, let say 100 computers running at 50%-70% utilization, you can centralize and use virtualization and either reduce the number of computers to around 70 or use the extra capacity for growth. This is a pretty conservative example. Just consider this quote from Computer World, April 20, 20009:
"Austin Energy: With a new virtual environment, applications run on 150 servers instead of 600"
Centralization gives you this opportunity. Note that I'm talking about centralizing the hardware resources. If you centralize processing for one large application, you'll likely need the help of advance features such as IDS Continuous availablity (CAF) and the integrated replication capabilities (HDR and ER).
Centralization does not mean that the personnel must also be centralized. Today, network access is pretty much a fact of life (I so wanted to use the word ubiquitous!). All the application and system management can be done from anywhere. For IDS, just consider the Open Admin Tool for IDS (OAT) or management tools from our partners such as AGS and CobraSonic. Managers can consider these resources as part of a "cloud".
What a nice segway to my next point
We hear a lot about cloud computing. You can buy time on some machines in the cloud. We could also mention software as a service like in the case of LotusLive (see https://www.lotuslive.com/en/) or the IBM cloud offering. This does not mean that you have to go outside to have a cloud. You could create a cloud from your centralized data center and provide capacity on-demand based on resource optimization.
When we talk about a large centralized data center, the server consolidation is only part of the savings. the saving in energy can be significant. The other day, I listened to a presentation by an IBMer that manages a large data center providing services worldwide. Here are the type of things he did:
His team installed active RFID sensors to monitor the temperature and humidity levels in different areas of his data center, including multiple locations in the racks, and at different times. With this information, he was able to clearly identify machine needs. At one point, he was able to identify that if he installed a (raised) floor tile with holes at a specific location, he could eliminate his "hot spot" without increasing his air conditioning needs. He even figured out the correlation between applications and machines heat output. So he can regulate the room temperature based on which application is running!
Talk about a great example of a smarter planet: instrumented, interconnected, intelligent (devices).
Lately, I've heard a lot about cloud computing. It starts with fast networks and technologies such as Mashups and REST interface. A mashup allows you to take information and processing coming from different sources and put them together to provide a new application and new insights based on the data manipulation.
REST (REpresentational State Transfer) is a way to access data, possibly through a web service, using the HTTP protocol (Web server protocol). This makes it simpler than using the SOA SOAP interface.
Now, providers like Google and Yahoo! provide email and storage services. I even hear that, with the help of virtualization, some companies are looking at providing a "web stack" that would provide the ability to run company businesses remotely. That sounds like a new twist to service-bureaux of olds.
There are several issues around this starting with response time and the complexity to provide what companies really want but it seems something that could take off. There are a lot of talks about web applications so why not a web IT infrastructure? That has a potential benefit of centralizing all the administrative needs.
I think this is something worth discussing. What do you think? Post comments to my blog to let me know.
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.
Looks like I jumped to conclusion too quickly. I won't give you any details or attenuating circumstances. I simply did not check properly. It looks like we do hve something on the Windows platform but not on the others.
I simply have to statrt lobying for the data server drivers as part od CSDK on all platforms. In the meantime, you can download the common drivers starting at this URL:
The one you want is the IBM Data Server Driver Package (DS Driver). On Linux, it is a 24MB download.
More on how to use it later.
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...
I just received an email alerting me to this new article on developerworks. If you're interested in setting up SDS servers, you may want to read this article:
Last week I stayed at a quaint hotel in Strasbourg. Since the room did not have an alarm clock, I decided to use my watch to wake me up on Monday morning. Considering that there is an eight-hour timezone difference between Denver and Strasbourg, using an alarm is a good idea.
I woke up on Monday 30 minutes before the alarm was supposed to ring. That's long enough to make it worthwhile falling asleep again so I did. I woke up again with a start, picked up my watch and looked at the time: the display was blank!I needed to find out what time it was in a hurry. Maybe I was late for the start of the class! Luckily for me, it turned out that it was the time I was planning to get up at. I guess my brain kept track of the time as I was sleeping. It has worked in the past but I don't find this method the most reliable. At this point, I started using my phone as my alarm clock.
Later that week, when I was in Paris, I had to go visit a partner. The sales specialist send me the information. I wrote the address down on a piece of paper and went to grab a taxi. The taxi driver could not find the place even with the use of a GPS device. I did not have access to my email with my laptop, I did not write down the partner's phone number and I had no way to contact anybody. I was about to tell the driver to turn around when I remember that I get my emails on my phone. Luckily, there was a phone number and we were able to get to the right location.
Twice in one week! Since I had to leave my hotel on Saturday at 5:00am, I did not want to take any chances: I setup a wake up time on both my phone and on the television/alarm clock. Surely at least one of the two would work. It turns out that both worked that morning and 20+ hours later I was back at home (ahh! the glamor of travel). Now my laptop appears to act a little strange. I better do a backup...
That made me think: Do all Informix DBAs have a contingency plan? What happens if something goes wrong? How much does it cost the business for each hour of downtime?
IDS offers a lot of capabilities that can address the needs of a business environment. It starts with online backup either full or incremental and adds to it through the following:
- Continuous Availability Feature (CAF): This provides the ability to share the disks so instances on different machines (or blade) use the same database space. This is great to quickly recover from machine failure since the database is accessible through another machine. Since all machines access the data simultaneously, it can also provide horizontal scalability
- High-Availability Data Recovery (HDR): Provides disaster recovery through the replication of the data to another instance.
- Remote Secondary Server (RSS): Adds to the HDR story by supporting additional copies of the data into other instances
- Continuous Log Restore (CLR): You can automate the restore of IDS into another instance so that instance is ready to be put online if needed in the case of a disaster on the production machine
- Enterprise Replication (ER): Gives you the ability to distribute the data over hundreds of instances to have distributes work and redundant data.
All these options work together. Talk to your local IBM-Informix IT specialists if you want to know more about these capabilities.
A special online event with live webcast is scheduled for February 25
Instead of repeating what is posted elsewhere, let me introduce you to Spokey Wheeler's blog. If you don't know about it, you may want to start visiting this blog regularly. Here's the link to Spokey's blog entry:
Spokey's blog on the Data in Action virtual event
Read the entry and register to the event!
When we talk about processing data in real time, it is easy to just write a program and be done with it.
The problems start piling up when we add analytics and volume.
A program is easy to write when it can process records sequentially. Once you reach the limit of this sequential processing, you start adding complexity that may represent the bulk of your work: You start by using multi-threading and eventually you need to also go to multi-processing to take advantage of multiple machines. It is much easier to use a framework to reduce those issues.
Still, a framework may give you the ability to distribute your processing but how easy is it to do? Now you want proper tools to assemble the many operations that you want to link together. Then, you also need to have the tools to easily identify bottlenecks so you can parallelize you operations. What about all the standard operations you would expect to be able to do?
This is where a platform comes in. It gives you the foundation for distributed processing but also gives you pre-built capabilities to interact with the outside world (files, message queues, databases, and so on) and also analytics so you don't have to reinvent the wheel.
For a more complete discussion on the subject, take a look at my two articles on the IBM Datamag site: part 1 and part 2.
Anybody knows what is Informix 3.30.12Z is?
I got a call yesterday to help with a customer that had that product. To give you an idea of what it is, I received the following header:
Informix Master Menu ........................................Informix Version 3.30.12Z
Copyright (C) 1981, 1982, 1983, 1984 Relational Database Systems, Inc.
Software Serial Number . . .
Sorry for the ..., I did not know how to insert significant spaces
For people who don't know, Relational Database Systems was the name of the company before it was renamed to Informix Software. With a copyright going to 1984, that means that the product is 24 years old! Add to that no updates and support.
I had to contact Jonathan Leffler to find out what this was about but one thing is clear, Informix (Relational Database Systems, Inc) has a long history of reliability and dependability.
These are two concepts I've been reading about lately in a book from Eliyahu M. Goldratt (The Goal).
It's interesting to read that a system throughput is determined by its slowest component. Of course, that's something we are familiar with in database management: we want to optimize the I/O to get better performance. What I found more interesting is that when an event is delayed, it can have a direct impact on the overall system throughput. For example, if the slowest component is delayed, it represents a direct loss to the system. In other cases, other components can take a long time to catch up after a delay.
One key to all this is to look at improving the entire system and the way to do it is to find out where the bottlenecks. Once they are found, we must figure out how to make sure they are not idle waiting for something to happen and that they don't do extra work.
This seems to be a lot of what an Informix DBA does when there are performance questions. I could easily point to disk fragmentation by expression, use of prepared statements and so on. The thing is that I've also seen other situations where people point to the database as the source of the bottleneck to find out that it is outside the database. I've seen issues of network and recently I was told by a customer that they must have a specific response time because the transaction already takes 3 times that before outside of IDS. IDS has to sprint because the other components jog.
In another situation, I found that what the customer was seeing as one database requests turned out to be over 100 SQL statements. The kicker was that most statements were unnecessary.
Next time people point to the database as the problem, make sure to get the complete picture from end to end.
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:
- the top 10 in-demand job in 2010...did not exist in 2004
- (dated!) 200 million register users in MySpace would represent the 5th largest country in the world (What about facebook?!?)
- It too the radio 38 years to reach an audience of 50 million people, it toook television 13 year and facebook 2 years
- It is estimated that 4 exabytes of unique information will be generated this year. This is more than the previous 5000 years
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