Big data in motion
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 3,986 Views
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.[Read More]
JacquesRoy 120000A2MS 3,207 Views
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.
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.
I've been saying for quite a while now that smart meters represent BIG DATA and that Informix TimeSeries is the optimal solution for an operational data store.
We can complement the Informix capabilities with other IBM products. When it comes to real-time processing of huge amount of data. The IBM solution is InfoSphere Streams.
It happens that Streams can interface with Informix as a data source or as a target (sink).
If you want to know more in this area, go take a look at the new information added to the Smart Meter Central wiki on Streams.
Two pages were added. One on a quick overview of Steams (with a youtube video) and another on setting up the environment.
The exact pages URLs are:
The wiki URL of the welcome page is: https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en#/wiki/Informix%20smart%20meter%20central/page/Welcome
Make sure to bookmark it.
More to come as we go deeper into BIG DATA!
JacquesRoy 120000A2MS 3,012 Views
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 came back from the Informix conference Thursday night and woke up thinking about an analogy about why we use Informix Dynamic Server. More on that in a minute.
I've been using databases for a long time. I believe that the first formal database system I used was back in 1984. It was a hierarchical database. I developed an inventory system for the Canadian Coast Guard. Over the following years, I used and supported multiple databases systems some looking more like C-ISAM and others relational. I still remember the good old days where I had to debug Oracle installation scripts :-)
So, why Informix? Isn't a database a database?
I uses to use a car analogy: people buy cars and they are used to what happens to it: If they have to go to the shop to get it fixed or tunes every other month, that's just the way cars are. Who would believe that you could buy a car and only have to put gas in it for years after years without having to waste time in the shop? the car is used to get you from point A to point B day after day. It almost makes it invisible but not quite since you still have to drive it. It's not the same with a database system: it can really be invisible.
I woke up Friday with this thought: You can write just about any application in any computer language you want. Why don't we all use COBOL. Way back, I know a guy that could do EVERYTHING in COBOL. He was even doing system programming! An object oriented version of COBOL has been available for years buy why. Isn't the "vintage" version of COBOL good enough? If I'm not mistaken, the number of COBOL lines of code in production still surpass any other programming language. That should be enough of an argument to standardize on it.
It seems to me that many people apply this line of reasoning to database systems. The trend is to look at databases as commodity. Who cares that one barely requires any attention? Who cares that it provides easy continuous availability? Who cares that it has great storage optimization? The difference is only more overhead. that translates only into more costs. Those significant costs are easy to hide so why worry about them. Everybody does it so no need to be more efficient...
Well, me, I'm old school. I come from an era where memory was measured in kilobytes and disk drives in megabytes. Yes, memory is much bigger now and not that expensive. Disk drives are so much bigger and not very expensive. Computers are so fast now. It seems to me that we should stop the insanity and pay attention to efficiency. Isn't that what cloud computing, virtualization and being green is all about?
No matter how I try to slide it, to me, Informix is number 1.[Read More]
JacquesRoy 120000A2MS 2,951 Views
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 new Informix, version 12.10 was announced last week. It is time to start talking about the new features in TimeSeries.
The Informix team has added a public version of a fast loading mechanism. It allows to load into existing TimeSeries that are defined as part of a container.
This loader API was previously undocumented. It was only available to use as part of the Tooling. A lot of work went into it since its internal implementation. You should not try to use the older internal version since it disappears in 12.10 in favor of this new one.
You can find a description of its use in the "Informix Smart Meter Central" in the page Loading fastest with the loader API
You should also refer to the Informix documentation for more details.
Since the Loader API is an SQL API, it can be used by any clients including InfoSphere Streams.
For more information on how to use Streams with the loader api, please see the Informix Smart Meter Central wiki: Streams and the TimeSeries Loader API
More to come. Don't forget, the IIUG conference is just around the corner. This is the perfect place to learn about all the new features in Informix 12.10: Simply powerful.
JacquesRoy 120000A2MS 2,694 Views
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.