IIUG Insider (Issue #191) May 2016

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 2016 is over and we are thinking about 2017. I was hoping to see more of you at our conference and I’m still optimistic hoping that we will have many more attendees in 2017.

In the meantime let’s enjoy Informix.

Gary Ben-Israel


IIUG has a new website

The new IIUG website has a new look. If you type http://www.iiug.org you will be redirected to the new URL.

Our new home page has a nice look and feel. Try it out and enjoy.

Gary Ben-Israel

Conference corner

IIUG 2016 – Editor’s Recap

IIUG 2016 was a great event. Excellent technical sessions, big variety of topics and best Informix speakers.
The only problem was the low attendance. I hope it was a onetime thing. I hope to see many more next year.

The IIUG Planning Committee and the IIUG Board of Directors are putting in a huge effort.

I sincerely think it is an event worth attending.

Please let us know what will it take to get you to the 2017 event?

Email me gary@iiug.org or anyone on the IIUG Board or Planning Committee.

Gary Ben-Israel

IIUG 2016 – President’s Comments

It’s not often I have a chance to write a Presidents note column as part of the Insider but with lots and lots of things going on, this issue I need to give some credit and Thanks all around.

First Thanks to the IIUG Conference team of Cindy Lichtenauer, John Fahey, Andrew Ford, Rhonda Hackenburg, David Link, Donna Miller and Bruce Simms for putting together an excellent 2016 IIUG Event. Also, thank you to the entire lineup of Speakers and in case you missed it here is some links for you:

Get a copy of the outstanding IIUG 2016 Guide at www.iiug2016.org/guide.pdf
Get a copy of the many sessions at www.iiug2016.org/sessions.zip

As for IIUG 2017, we will begin our search for a location and exact dates starting very soon. Watch the Insider for the announcement which we hope to make by October 2016 or sooner.
Also special Thanks go to the IIUG Server/Web team. As Gary mentioned above, we have a new web page / server setup at www.iiug.org. Be advised, the new layout and setup is NOT complete and we are still in the process of moving and converting so please bear with us. See something wrong, drop us an email to webmaster@iiug.org.

Lots of news on the IIUG front and we are sorry but, we missed the IIUG elections. Due to some personal issues on my end, we had to push them off. We hope to get this announced and done as soon as possible, so please bear with us.

Stuart Litel – IIUG President

IIUG 2016 – Thanks to Everyone

The IIUG Planning Committee would like to thank everyone who helped make IIUG 2016 a success. First we would like to thank the IIUG Board of Directors for selecting such a fabulous venue for this event and Sawgrass Marriott Golf Resort and Spa management and staff. Thanks to IBM, our Platinum sponsor, and our Gold Sponsor Four Js. Also, thanks to our Patron Sponsors: ASK Database Management, Advanced DataTools, Fourth Generation Software Corp and The Municipal Court of Seattle.

A big thanks to all of our presenters, without you there can be no IIUG event because there would be no content. We appreciate you technical expertise and willingness the share your knowledge with others. A special thanks to our user presenters, we understand how difficult it can be for you to take yourself away from work.

A special thanks to all of our attendees. Although our numbers were lower than past years, it is nice to know that there are many users who still support Informix and know what an excellent product it is. We received fantastic feedback from you and are reviewing your comments concerning IIUG 2016 as we are preparing for IIUG 2017.

We especially want to thank to attendees who participated in our evening events and thought we could congratulate our winners:
Gary Andrus The Hole-In-One winner who walked away with a new laptop.

Glow-in-the-Dark Mini Golf Challenge:

Closest to Hole 9 Nick Geib
Closest to Hole 18 Mark Collins

Lowest score overall:

  1. Tim Parke
  2. Mike Walker
  3. Patrick Lynch

Non-lowest score overall: Ang Yip Keong
Most Strokes on one hole: Raul

Make sure to congratulate the 2016 IIUG Board of Directors award winners: David Link and Shawn Moe. Both are very deserving.

Again, thanks to everyone!! Hope to see you in 2017. Bring a friend.

IIUG Planning Committee

Works for me

In this section I will write about things that help me in my day to day work.
Most DBAs probably have their own ways to perform these tasks which may be different than the way I do them. So, if you find an error or can think of a better way, please let me know. If not feel, free to use these tips as is or modify them to fit your needs.
Today’s topic is a stored procedure my developers use when they try to delete a row and receive the following type of message:

Key value for constraint (informix.u2705_9212) is still being referenced

This is really frustrating. The constraint points to the table’s primary key (which does not help much) but even when you figure out the referencing tables it can take you a while to scan through them and find those that are still referenced.
We use a stored procedure called who_the_f. parameters are the referenced table name and the primary key value for the deleted row. It returns the referencing tables separated by comas.

If there are no referencing tables it returns: “No references found!”

Note: This stored procedure uses the view back_ref_view which was described in the April Insider.

create function exists_in_table(t_nm nvarchar(128),
c_name nvarchar(128), chk_val int)
returning smallint
define sel_str nvarchar(255);
define k,j int;
let sel_str =
"select "||c_name||" from "||t_nm||
" where "||c_name||" = "||chk_val;
prepare prp_st from sel_str;
declare find_curs cursor for prp_st;
open find_curs;
let j = 0;
fetch find_curs into k;
exit when SQLCODE = 100;
let j = 1;
end loop
close find_curs;
free find_curs;
return j;
end function;
create function who_the_f(tb_nm nvarchar(128), chk_val int)
returning lvarchar(2000)
define ret_str lvarchar(2000);
define j smallint;
define k smallint;
define sel_str lvarchar(500);
define referencing_t nvarchar(33);
define referencing_c nvarchar(33);
let k = 0;
let ret_str = "";
let sel_str =
"select referencing_table, referencing_column"||
" from back_ref_view where referenced_table = '"||tb_nm||
"' and cascading_delete = 'No'";
prepare prep_st1 from sel_str;
declare back_ref_curs cursor for prep_st1;
open back_ref_curs;
fetch back_ref_curs
into referencing_t, referencing_c;
exit when SQLCODE = 100;
let j = exists_in_table(referencing_t, referencing_c, chk_val);
if j > 0 then
let k = k + 1;
if k > 1 then
let ret_str = ret_str||",";
end if
let ret_str = ret_str||referencing_t;
end if
end loop;
close back_ref_curs;
free back_ref_curs;
if k = 0 then
let ret_str = "No references found!";
end if
return ret_str;
end function;

Gary Ben-Israel

Informix news

Port Authority to spend $2.8b on BPO

The Gleaner

Apr 21

Newly released government documents indicate that the investment will include $1.1 billion of capital expenditure on a 63,000-square foot building at the Montego Free Zone and $1.7 billion at the Portmore Informix Centre in St Catherine.


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, is submitted for 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 the 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 a few days ago and will definitely keep registered, due to the value of the information delivered.

Check at this place.

New RFEs for May 2016

Slow activity for May in terms of new RFEs, but the vote activity has been consistent in May, see TOP 15 in next section.

Log DRDA listener messages to online.log similar to current logging of the … Votes 2

The DRDA listener (drsoctcp) is not logging any error messages to the online.log. The SQLI listener (onsoctcp) does log messages when, for example a wrong username is being used to connect to the inst…

TOP 15 RFE’s

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


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


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


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


Obtain the query plan of a running query Under Consideration 28


New feature to have FORCE_DDL_EXEC functionality for all DDL changes Submitted 22


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


Backup Individual database, not entire instance Submitted 20


ALTER owner of database objects after creation Submitted 18


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


Allow defining rolling container windows defined on user defined calendars Submitted 11


allow repack of TBLSpace extents Submitted 9


Informix ER: Increase maximum allowed replicates Submitted 9


A Java JDBC program has a problem with displaying the content of column “fe… Submitted 6


Mark a row as “archived” so it will not show up in regular SELECT (or other… Submitted 6


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.

Gary Ben-Israel

Informix corner

Welcome to the 2016 Informix Roadshow

Informix 12.1 is simply powerful.

Coming to a Location Near You

What is the Informix Roadshow?

The 2016 Informix Roadshow is a two-day, deep-dive technical event that gives you hands-on experience with the latest IBM® Informix® release features, and Informix Warehousing technology. This roadshow is designed to help you gain fresh, innovative ideas for optimizing your business performance and creative competitive advantage – and it’s absolutely free to attend these events.

The Roadshow topics include:

  • Latest features of IDS 12
  • Replication Technologies
  • Compression
  • Storage Manager
  • Time Series – Smart Metering
  • Informix Warehouse Accelerator
  • Embed and Autonomics
  • NoSql
  • Internet of Things
  • Roadmap

The current Informix Roadshow presentations and agenda, both of which are subject to change, and the last four years Informix Roadshow presentations are available here.

The current Informix Roadshow Agenda is here subject to change.

If you would like to try Informix for free, links to various software downloads are here:

Next events will take place in India:

Bangalore (June 2-3)
Hyderabad (June 6-7)
Mumbai (June 9-10)

Hurry up. Limited seats. Register and confirm your availability for the 2016 Informix Roadshow

Stay tuned for upcoming events:

Date Location
September 12-13 Zagreb, Croatia
September 15-16 Zagreb, Croatia
September 29-30 Buenos Aires, Argentina
October 3-4 Sao Paulo, Brazil
October 6-7 Rio de Janeiro, Brazil
October 11-12 Mexico City, Mexico

Calendar of events

June – 2016

Date Event Location Contact
2-3 2016 Informix Roadshow Bangalore, India
6-7 2016 Informix Roadshow Hyderabad, India
9-10 2016 Informix Roadshow Mumbai, India

September – 2016

Date Event Location Contact
12-13 2016 Informix Roadshow Zagreb, Croatia
15-16 2016 Informix Roadshow Zagreb, Croatia
29-30 2016 Informix Roadshow Buenos Aires, Argentina

October – 2016

Date Event Location Contact
3-4 2016 Informix Roadshow Sao Paulo, Brazil
6-7 2016 Informix Roadshow Rio de Janeiro, Brazil
11-12 2016 Informix Roadshow Mexico City, Mexico

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 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

IIUG Insider sponsored by:


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.