I just had a need for a function that takes a datetime year to second and returns the number of seconds since January 1, 1970.
That would be easy to do by writing a "C" UDR but I did not want to deal with compiling and installing a shared library so I decided to approach it as an SPL routine.
Not that it is a great thing but I thought I'd share it with whoever needs it. Let me know if you find this useful:
CREATE FUNCTION epoch(dt datetime year to second)
DEFINE dt_varchar varchar(20);
DEFINE mm, dd, yy, days, hh, mi, ss integer;
LET mm = MONTH(dt);
LET dd = DAY(dt);
LET yy = YEAR(dt);
LET days = MDY(mm, dd, yy) - MDY(1, 1, 1970);
LET dt_varchar = dt;
LET hh = substr(dt_varchar, 12, 2);
LET mi = substr(dt_varchar, 15, 2);
LET ss = substr(dt_varchar, 18, 2);
return (days * 86400) + (hh * 3600) + (mi * 60) + ss;
You can use it either in an SQL statement or directly with EXECUTE FUNCTION. For example:
EXECUTE FUNCTION epoch("2008-11-25 08:32:45");
1 row(s) retrieved.
This has been in the works for quite a while but now it’s out!
This new version adds multiple interesting new features including:
Streaming data to Excel
Easy setup for high-availability
Resilient processing with the consistent region annotation
Streaming data to Microsoft Excel makes it easy create user interfaces to get real-time feedback on what’s happening in addition to providing all the capabilities from Excel to do additional processing on the data received.
A lot has been done on the high-availability front. It is much easier to setup redundant administrative services and have them failover automatically when needed. In addition, there is no need for a DB2 database. Instead, Streams now relies on Zookeeper to preserve all the state information. Also,to continue to improve on high availability, Streams does not require a shared file system anymore.
There is a new feature that guarantees at least once processing a tuples within a region or a set of operators. It is easy to use. We simply have to add annotations that define the region and set a few parameters.
There has been enhancements to existing toolkits and addition of new ones such as support for Kafka in the messaging toolkit and the new HBase toolkit.
There is more to the new release of Streams. You can find the online documentation in the knowledge center at:
To get an idea of what’s new in this release, the a look at:
We are now living in a world that is more and more instrumented, intelligent, and interconnected. That is actually the IBM definition of a smarter planet.
This opens the door to many possibilities to better use natural resources and improve many things.
Like "uncle Ben" said: "With great power comes great responsibilities" (I'm sure you know where that comes from ). There is always a way to misuse capabilities.
Recently I ran into an interesting video on Ted called Tracking the trackers.
It is basically about how many different sites that you may have never visited can track you and your information and you can't say anything about it.
You can find this video at: Tracking the trackers
It is about a basically unregulated industry doing what is called "behavioral tracking". Apparently it is a $39B industry!
the video is a little under 7 minutes. Enjoy.
It is easy to jump from stealth tracking to security concerns: What is going on in your network?
If your systems are accessible through the network, you should take a close look at how secure they are. Make sure your INFORMIXDIR files all have appropriate access control.
What about your users log ins? Have you setup your environment in case of denial of service attacks?
Maybe it's time to review this URL: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sec.doc/SEC_wrapper.htm
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:
- Space savings
In a standard relational database, each time series element must have an identifier and a time stamp because the time series element are stored in a separate table from the object it refers to (such as an electric meter). You then need an index on the identifier and the time stamp so you can joint it with the table that allows you to select what identifier and time range you want to operate on.
In contrast, Informix TimeSeries stores the time series in the same table and row that represents the object. You don't need the additional index on the identifier to join tables. Also, the data is kept in time order in the time series. In the case of regular intervals, you don't even need to keep the time stam since the position gives you the time
In customer tests we have regularly seen that Informix TimeSeries takes one third of the disk space.
- Performance benefits
Just by having less data on disk gives the Informix TimeSeries a significant performance benefit but it does not stop there.
The data is ordered. This means it is much faster to get to the exact subset of data that you want to process. By being ordered, This means you will likely find the next record that you are looking for on the same page as the last one. In a relational system, all this data could be scattered over a large number of pages without specific ordering. This would cause a lot more I/O operations to be executed than in the case of Informix TimeSeries. You also don't need to go through an additional sorting step before you process your data.
In customer tests we have seen as much as 60X performance improvements over standard relational in some queries.
- Simpler development
Informix TimeSeries comes with a set of functions that allow you to manipulate the time series. For example, if you need to group ou r reading to go from a 15 minute interval to a hourly interval, it can be done in a simple statement. Similarly, if you want to calculate a running average: simple statement. This means you don't need to write specialized code to provide this processing. It is built into the Informix TimeSeries capabilities.
The end result can be simpler code, less maintenance, and faster time to market
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
The first entry went in almost without problems. I had to quickly add some formatting after I posted it to make it more readable. Over time, I'll figure out how to do this and make my formatting fancier.
I'm new to blogging. I'm also pretty new at reading blogs. With the new Web 2.0 technologies, there is no need to go to a blog every so often to find out if there is something new. So, in case you don't know, here's some information on dealing with blogs.
Blogs support a capability called syndication. This allows to create what's called a feed to warn people of changes. There are two main types of feeds: RSS and Atom. No need to know more about it for now. Just that you can use a feed URL to get the changes in a syndicated site.
I would suggest that you use a feed reader. Why? because there are many sites you may want to subscribe to. For example:
- Guy Bowerman's blog (http://www-128.ibm.com/developerworks/blogs/page/gbowerman)
- Madison Pruet's blog (http://www.ibm.com/developerworks/blogs/page/roundrep)
- Feeds form the IIUG site (http://www.iiug.org/rss/index.php)
- Informix Zone site (http://www.informix-zone.com/)
- and many more. . .
Take a look at the latest IIUG newsletter (insider #94) for more in the Informix Resource section.
Having to visit each site regularly to see if it has new stuff can be time consuming. Using a feed readeraggregates all those and lets you know what's new. That's the way to go!.
If you do a search on the web, you can find multiple feed readers. I did not want to spend too much time figuring it out so I downloaded a Windows-based open-source product that will do until I find or am told about something better. Check out: http://www.feedreader.com/
If you haven't done it already, set yourself up and stay informed on the latest entries.
That's it for the introduction to this blog. Now it's time to dive into Informix and Computing![Read More
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 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
The Informix team is putting a lot of energy behind this conference. The team is also putting together a Customer Advisory Council meeting on June 2nd where there will be discussions on product directions and features prioritization.
For more information on the conference, please see:
The call for speakers is going on until February 13. This is a great opportunity to participate with the EMEA Informix community and get some exposure for yourself and your company. Take advantage of it.
Find out more at the URL mentioned above. Like it says on that site: Register Today!
I think Terri is pulling my leg. She is apparently receiving concerned emails about what happened in Brussels. It was a humorous situations that I wanted to relate in a fun way. I guess I have a future in fiction writing :-).
Really, nothing happened. She took a picture, the police courteously told us that the American embassy did not want people to take picture. Terri deleted the picture from her camera while having a pleasant time with the officers. We then left and laughed about it.
So, don't worry, Terri is doing fine and we all had a good time in Brussels. I strongly encourage people to come and visit.
What does that mean to be green? Is that using solar panels to power our computers? What about if we put pedals under the desk of each employee so they generate the power they need. That would have the additional benefit of raising the fitness level and maybe improve the overall health level... I remember Martie Lurie and his son demonstrating something about a bicycle and an Informix database at the last IIUG conference (or was it IOD?). Maybe they were onto something :-)
Kidding aside, we know that Informix was green before people cared about green. The product was built from the ground up to take full advantage of hardware resources. To me, that's not even half the story. Informix IDS is set it and "forget it". What type of impact does that have on the overall carbon foot print? We often hear customers talk about a 1 to 10 ratio between Informix DBAs and competitor products. And there is the extensibility features. You know me, I could go on and on about that and the huge benefits it can provide such as processing 100,000 trades per seconds on a 4-CPU Linux machine.
Lately there has been more happening with Informix to help make it greener by including it into a virtual appliance that can even be deployed in the Amazon EC2 Cloud. Guy Bowerman has talked about it on his blog and will cover this subject in his presentation next week at the Informix conference (Apr 26-29).
Take a look at theThe GReen IT Report on DeveloperWorks to see what IBM has to say about this subject.
Till next time.
This was the last day of the conference with a 35 sessions. I was surprised to see how many people attended the presentations until the end. I see this as a big endorsement of the value provided by these presentations.
On my part I delivered one presentation first thing in the morning and another one starting at 2:10 pm. Despite that, my session was well attended.
Overall a very successful conference that was well worth attending.
IDS 11.50.xC5 became eGA on July 24th. It includes several new features including the "CONNECT BY" syntax and the MERGE command. There were other improvements in multiple areas such as administration and usability, and in the continuous availability including Enterprise Replication (ER).
For more information look at:
I recently received a note about the IOD conference, October 25-29, at the Mandalay Bay in Las Vegas. If you register by August 31, you can get the early bird hotel rate!
Please go to the Conference Site to learn more about the IOD conference and register. Here are the top reasons provided to attend:
- Turn your information into a strategic driver of innovation, business optimization and competitive differentiation
- Learn how to transform data into a trusted strategic asset using an information agenda
- Improve business performance by applying data analytics and optimization techniques, e.g. save $423 million and realize 95% improvement in on-time delivery
- Accelerate information intensive projects for immediate ROI, e.g., increasing daily deliveries by 100% and spotting new trends in seconds - not weeks
- Optimize your existing information infrastructure to achieve higher availability and improved ROI
- Add value to your organization by building your skills and knowledge
You can find Informix-specific information on the conference at Informix at Information On Demand
. Here are the top 5 reasons on the Informix side:
- The best in IBM technical training
- Hands-on-labs provide in-depth training
- Immediate return on investment
- First-hand experiences shared by customer speakers
- Everything your company needs in one location
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.
The day started with a keynote presentation by Dr. Anant Jhingran on "Cloud computing, databases and the role of IDS". He was assisted by our own Guy Bowerman. That was quite a good start to another great day of learning and networking.
There were 35 sessions covering subjects including Gillani Fourgen case tool, Genero report writer, IDS tasks and sensors, performance tuning, backups, trouble shooting, encryption expert, and index enhancements. Quite a range of subjects and that's not the half of it!
I had interesting conversations with some partners. One of them mentioned how the AGS Server Studio product transformed someone that knew nothing of databases into a database administrator in no time flat. Looks like the ease of use of IDS with the ease of use of AGS is an unbeatable combination. I also had a discussion about collecting and sharing the information about sensors worldwide to monitor the health of the planet. Talk about a stimulating conversation.
There is one more day of this! I don't know how much more I can take :-).