IIUG Insider (Issue #215) May 2018

Highlights: IBM Informix: the white swan

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.



Informix is still the best database. Having IBM and HCL joining forces I have confidence it will remain number one.
New cloud opportunities, new administration tools, new features.
Marketing and sales efforts. Like you can see in the highlights section Informix is the white Swan.
Swan stands for Sleep Well At Night. Actually with Informix you are calm during the day as well.
Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors

2) Highlights

2.1 IBM Informix: the white swan

A black swan is an event or occurrence that deviates beyond what is normally expected of a situation and is extremely difficult to predict. The term was popularized by Nassim Nicholas Taleb.

We have titled this paper “IBM Informix: the white swan”.

Not just because of the use of the Informix database should help users sleep well at night (SWAN) but also because the entire history of Informix makes that outcome entirely predictable.

White Paper by Bloor
Authors Philip Howard
Publish date May 2018

3) Conference Corner

3.1 IIUG WORLD 2018 – Registration is Now Open!

For what? IIUG WORLD 2018 being held October 28 2018 – November 1 2018. Registration for this historic event is now open.

The place: The Marriott Renaissance Arlington Capitol View. That is the Arlington (Virginia) next to Washington, DC. Capitol View. That says it all. In addition, the hotel is very accessible from any continent, and many flights from most places can be non-stop as well. In addition, you should not need a rental car. Ride the DC Metro instead!

The event: IIUG World 2018 will be the premier technical event for all things INFORMIX this year. We have moved the event to the fall, so as not to conflict with the IBM Data event (formerly Insight, then Watson, now Think) being held each spring.
Of course the most important reason to attend IIUG World 2018 is not the location, nor is it the superior FOOD and BEVERAGE served at the event. It is the content. Once again we will have more Informix content than ever, and are including a track focusing on IBM and HCL Technology, which may or not be Informix related.

Register and book your hotel now at www.iiug.org/iiugworld. Early Bird registration is available through July 15, 2018. IIUG members who register before July 15th can save a few hundred dollars by using their membership discount!

Register by July 15th and save $275.
Early Bird – through July 15, 2018 $1075.00
Regular – through September 15, 2018 $1350.00
Late Registration – through October 22, 2018 $1450.00

Remember IIUG members save an additional $100 off the above prices!!!
Interested in speaking? It’s easy!! We will even assist you in doing it the first time. No matter what your skill level is, we are sure you have something to teach others. Some tip, some process, some secrets that you know about Informix. You did not have to use the software for 20 plus years to speak. Best of all, any customer speaker who is selected (and trust us it is easy to get selected if you are a first timer), you will receive a full complementary conference pass. See more details at /iiugworld/2018/speakers/

Want to see what you have missed from past years? We now have a web page with session slides and documentation of all the previous IIUG events at /iiugworld/previous.php

So, register now for the biggest Informix event of the year in one of the most historic cities in America at /iiugworld or better yet join us as a speaker at /iiugworld/2018/speakers and get a complimentary full pass for this IIUG 2018.

Questions? Comments? Problems? Reach us at conference@iiug.org.

Hope to see you there.
IIUG World 2018 Planning Committee

4) RFE Corner

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 idea of the RFE corner is to provide a digest on new Informix RFEs and make those RFEs more visible and accessible to the community, so you can vote on those that are also important to you. By actively participating 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.
TOP 15 RFE’s

Abstract Status Votes Progr.
In-Place Alter for varchar, lvarchar and boolean Under Consideration 64 +5
Backup from RSS or HDR Secondaries using ontape, onunload, onbar, dbexport Under Consideration 59 0
SQL interface to obtain the temporary space usage (tables, hash, sorts…) Under Consideration 50 0
Obtain the query plan of a running query Under Consideration 49 0
Request to track and save a time stamp for last time an index was used. Nee… Delivered 40 0
Backup Individual database, not entire instance Submitted 35 0
Informix should be able to change owner on tables and any other objects Submitted 31 +3
Implement CREATE OR REPLACE option for stored procedures Under Consideration 30 +2
ALTER owner of database objects after creation Submitted 29 0
New feature to have FORCE_DDL_EXEC functionality for all DDL changes Submitted 24 0
Implementation of regular expressions (adding to LIKE/MATCHES functions) Under consideration 23 0
Ability to re-create views and procedures without dependent objects being dropped (34762 Under consideration 20 0
Need an onmode option to terminate orphaned, tightly coupled global transac… Under Consideration 19 0
SQL interface to obtain the temporary space usage (tables, hash, sorts…) Declined 19 0
allow repack of TBLSpace extents Under Consideration 18 0

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.

Gary Ben-Israel

5) Works for me

Thank you Eric for this nice Perl script.
I was writing this perl script yesterday and thought it might be good for the community. It has about 100 lines, so I don’t know how it can be included in the insider.
I just attached it to this email. You can test it too

Anyway here is the presentation:

If you are a regular user of the so many options gifted onstat command to monitor your preferred DBMS system, didn’t you ever dream about having a command that is as easy to run as onstat –d, but with a more directly explicit output ?
All you need is perl, which is present on any unix/linux system. And if you run Informix on windows, this tool will also work great if you install a perl distribution such as strawberry perl or activestate perl.

I did dream about this, and finally yesterday I decided to take some time to produce this tool.

How to run it?
Copy the utility somewhere on your Informix server.
Do a chmod +x ifmx_onstat_d.pl
Then type “ifmx_onstat_d.pl” (take care if its location is in the path, else type the full path)
If you want to read to output of an existing file containing onstat –d output, run ifmx_onstat_d.pl –infile <filename>

And here we go

Here is an example of what it shows:
IBM Informix Dynamic Server Version 12.10.FC10 -- On-Line -- Up 3 days 18:19:51 -- 57096176 Kbytes

address chunk dbspace offset size(Mb) free(Mb) bpages flags pathname
492d8268 1 rootdbs 0 9,765 9,523 PO-B– /ids_chunks/ids_system/v1210/perfcounters_root01.P.dbs
4f10e028 2 data03 0 57,617 16,693 PO-B– /ids_chunks/ids03/v1210/perfcounters_data03.P.dbs
4f10f028 3 data04 0 57,617 57,129 PO-B– /ids_chunks/ids04/v1210/perfcounters_data04.P.dbs
4f116028 4 temp03 0 14,648 14,648 PO-B– /ids_chunks/ids03/v1210/perfcounters_temp03.P.dbs
4f117028 5 data01 0 57,617 47,894 PO-B– /ids_chunks/ids01/v1210/perfcounters_data01.P.dbs
4f118028 6 data05 0 57,617 56,617 PO-B– /ids_chunks/ids05/v1210/perfcounters_data05.P.dbs
4f119028 7 phylog 0 15,625 5,829 PO-B– /ids_chunks/ids05/v1210/perfcounters_phylog.dbs
4f11a028 8 loglog 0 4,882 4,882 PO-B– /ids_chunks/ids05/v1210/perfcounters_loglog.dbs
4f11b028 9 temp04 0 14,648 14,648 PO-B– /ids_chunks/ids04/v1210/perfcounters_temp04.P.dbs
4f11c028 10 temp02 0 14,648 14,648 PO-B– /ids_chunks/ids02/v1210/perfcounters_temp02.P.dbs

Total by dbspace
Dbspace Type Size(Mb) Used(Mb) Free(Mb) Chunks #
rootdbs Admin 9,765 242 9,523 1
temp02 Temp 14,648 0 14,648 1
data03 Data_2K 57,617 40,923 16,693 1
data04 Data_2K 57,617 488 57,129 1
temp03 Temp 14,648 0 14,648 1
data01 Data_2K 57,617 9,723 47,894 1
data05 Data_2K 57,617 1,000 56,617 1
phylog Admin 15,625 9,795 5,829 1
loglog Admin 4,882 0 4,882 1
temp04 Temp 14,648 0 14,648 1

Global Total
Admin Data 30,273 10,037 20,236
Temp Data 43,945 0 43,945
User Data 230,468 52,134 178,333
Blob Data 0 0 0
Global Total 304,687 62,172 242,515

use Getopt::Long;
usage () if ( ! GetOptions(
"infile=s"=>\$Infile, # if specified, reads an onstat -d output file
"root=s"=>\$Root, # resets generic expression of rootdbs
"physlog=s"=>\$PhysLog, # resets generic expression of physical log dbspace
"logiclog=s"=>\$LogicLog, # resets generic expression of logical log dbspaces
) ) ;
if (!defined($Root)) {
if (!defined($PhysLog)) {
if (!defined($LogicLog) ) {
$LogicLog="log" ;
if ( !defined($Infile) ) {
$Cmd="onstat -d|";
} else {
open ONSTATD,$Cmd or die "Cannot run onstat -d";
while (<ONSTATD>) {
if (/\w+\s+(\d+)\s+\w+\s+\d+\s+\d+\s+(\d+)\s+([\w\s]{5})\s+\w+\s+(\w+)/) {
if ( $Dbspace{$DbsNum}->{Flags} =~ /..T../ ) {
$Dbspace{$DbsNum}->{DbsType} = "Temp";
} elsif ( $Dbspace{$DbsNum}->{Flags} =~ /..B../ ) {
$Dbspace{$DbsNum}->{DbsType} = "Blob";
} elsif ( $Dbspace{$DbsNum}->{Flags} =~ /..S../ ) {
$Dbspace{$DbsNum}->{DbsType} = "SmBlob";
} elsif ( $Dbspace{$DbsNum}->{Flags} =~ /..U../ ) {
$Dbspace{$DbsNum}->{DbsType} = "TempBlob";
} elsif ( $Dbspace{$DbsNum}->{Name} =~ /$Root|$PhysLog|$LogicLog/ ) {
$Dbspace{$DbsNum}->{DbsType} = "Admin";
} else {
$Dbspace{$DbsNum}->{DbsType} = "Data_" . $Dbspace{$DbsNum}->{PgSize}/1024 . "K";
} elsif ( /(\w+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+([\s\w]{3})\s+([\w\-]{6})\s+([\w\-\/\.]+)/ ) {
if ( $ChunkLine++ < 1 ) {
printf "address chunk dbspace offset size(Mb) free(Mb) bpages flags pathname\n";
$Dbspace{$DbsNum}->{Size} += $ChunkSize;
$Dbspace{$DbsNum}->{ChunksNbr} ++;
$TotalSize += $ChunkSize;
$Dbspace{$DbsNum}->{Free} += $ChunkFree;
$TotalFree += $ChunkFree;
if ( $Dbspace{$DbsNum}->{DbsType} =~ /Admin/ ) {
$IfmxAdminSize += $ChunkSize ;
$IfmxAdminFree += $ChunkFree ;
} elsif ( $Dbspace{$DbsNum}->{DbsType} =~ /Temp/ ) {
$IfmxTempSize += $ChunkSize ;
$IfmxTempFree += $ChunkFree ;
} elsif ( $Dbspace{$DbsNum}->{DbsType} =~ /Blob/ ) {
$IfmxBlobSize += $ChunkSize ;
$IfmxBlobFree += $ChunkFree ;
} else {
$IfmxDataSize += $ChunkSize ;
$IfmxDataFree += $ChunkFree ;
printf "%s\t%3d\t%15s\t\%10d\t%10s\t%10s\t%3s\t%6s\t%s\n",$Address,$ChunkNum,$Dbspace{$DbsNum}->{Name},$Offset,$ChunkSizeFmt,$ChunkFreeFmt,$BpPages,$Flags,$PathName;
} elsif (/Informix Dynamic Server Version/ ) {
print $_ . "\n" ;
print "Chunks\n" ;
printf "\nTotal by dbspace\nDbspace Type Size(Mb) Used(Mb) Free(Mb) Chunks #\n" ;
foreach $dbspace ( sort keys %Dbspace ) {
printf "%-10s\t%-7s\t%12s\t%12s\t%12s\t%d\n",
$Dbspace{$dbspace}->{Name}, $Dbspace{$dbspace}->{DbsType},
$Dbspace{$dbspace}->{ChunksNbr} ;
printf "\nGlobal Total\n";
printf "Admin Data %12s %12s %12s\n",commify($IfmxAdminSize),commify($IfmxAdminSize-$IfmxAdminFree),commify($IfmxAdminFree);
printf "Temp Data %12s %12s %12s\n",commify($IfmxTempSize),commify($IfmxTempSize-$IfmxTempFree),commify($IfmxTempFree);
printf "User Data %12s %12s %12s\n",commify($IfmxDataSize),commify($IfmxDataSize-$IfmxDataFree),commify($IfmxDataFree);
printf "Blob Data %12s %12s %12s\n",commify($IfmxBlobSize),commify($IfmxBlobSize-$IfmxBlobFree),commify($IfmxBlobFree);
printf "Global Total %12s %12s %12s\n",commify($TotalSize),commify($TotalSize-$TotalFree),commify($TotalFree) ;
sub commify {
my $text = $_[0];
$text =~ s/\.\d+//g;
$text = reverse $text;
$text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
my $value=scalar reverse $text;
return $value;

Eric Vercelletto

6) Informix Corner

6.1 Discovering best practices for IoT at Think 2018

A blog by

Rajesh Govindan
Informix Product Marketing & Channel Initiatives
IBM Hybrid Data Management


7) Informix Resources

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

7.1 Informix Blogs and Wikis

Blogs and Wikis that have been updated during the last month

More Blogs and Wikis

7.2 Social Media

LinkedIn: https://www.linkedin.com/groups/25049
Twitter : https://twitter.com/iiug
Facebook : https://www.facebook.com/IIUG.Informix
Informix IoT Channel : https://ibm.biz/BdH2nm

7.3 Forums, Groups, Videos, and Magazines

  • 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

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.