IIUG Insider (Issue #235) February 2020

Highlights: Over 250 members in the IBM Informix community forum

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.


1) Editorial

2) Highlights

  2.1 Over 250 Members in the IBM Informix Community Forum

3) IBM Informix Forum Corner

4) RFE Corner

5) Informix corner

  5.1 IIUG Announces First in Series of Worldwide Informix Tech Day Events to Take Place in India

  5.2 Informix Webinars

6 Events

  6.1 IBM Think 2020 – San Francisco

7) How to

8) Informix Resources

  8.1 Informix Blogs and Wikis

  8.2 Social Media

  8.3 Forums, Groups, Videos, and Magazines


Once upon a time there was a campaign called “Where is Informix”

The answer was “Informix is everywhere”. Do you still remember that video? https://www.youtube.com/watch?v=SaYlmpIuE_o

It is from way back when IBM was making videos about Informix. Today I doubt there are many IBMers who know it still exists.

Well it does.

The question is, for how long? I wish I had an answer. The IIUG Board is trying to find out about the Informix roadmap for 2020 and beyond. Is there a roadmap? What’s in it? I’ll let you know when I have an answer.

Stay tuned.

Gary Ben-Israel

IIUG Insider Editor

IIUG Board of Directors


2) Highlights

2.1 Over 250 Members in the IBM Informix Community Forum

The IBM Informix community forum has over 250 members and over 700 posts.

This is the best place to find a solution to your questions and help other Informix users with theirs.

Just go to /community. If you are not a member, join.

Old posts from the IIUG IDS forum will be imported into this forum in the near future.

I will report the latest posts in the Insider but recommend you use the forum directly.

Gary Ben-Israel

3) IBM Informix Forum Corner

Join and visit the IBM Informix community forum at /community

Recent posts

Can we track instance ontape (or for that matter onbar too) restores?

User Defined Routine (UDR) example required – OSAD Array

IDS 12.10.FC12W1 Memory block header corruption

sysblobs.spacename: partition, dbspace, or family

Gary Ben-Israel

4) RFE Corner

IBM has created a new RFE – Request For Enhancements website.


Please visit this site to vote for your favorite enhancements and place new requests.

As you can see, it is a new RFE site. It is friendly and has a nice look and feel.


Unblocked Checkpoints on HDR

When a checkpoint occurs on a primary it blocks the checkpoint temporaily while the checkpoint is applied to the HDR. On an RSS this can be prevented by configuring the config value RSS_NONBLOCKING_CKPT so log replay continues during checkpointing…

Votes: 5

Created February, 25 2020

Status: Needs review

Column level encryption, an option to cleanup passwords on certain/all rows for DBSO or Informix

All users which are granted permissions for insert/modify data into tables are also allowed to use their own password for column level data encryption. But not all user are their whole lifetime in the company and move. What happened with their …

Votes: 1

Created February 17, 2020

Status: Needs review

Column encryption, select rows with different passwords without errors

When using char/varchar columns with the option of encryption each user can choose his own password and more worse multiple passwords. All is well in case I read the data without decrypt() then I see the raw data for encrypected data and the re…

Votes: 1

Created February 17, 2020

Status: Needs review

Popular (Over 50 votes)

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

In HDR environments, being able to backup or export databases from and HDR, HDR read-only, or an RSS secondary server is critical. Programs like ontape, onunload, dbexport, and onbar generate locks on tables during backups and data-exports. This creates problems while applications and users using Informix try to use the system during backups in 24 x 7 x 365 production environments. Being able to backup or export data on secondary systems is critical for compliance and backup integrity.

Votes: 94

Created: December 24, 2018

Status: Future consideration

Obtain the query plan of a running query

Many times a DBA is called to check upon a slow process. Most of the times those processes are running a slow query.

Sometimes it’s hard to know if the query is using the best query plan or not. A DBA can reproduce the query, but it it was prepared without values or if the statistics were changed after it started there is no guarantee that the query plan seen by the DBA is the same as the running query.

We have “onstat -g pqs” which sometimes can give us a clue, but it’s mostly cryptic and undocumented. If a user has X-Windows he can try xtree, but it’s a bit strange for todays standards.

We also have SQLTRACE, but if it was not set when the query was launched it will not capture the info (and besides, due to the circular nature of the buffer it may not be there at the time we need it).

A simple pseudo-table in sysmaster could probably implement this with ease.

It could also be extended to support the “last” query plan making it easy to get the query plan in any tool

Votes: 80

Created: December 24, 2018

Status: Future consideration

Backup Individual database, not entire instance

Need a mechanism, similar to ontape, to backup a live database (as opposed to entire instance), without locking it.

Votes: 66

Created: December 24, 2018

Status: Future consideration

Informix should be able to change owner on tables and any other objects

If user=informix creates all database and all tables, then programmer creates table but user=informix cannot change it. Only drop it.

Votes: 64

Created: December 24, 2018

Status: Future consideration

Gary Ben-Israel

5) Informix corner

5.1 IIUG Announces First in Series of Worldwide Informix Tech Day Events to Take Place in India

The IIUG is excited to announce the first in a series of Worldwide Informix Tech Day Events.

The IIUG will be coming to you in 2020!


Take control of your data, gain new insights from it, and drive digital transformation and innovation across your business! Come spend a day with the IBM and HCL Informix teams during this technical exchange covering the unmatched capabilities of Informix. Learn new ways to deliver significantly greater business value and operational efficiency from our experts.

Clients and partners interested in IBM Informix, or looking to address data management challenges, are invited to this event.

The first two locations are:

March 24 – Bengaluru, India

Bengaluru Registration

March 26 – Chennai, India

Chennai Registration

Stay tuned for other locations.

5.2 Informix Webinars

Informix 14.10 for App Dev

Date and time: March 24th, 1pm Eastern/12pm Central

Speaker: Brian Hughes, Informix Software Engineer, HCL Software

Description: Explore the breadth of connectivity options from your applications to your data. In this session, we will cover the various drivers, connection endpoints, new features, and future direction for enabling applications and developers to get the most out of Informix for your business.

Streaming Analytics Using Asynchronous Post Commit Triggers

Date and time: April 21st, 1pm Eastern/12pm Central

Speaker: Nagaraju Inturi, Software Engineer, HCL Software

Description: Aggregate and generate real-time reports on OLTP data with zero impact to OLTP applications and without polluting buffer pool with expensive analytic queries. Other use cases include:

  • Data transformations

  • Update external systems like MQTT, Hadoop and Graph databases

  • Replicate data for tables that do not have primary key, unique index or ER key.

Informix 14.10 Now Supports Common Table Expressions!

Date and time: May19th, 1pm Eastern/12pm Central

Speaker: Jinming Xiao, Informix SQL/Optimizer Engineer, HCL Software

Description: Common Table Expressions (CTE) are a SQL standard feature, CTE simplify queries and make queries more readable. Use recursive CTE to query hierarchical data, and tree/graph structured data. Also, you can do programming tasks with CTE.

Join this session to hear more about, and see, a demo with Informix 14.10 release support of:

  • Non-recursive CTE

  • Recursive CTE

  • Nested recursive CTEs in one query

  • CTE with Insert, Update, Delete statements

6 Events

6.1 IBM Think 2020 – San Francisco

May 4, 2020 – May 7, 2020

Moscone Center – SF, 747 Howard St
San Francisco, CA 94103 United States + Google Map

Find out more »

7) How to

When you try to delete a row and receive the following type of message:

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

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

The following SPL routine can be used.

who_the_f(table_name, key)

The stored procedure’s 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: NULL

create function exists_in_table(t_nm nvarchar(128),

c_name nvarchar(128), chk_val int)

returning smallint

define sel_str nvarchar(255);

define 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 j;

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 = NULL;

end if

return ret_str;

end function;

8) Informix Resources

IIUG website

IBM Informix community

8.1 Informix Blogs and Wikis

Blogs and Wikis that have been updated during the last month

More Blogs and Wikis

Art Kagel http://informix-myview.blogspot.co.il/ (Last updated November 2019)

8.2 Social Media

Linkedin: https://www.linkedin.com/groups/25049

Twitter : https://twitter.com/iiug

Facebook : https://www.facebook.com/IIUG.Informix

8.3 Forums, Groups, Videos, and Magazines

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.