Highlights: IIUG 2021 Survey

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.

Editorial

Last month I said we have a reason to be optimistic about Informix. I’m still optimistic about the product.

Informix’s position both at IBM and HCL is improving. And content from IBM, HCL and users is included as well. I’m doing my best to supply useful content. Please send me technical articles. In our 2021 survey 54.8% answered the question:

How did you find out about this survey? IIUG Insider. More than all other methods combined.

So, it seems my hard work is not in vain.

Thank you all for your support.

Gary Ben-Israel
IIUG Insider Editor

IIUG Board of Directors

gary@iiug.org

Highlights

IIUG 2021 Survey

Many of you recall that IIUG conducted a short survey at the end of 2021, which was continued in beginning of 2022. We had several questions, and most of them were repeated from the previous survey. That was intentional, to allow us to keep track on potential changes over time, and this is something we intent to do each year.

As it usually goes with surveys, some results turn out to be expected, some turn out to be not particularly interesting, and some do not lead to clear conclusion. However, we’d like to share some of the results that we do find interesting, over the next few editions of the Insider. This time, it’s the story of Informix versions.

In 2020, we asked you to check all the versions you are using in production. Of course, all of the Informix versions were there, and it was interesting to see how many people still use the outdated ones. 49% of the respondents back then said they are using 11.50 or 11.70, and 21% said they have even older version in production. This is no surprise, as Informix is very well known to be reliable, long-running database, and many of the systems running on these versions are in fact legacy systems with low maintenance demands and quite often, no new development.

Considering 11.70 and older versions, the results of the 2021 survey are very close to the ones of 2020 – again, unsurprisingly. What was interesting to see was the difference in 12.10 and 14.10 installations. These represent more active systems where maintenance and further development are very important. Here, we do see that there were changes over the past year. Number of 12.10 installations has fallen by approx. 10%, while the number of 14.10 has grown by almost the same percentage. This indicates that there were some migrations from 12.10 to 14.10 during 2021. Yet, there are lot more 12.10 than 14.10 still in production.

Have a look at the chart for more graphic representation of this story.

Ognjen Orel

IIUG Board

IIUG World

A Note from the IIUG President

Hello Informix Community,

You know I’ve been talking about an in-person IIUG World event in the fall of 2022.  I was ready to announce the date and location in this month’s Insider. However, we are not there yet.

We continue to work out the details for this event in conjunction with IBM.

We hope to share the details in the next Insider or in a dedicated email as soon as possible.

Best,

Cindy

Informix Corner

Are sunsets beautiful? Usually but not always . . .

In Rom-Com movies and romance novels, nothing sets the perfect environment or makes the heart swoon more then a scene played out during a sunset. The golden glow of light as it washes across the participants, the temperature cooling while feelings and passion ignites into an inferno as they look into the future. Sunsets are glorious events which warm the heart and mind.

Let’s Talk End of Life, and Preparing for It                          The Software Version, that is

If you’ve been paying attention to the IBM Informix (Informix) roadmap presentations this year, it has been openly disclosed that a new Informix release is under development. Originally targeted for release at the end of 2022, it’s now been pushed into 2023. When exactly we’re not saying (yet.)

What does that mean for customers using Informix v.12.10? Well, to be blunt, its days are numbered. Hopefully this article will help you understand the IBM software lifecycle policy as it applies to Informix. For customers using Informix v.12.10, it should be obvious that it’s time to be actively planning on moving to what will be the supported releases.

Before continuing, please know that full explanations and details of everything mentioned here is available at https://www.ibm.com/support/pages/lifecycle/

As a general rule, IBM supports the current and immediate previous release of a product. When a new release is formally announced, second (and older) releases are given two critical “end-of” dates which are often referred to as “end-of-life” dates.  The first date is called “end-of-marketing” or more accurately “end-of-sales.” This is the date when any sales (new or to existing customers) of that release is stopped.  Generally speaking, this date is approximately 1-2 years after the V.new announcement date.

The second date is called the “end-of-support” date and is the moment in time when technical support for the older release is no longer available. This includes calls to technical support and support inquires initiated by a web submission.  This length of time is longer than the end-of-sales period and is typically 3-5 years after the V.new announcement date.

Continuing Support is available for Informix products that have reached End of Support.  This offering is a subset on “Full Support”, in that it provides support for known defects.  Details on Continuing Support for Informix can be found here:  https://www.ibm.com/support/pages/informix-continuing-support-offering

IBM also offers “extended support” contracts for up to 3 years after a release’s end-of-support date for customers who need more time to move to a supported release. This is purchased directly from your Subscription and Support representative.

So, what does this mean in practical terms for Informix v.12.10? Well let’s look at Informix version 11.70 as a guide for what happened when v.14.10 was released making it the “older release.” Informix v.14.10 was announced / released in March 2019.  At that time, Informix v.11.70 had already received an end of sales date of December 2017. An end of support date of September 2020 was announced.

Let’s make a wild assumption that V.new is announced / released in June 2023 (not real!! Not an official date!!) you can assume V.12.10 will get an end-of-sales date around December 2024 with an end-of-service date close to September 2025. Again, these are assumptions. IBM can shorten or lengthen the dates at its discretion.

With this in mind, and the fact that a new major release (with cool stuff) is coming, we strongly encourage you to begin migrating Informix v.12.10 environments as soon as practical.

2022 Informix Roadshows

What is the Informix Roadshow?

The 2022 Informix Roadshow is a one day deep-dive technical event that gives you hands-on experience with the latest IBM Informix release features and 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 and agenda include:

    09:00 – 09:45 Informix Roadmap (Scott Pickett)

    09:45 – 10:30 Informix Modernization (aka Informix in CP4D) (Scott)

    10:30 – 10:45 Break

    10:45 – 12:00 Informix 14.10 New Features (Part 1) (Scott/Jan Musil)

    12:00 – 13:00 Lunch

    13:00 – 14:30 Informix 14.10 New Features (Part 2) (Scott/Jan)

    14:30 – 14:45 Break

    14:45 – 16:15 Informix 14.10 Demos (Jan)

    16:15 – 16:30 Break

    16:30 – 17:15 Informix in CP4D Demos (Jan)

All times and speakers are somewhat variable, and we may have some local speakers at some events. Attendees will be able to take home the presentations given at the end of each event. Agenda will change somewhat as we proceed throughout the year with new releases of both Informix on Cloud Pak for Data and the latest fix pack of Informix 14 when it is released. As venues change later in the year, so will the local speakers, 

The Informix Roadshow one day events will be conducted in the following cities in Europe from June 6, 2022 to June 22, 2022 and the locations and addresses are given below:

Prague       June 6 @IBM V Parku 2294/4, 148 00 Praha 4-Chodov, Czechia IBM Česká Republika

Bratislava   June 9 @IBM Mlynské nivy 49, 821 09 Ružinov, Slovakia

Zagreb       June 13 @Alfatec Tuškanova ul. 37, 10000, Zagreb, Croatia

Sofia          June 16 @NDB 9, Vladimir Vazov Boulevard 1510 Sofia

Warsaw     June 22 @IBM Krakowiaków 32, 02-255 Warszawa, Poland

Other cities and locations are under consideration for in-person events throughout the remainder of the year. Contact Scott Pickett spickett@us.ibm.com or Carlton Doe cdoe@us.ibm.com if there is an interest in having an in person event near you.

A wise person we’ve met along the way once said that his company attends these events whenever they come to his area to get “three good ideas” …….. to take back with him to use in his company’s environment.

You can get that and much more …..

The session content isn’t marketing hype …. its about our technology and how you can use it to gain your competitive advantage in the marketplace; a real advantage that is employed quietly and effectively by many companies all over the world. These sessions are conducted by some of the best Informix professionals in the world, with years of Informix “real-world” experience. They’ll teach you how to use this technology, answer your questions and give you “hands on” experience in the technology as well. You will meet local fellow Informix professionals such as yourself, gain fresh insights on the larger business world, and in the world of Informix as a whole.

If you would like to try Informix, here are links to various software downloads and Demo Environments:

IBM Informix on Cloud on Bluemix via Salesperson or self-service PAYGO (credit card)
IBM Informix Innovator-C Edition  (free, production use allowed)
IBM Informix CSDK (free software download)
IBM Informix JDBC, free
IBM Informix Connect, free
IBM Informix Developer Edition and Time Limited Downloads, free
        (includes Enterprise and Advanced Enterprise Editions)

 See you there …. Informix is everywhere …… or is it simply faster, stronger and more flexible ……

 And thanks for being interested in or a customer of IBM Informix!!!

Website for Informix
https://www.ibm.com/analytics/us/en/technology/informix/

Scott Pickett

Improving performance of variable length columns (VARCHAR & LVARCHAR)

Informix support for variable length columns goes back a long way with VARCHAR supported at least since v6.01 in 1993 (did anyone besides me actually put v6.01 into production?). Later with Informix v9 LVARCHAR was introduced. There has always been a problem with variable length rows in Informix. Until recently, Informix would only place a new row on a page of a variable length record table if the maximum length of the row would fit. This to allow the new row to grow to its full length if needed without having to relocate the row to a forwarding page. For tables with multiple VARCHAR and LVARCHAR columns that are large that can mean that as much as half of a page (and occasionally more than half) would remain unused wasting many MBs or GBs of storage.

A forwarding page is a page in a variable length table containing rows that outgrew their original page when the variable length column(s) they contained were updated with longer length values. That leaves a forwarding pointer on the original page causing the engine to have to perform an additional IO to access the row’s new location. This can slow down processing for that table.

In v11.50 IBM introduced the ONCONFIG parameter MAX_FILL_DATA_PAGES. Setting that parameter to ‘1’ (default: ‘0’) would permit the engine to locate a new row on a variable length table page as long as there would be 10% of the page free after the insertion to allow rows to grow somewhat before having to be moved to a forwarding page, but allowing more efficient storage of variable length rows. However, this created a new problem. The engine needed to know on what page the smallest sequence of unused space that would fit a new row existed when inserting a new row. The partition bitmaps which are helpful in locating deleted space to hold fixed length rows only tell us if the pages is full, ¾ full, ½ full, or empty so they are no help with variable length rows under MAX_FILL_DATA_PAGES. In addition, compressed tables suffer from the same issue as variable length tables when they were added to Informix.

To fix this problem, IBM introduced a small cache called the Partition Free Space Cache for each variable length table containing up to 32 pages from which rows have recently been deleted. This cache is enabled globally by setting the sparsely documented ONCONFIG parameter PFSC to ‘1’ and reported on by the onstat -g pfsc report. If a new row could fit on one of these 32 pages all was well. If not, then all of the partition’s bitmaps would have to be searched and many candidate pages that the bitmap entries indicated might have sufficient free space would have to be read until one was found that could be used for the new page. This could slow down inserts on variable length and compressed tables significantly.

To resolve this issue, IBM created Boosted Partition Free Space Caches which caches all free space in a variable length partition. These caches require additional memory consumption, but vastly improve insert performance for variable length tables. The existence of the cache is enabled by setting the undocumented parameter PFSC_BOOST to ‘1’.

However, you can control which tables and table partitions are tracked in the cache by using the SQL API functions:

– table pfsc_boost enable|disable – These functions take two arguments the table name and the database name:

execute function task( ‘table pfsc_boost enable’, ‘mytable’, ‘mydatabase’ );

execute function task( ‘table pfsc_boost disable’, ‘mytable’, ‘mydatabase’ );

– fragment pfsc_boost enable|disable – These functions take a single argument, the partition’s partnum. The target partition can be a single partition table or one partition of a multiple partition table:

execute function task( ‘fragment pfsc_boost enable’, 123456 );

execute function task( ‘fragment pfsc_boost disable’, 123456 );

Here is some sample output from onstat -g pfsc:

$ onstat -g pfsc | egrep ‘npages|400004’
tblnum    flg  npages   avgfr nsearch  nmiss    pgsrch   pgskp  pgbusy mvbin
…
400004    L-   0        0     0        0        0        0      0      0    

dbaccess sysadmin –

> execute function task(‘fragment pfsc_boost enable’, 4194308 );

(expression)  Succeeded: fragment pfsc_boost enable(‘4194308’)  

1 row(s) retrieved.

$ onstat -g pfsc | egrep ‘npages|400004’
tblnum    flg  npages   avgfr nsearch  nmiss    pgsrch   pgskp  pgbusy mvbin
…
400004    B-   0        0     0        0        0        0      0      0  

Notice the change to the second field (flags). Position 1 now shows a ‘B’ for Boosted. An ‘L’ in the first position indicates that the partition only has the original ‘Light’ cache. If the second flag position is an ‘I’ that indicates that the boosted cache is in the process of being built.

Art Kagel

InformixHQ Enhancements

We are excited to announce that on April 15^th, the IBM Informix 14.10.xC8 fixpack was released and here are the InformixHQ highlights and key features that were delivered:

• InformixHQ enhancements:
• Please be aware that in 14.10.xC8, InformixHQ has had a major version upgrade and an upgrade of the H2 Database is required. If users using InformixHQ 1.6.3 or lower, try to move to InformixHQ 2.0.0 or above they will get an error and they will not be able to start InformixHQ until the data is migrated to the newer version of H2 Database. For more information, see H2 Database, H2 Database Migration. You can also see the blog in the IBM Informix Community page: Data Migration Guite:H2 Database in InformixHQ.
• The logging mechanism has been enhanced where unnecessary stack traces have been removed and now provide more informative messages in the logging file when INFO log level is set. These changes reduce the log file size significantly. It is still possible to enable stack trace in DEBUG & TRACE log level if needed.
• A new Shutdown button has been added to have an easy way to shut down the InformixHQ server using the UI instead of the command prompt.
• Additional options added to the InformixHQ Script
• User can now filter the process list by providing search keyword to the list option
• stopserver is a new option added to stop the server process interactively
• stopagent now supports using a search parameter to filter agent process list and the user can either terminate all the processes at one time or one by one interactively
• Usability Enhancements
  • Showing full chunk path with copy to clipboard option in Storage->Spaces->Chunks
  • Enhancements in Recovery Logs page.
  • Formatting Stored Procedures & Functions code in Schema Manager->Stored Procedures & Functions

Please visit What’s new in Informix version 14.10.xC8 to find additional details.

Karen Qualley

Cloud Pak for Data (CP4D) 4.5 is Coming Soon and Along With it are Many Enhancements to the Informix Cartridge!

The 4.5 release of CP4D is the next major version and direct upgrade path for those on 4.0.x versions of CP4D.

The Informix team has been working on several updates to our cartridge for this next releases and product management wanted to give you a preview of some of the new things to look out for! (as always, it comes with the caveat that future features and roadmaps items can always change!)

To step back for a minute, the Informix Cartridge for 4.0, released in September 2021, brought you enterprise readiness with High Availability setup right out of the box (Primary and Secondary) and added multiple storage options for data and backups. The ability to expand your capacity by adding up to 8 RSS nodes in different zones was also delivered along with fully encrypted communication and certificate rotation. In the 4.0.6 update we were able to remove the dependency on root processes which was a big step in becoming cloud native.

For the 4.5 release we are working to add even more security improvements by supporting Attributes based access control (ABAC) as well as OpenShift Container Platform 4.10.

We are also working to reduce your total cost of ownership by integrating more into the overall CP4D services. Some examples include supporting the Licensing Service so you can easily determine license utilization and integrating more tightly with CP4D’s Serviceability (must-gather) Service for better diagnostics.

And we are looking to increase usability by supporting Microsoft Edge and increasing install and upgrade automation using ansible scripts.

We will have more details to share next month after the GA!

Informix Training

Free Informix 4GL for Developers Trial Course

I am testing a new online training platform for 4GL Class and would like your feedback on how this works for you. This is a free Online Informix 4GL Training class available to August 2022. (You have 90 days to complete the course once you start.)  If the platform works, I may move all my training to it.  Please check it out and let me know what you think.

More information at https://advancedatatools.com/article/informix-4gl-free-training-course/

Free Informix Tutorials Videos – a step by step approach to using Informix Database Servers are available at https://advancedatatools.com/tech-info/informix-tutorials/

Lester Knutsen

Informix Tech Talks on YouTube

Next Informix Tech Talks:

Updated Informix SQL Performance Tuning Tips for Informix Developers by Jeff Filippi, Integrated Data Consulting, LLC

Date: Thursday, June 16, 2022, 2:00pm EST

Description: Learn how to improve your Informix SQL statements to optimize performance on your Informix Database system.

Bio: Jeff has worked with Informix products for 30 years and specialize in Informix.  Jeff has his own company for 20 years working with customers on Informix.

Please register – Tuning Tips

WAIUG Virtual Meeting by Washington Area Informix Users Group
Date: Thursday, July 14, 2022, 9:30 am EST

Description: The WAIUG is back and excited to bring you a new and exciting set of Informix technical talks on July 14th, 2022 from 9:30 am-1:00 pm. This free virtual meeting is open to anyone in the Informix community who wants to pick up some new skills and learn some new things around Informix.

We are finalizing the class lineup but we will have excellent talks from Paul Watson (Oninit), Mike Walker (xDB Systems), and more. Stay tuned here for a full list of presenters and classes.

Sign up below to make sure to reserve your spot. Once you RSVP we will have your information and will send out an invite to the virtual meeting in a few weeks.

Visit the WAIUG website for more information – https://www.waiug.org

Please register – WAIUG Meeting

Past Informix Tech Talks

All the past Informix Tech Talks are available as replays on our website at:
https://www.iiug.org/en/category/techtalks/

Informix Tech Talks YouTube Channel

Our channel on YouTube for Informix Users has over 71 Informix Technical Videos, with over 273 subscribers and more the 1,000 views last month! This YouTube Channel will be a place for Informix how-to videos.

Informix Tech Talk YouTube Channel Videos

IIUG World 2021 Sponsor Session Replays

Lester Knutsen

In Search of Tech Talks Speakers

We are looking for additional speakers and especially any new speakers to share your Informix knowledge with our user Community via the IIUG Tech Talks. We will assist you in preparing for your Tech Talks presentation by prerecording your session. This will allow you to be available to answer any questions that come up in the chat during you presentation.

If you are interested in producing a 15 – 45 minute presentation, please contact either Lester Knutsen or Rhonda Hackenburg for additional information.

Rhonda Hackenburg

RFE Corner

IBM has an 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. It is friendly and has a nice look and feel.

Recent

CM should support majority-vote failover arbitration as an alternative to priority-based

 JDBC driver shouldn’t require SSLCONNECTION to be set when SQLHOSTS is used

 CM should allow LOG to be set at the SLA-level

 CM should report client IP and SLA when errors occur

 Informix 4gl based Datablades

Global cursors in SPL

Popular (Over 50 votes)

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: 100 (+0)

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: 94 (+0)

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: 86 (+0)

Created: December 24, 2018

Status: Future consideration

Gary Ben-Israel

IBM Informix Forum Corner

Join and visit the IBM Informix community forum at http://www.iiug.org/community

Posts from the last  few days:

restoring Informix using the onbar command. problem

Next Informix Tech Talk: Updated Informix SQL Performance Tuning Tips for Informix Developers by Jeff Filippi

create table taby as select * from tabx PANIC informix server

can I delete sysutils:bar_instance ?

Gary Ben-Israel

Informix Resources

IIUG Website
www.iiug.org

IBM Informix community
http://www.iiug.org/community

IBM Informix RFE
http://www.iiug.org/rfe

Informix Blogs and Wikis

Starting with the next Insider this section will be removed.

Blogs are no longer a source of valuable information.

If you disagree, please email me and tell me which blogs you follow.

Technical Articles

https://www.oninitgroup.com/technical-articles/

https://www.oninitgroup.com/faq-items/cloud-backups-with-informix-14-10/

Over 10 Years of Informix Webcast Replays are available at https://advancedatatools.com/tech-info/past-webcasts/

Over 30 years of Informix Blog posts are available at https://advancedatatools.com/tech-info/all-tech-topics/

Social Media

YouTube: https://www.youtube.com/c/InformixTechTalksbytheIIUG
Linkedin: https://www.linkedin.com/groups/25049
Twitter: https://twitter.com/iiug
Facebook: https://www.facebook.com/IIUG.Informix

Forums, Groups, Videos, and Magazines

• The IIUG old forums at http://www.iiug.org/forums/technical.php *** NO LONGER ACTIVE ***

• Informix Marketing channel on YouTube http://www.youtube.com/user/informixmarketing?feature=results_main

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.

Editors: Gary Ben-Israel, Rhonda Hackenburg

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