IIUG Insider (Issue #196) October 2016

Highlights: What do you want to hear at the 2017 IIUG Event?

Welcome to the International Informix Users Group (IIUG) Insider! Designed for IIUG members and Informix user group leaders, this publication contains timely and relevant information for the IBM Informix community.



The IIUG board of Directors met at IBM World of Watson 2017. It was a great conference. 17,000 attendees and plenty interesting sessions. We at the IIUG are planning our 2017 event. We hope to see many of you there.

Mark your calendars and stay tuned.

Gary Ben-Israel


What do you want to hear at the 2017 IIUG Event?

The International Informix Users Group is here to support the User community. We need to know what topics we can present at our IIUG 2017 Event. Please shape the content of this year’s conference by taking the following survey:

Survey of Topics to Present

Gary Ben-Israel

Conference corner

IIUG 2017 – April 23-27 Raleigh NC, USA

The IIUG Planning Committee is currently accepting presentation proposals for IIUG 2017, which is to be held Sunday April 23 – Thursday April 27, 2017 at The Marriott City Center, Raleigh NC, USA. Registration will be open soon. Go to www.iiug2017.org for more information.

The purpose of this note is to encourage you, one of the many loyal members of the Informix community, to volunteer to speak at IIUG 2017. Our event stands out in our industry in large part due to the vast diversity of our speakers. While many sessions are hosted by Informix technical pros, a key component of the event is the “end user” participation. We all have vastly different backgrounds, both personal and professional. We all use Informix differently. The Informix feature-set has grown significantly in the past several years. There aren’t many of us who know ANYTHING about this new stuff! Would you like to share your unique knowledge with the rest of the community?

IIUG 2017 will be comprised of three days of technical/user sessions, a series of hands-on-labs and one day of comprehensive tutorials intended to reinforce the power of Informix. Presentation proposals should be brief, highlighting the Informix technical solutions, real world experiences or trade tips you will include in your presentation. Multiple presentations are encouraged. Speaker guidelines can be found at www.iiug2017.org/speakers/call.html

All User Speakers will receive:

  1. Complimentary IIUG 2017 pass (note that this does not include Conference Tutorials but these may be purchased separately for a nominal fee).
  2. Speakers who present TWO sessions will receive the above pass PLUS FREE Conference Tutorials.

Use www.iiug2017.org/speakers to register your proposal. These “proposals” need only to be a well thought out idea or outline of what your presentation will be. We won’t need the actual presentation until next March. So don’t be shy!

The deadline for submitting IIUG 2017 speaker proposals is November 14, 2016. For your convenience, speaker logins have been retained for those who submitted an IIUG 2016 speaker proposal. Please submit your proposal and a brief biography today for consideration.

See you in Raleigh!

The IIUG 2017 Conference Team

Conference questions can be sent to conference@iiug.org

4) RFE corner

Just in case you are not aware, some time ago IBM created a public website to collect the requests for new features directly from users. The RFE (Requests For Enhancements) website is included in developerWorks. You can access it here.

Once you logged in with your usual IBM ID, choose “Information Management” in the Brand dropdown box and “Informix Servers” or “Client Products” in the Products dropdown box.

The interesting thing is that any request, including your request, if you place one, are submitted to be voted on. This means the RFEs that receive more votes have a greater chance to be considered by the architecture and development teams for further consideration. In other words, this IS your opportunity to provide enhancement ideas even if you are not the biggest IBM customer on the planet earth.

Some RFEs will be of great interest, others will not seem useful to you. This is why your opinion is important. Do not hesitate to vote and place comments!

The idea of the RFE corner is to provide a digest on new Informix RFEs and make those RFEs more visible and accessible for the community, so that you can vote for them in a faster and easier way. By participating actively in this website, IBM will have solid and useful elements from the customer base to introduce new functionality to Informix product.

Also in the area of IBM website, a new functionality has been released: MyNotifications. You will want to register this webpage in order to receive the notifications of your choice (new product defects, new patch release, new versions etc…, on the frequency of your choice (daily, weekly). I have registered and will definitely remain registered, due to the value of the information delivered.

Check at this place.

New RFEs for October 2016

Only 2 RFEs for this month. Nonetheless some of the top 14 are still progressing.

In a HDR cluster, be able to shut down the primary without triggering the a… Votes: 1 In a HDR cluster, one should be able to shut down the primary instance without triggering the auto-failover regardless the setting of DRAUTO and the existence of connection managers. Request to have an option to shutdown a primary server without having to stop the CMs etc. this is valuable only for planned maintenance shutdowns.
Onclean to display shmem key and master daemon process id before removing t… Votes: 2 Onclean shows the shmem keys and PID after confirmation to proceed with killing.
Request to show shmem keys and pid BEFORE prompting for confirmation

TOP 14 RFE’s

Abstract Status Votes Progr.
In-Place Alter for varchar, lvarchar and boolean

Under Consideration



Backup from RSS or HDR Secondaries using ontape, onunload, onbar, dbexport

Under Consideration



SQL interface to obtain the temporary space usage (tables, hash, sorts…)




Obtain the query plan of a running query

Under Consideration



Request to track and save a time stamp for last time an index was used. Nee…




Backup Individual database, not entire instance




New feature to have FORCE_DDL_EXEC functionality for all DDL changes




Implementation of regular expressions (adding to LIKE/MATCHES functions)

Under Consideration



Implement CREATE OR REPLACE option for stored procedures

Under Consideration



Allow triggers install/updates without taking an outage for the box

Under Consideration



Need an onmode option to terminate orphaned, tightly coupled global transac…

Under Consideration



Allow “group commit” as other RDBMS

Under Consideration



Add constant to centralized Key of ER Consolidation Model




Ability to re-create views and procedures without dependent objects being dropped (34762

Under consideration



Do not forget to vote for one or several of those RFE’s if they fit your requirements.

You can access each RFE by clicking on the above links. At the bottom of each RFE page you will find a hyperlink to vote for it. You will see the Request stats, including number of votes for this request, on the right side of the request page. The more votes, the greater the chance an enhancement will be addressed by the Development Team, taking into consideration the general interest.

Take some time to examine the full list and vote for the enhancements you would like to see implemented.

Eric Vercelletto

Developer corner

Why didn’t I have JSON available 15 years ago?

Interview with Lannig S.

Lannig S. has been responsible for developments and Informix DBA for many years in a large industrial group specialized in the manufacture of batteries and plastic films. The company adopted the Informix technology in the early 90s. It is one of those companies that are definitely not thinking about replacing Informix. Why would they operate such a change when the TCO is excellent, the platform is highly available and the performance is great, even with a small hardware configuration?

While attending a presentation of the Informix 12.10 new features, Lannig (aka LS) had a revelation and decided to conduct a POC upon this idea, just for the sakes of curiosity. This POC, based on a real in-house case study, would confirm his intuition and radically change his vision about the use of NoSQL in a hybrid environment with IBM Informix 12.10. He wished to share his experience with the community.

IIUG: what did you have in mind when you decided to redesign this functionality using NoSQL?

LS: our factories are dedicated to making a very wide range of products, including from simple products to complex and composite products made of the single ones. Classifying those products is a key and daily concern for us.

IIUG: If my understanding is correct, an item can be composed of one to many items, inducing frequently a high rate of recursivity on the item number column, correct?
LS: Absolutely! By the way, years ago, we had issues with this model when we tried to use recursive stored procedures. SPL had nasty limitations, not to mention an enormous increase of memory resources usage. But our most painful concerns turned to come from the items attributes side.

IIUG: What was the challenge?
LS: The industry was evolving rapidly, leading the products engineers to request more attributes for their products. The data model consisting in one column per attribute became obsolete just because it required to perform an ALTER TABLE, which is unacceptable in a 24×7 production system.

IIUG: So you had to normalize the schema and move the items attributes to a separate 1 to N table, correct?
LS: Exactly, no rocket science, but we had to redevelop this part of the application. In addition, the attributes had more and more different types, which obliged to create a new attributes type nomenclature based on a large VARCHAR column.

IIUG: This looks as an appropriate design on theory. It provides functional flexibility, but what about the performance?
LS: It kind of worked, but the number of table rows increased almost exponentially: although the consequences were not so negative for simple items, they really were noticeable for complex composed items. We gained flexibility, but the performance decreased significantly. In addition, having heterogeneous types of attributes materialized by a VARCHAR column prevented us from using operations such as SQL aggregations and calculations on numeric values.

IIUG: I guess redeveloping and tuning this part has been long and difficult?
LS: Good guess! The development and implementation of this solution took us a lot of time, and fixing severe performance problems has been time consuming for my team. An appropriate use of stored procedures helped us to bring the performance to an acceptable level.

IIUG: This is when you decided to use a not so famous SELECT functionality that has been key to provide acceptable performance. Can you quickly talk about this feature?
LS: We started using SELECT / CONNECT BY. We found this new feature by chance in the Informix 11.50 release notes. This provided a very efficient solution to the performance issue generated by SELF JOINS. Using this syntax allows browsing a hierarchical tree like product nomenclature, downwards or upwards, and avoid using complex and slow queries. We loved this feature and definitely recommend any developer dealing with recursive queries to take a look at it.

IIUG: So you found an effective solution to manage hierarchical queries more efficiently, but no solution to handle the items complex attribute issue.
LS: The revelation came when I understood what JSON/BSON new native datatype is. The idea was to create a generic BSON “attributes” column, storing a key/value pairs for each attribute, hence getting rid of the structured schema: creating a new attribute will from now on consist in having the application create this new key/value pair on the fly et voila!

IIUG: Indeed, on paper, it sounds interesting. What benefits did you identify after implementing your idea on a real size environment?
LS: The big advantage is to get rid of the attributes table and relocate the attributes in the items table. The consequence of this change is a drastic reduction of the accessed data volume: for the more complex articles, the response time decreased from 5 minutes to a few seconds!

IIUG: As a developer, what benefits have you drawn from this solution?
LS: Beyond the fact to find that almost 15 years of development on this issue were replaced in 1 work day using Informix NoSQL, this solution undeniably resulted in very significant development cost savings, unprecedented flexibility to manage the attributes and finally great response times: the application users are now happy!

IIUG: Have you identified “collateral benefits” and new directions for improvement?
LS: We plan to include images in the attributes, there is nothing easier with NoSQL. We also saw that it was now possible to use arrays as a data type, for example an array of 12 numeric fields to store the number of monthly sales for this article. Also very important: attributes handled as NoSQL fields can be accessed by indexes, using either MongoDB syntax or traditional SQL syntax using casts.

IIUG: This is true, even if a BSON column somewhat looks like a SMARTBLOB, its content is organized, discriminated and can be indexed just as a column in a classic SQL table.
LS: We even written a join between an SQL table column with a NoSQL collection field, and it worked great! Using NoSql gave us a simple, lightning fast and easy to maintain solution that solved a 15 years issue in our company! Hybrid development is not just a hype, it has a real added value for us.

IIUG: Do you have trouble finding a development tool that goes well with NoSQL?
LS: With our usual development tool (Windev ©) we had no difficulty in developping this functionality. We would love to connect the NoSql part to spreadsheets, our user are big fans of this type of tools.

IIUG: And what do you think about this new way of implementing solutions as an Informix DBA manager?
MS: The very good thing is that absolutely no additional work for DBAs is needed: no need to hire a NoSQL DBA, no need to develop and maintain additional complex ETL or EAI procedures. Nothing has changed for the DBA and system team: the same Informix system we have been loving for 20 years now because we can trust it!

Eric Vercelletto

Calendar of events

December – 2016







McLean, VA, USA


April – 2017






IIUG 2017

Raleigh, NC, USA


Informix resources

IBM Informix home page www.informix.com or directly at: http://www-01.ibm.com/software/data/informix/

Informix Blogs and Wikis

Blogs and Wikis that have been updated during the last month

More Blogs and Wikis

Social media

Linkedin: https://www.linkedin.com/groups/25049
Twitter: https://twitter.com/IBM_Informix
Facebook: https://www.facebook.com/IBM.Informix
YouTube: https://ibm.biz/BdH2nb
Informix IoT Channel: https://ibm.biz/BdH2nm

Forums, Groups, Videos, and Magazines

  • The IIUG forums at /forums/technical.php
  • Informix Marketing channel on YouTube http://www.youtube.com/user/informixmarketing?feature=results_main
  • IBM DATABASE MAGAZINE at http://www.ibmdatabasemag.com
  • Credentials, the IBM Certification Newsletter at http://www-03.ibm.com/certify/email/201307credentials.shtml
  • The Informix Zone at http://www.informix-zone.com
  • There is now an Informix group on LinkedIn. The group is called “Informix Supporter”, so anyone loving Informix can join, from current IBM employees, former Informix employees, to users. It will also be a good occasion to get in touch with others or long-time-no-seen friends. If you fancy showing the Informix logo on your profile, join. To join, simply go to: http://www.linkedin.com/e/gis/25049/5E4B2048E558

Useful Links

In response to your input, we have created a new page on IIUG web site containing all the links we use to include. Please find it at /quicklinks.html

Closing and Credits

The International Informix Users Group (IIUG) is an organization designed to enhance communications between its worldwide user community and IBM. The IIUG’s membership database now exceeds 25,000 entries and enjoys the support and commitment of IBM’s Information Management division. Key programs include local user groups and special interest groups, which we promote and assist from launch through growth.

Sources: IIUG Board of Directors

IBM Corp.

Editor: Gary Ben-Israel

For comments, please send an email to gary@iiug.org

Categorized as Insider

By Vicente Salvador

Board member since 2014, a user since 1989 and Informix fan. I'am software architect which allow me to combine technical and business skills.