Editorial
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
gary@iiug.org
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.
https://ibm-data-and-ai.ideas.aha.io/?project=INFX
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.
Recent
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!
EXPAND YOUR HORIZONS
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;
loop
fetch find_curs into j;
exit when SQLCODE = 100;
let j = 1;
exit;
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;
loop
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
www.iiug.org
IBM Informix community
/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