Highlights: Another IDS Fixpack Plus™
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.
Table of Contents
Editorial
Things for Informix are looking much better. New Fix Pack, More HCL and IBM involvement. Better relations between the IIUG, HCL and IBM.
In this issue there is even an article about Informix growth by an Informix user, thank you Ben Thompson. It seems that things are getting better and we have a reason to be optimistic.
Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors
Highlights
Another IDS Fixpack Plus™
You’ve probably noticed that Informix developers really struggle to release a fixpack that contains only bug fixes and no features. Honestly it just about kills us. In late 2020 we were mere days from freezing 14.10.xC5 and I had to pry a good friend’s fingers off his keyboard.
“Dammit Carl, no enhancements! We all signed the pact!”
“Just—ek—one tweak!” he gasped. “It’s borderline cosmetic!”
I struck him on the face but he kept typing.
“Security!”
Things spun out of control from there. I think we had to put him down eventually. My point is that each of us has a long list of pet projects and we’re always looking for an excuse to sneak a couple of goodies into any truck leaving the factory.
So what’s the Plus™ in the 14.10.xC8 fixpack? Let’s look back at the previous release for a minute. Art Kagel had pointed out to me that internally the server had a lot of read-ahead info it wasn’t sharing. These discussions led to several enhancements. As of 14.10.xC7 onstat -g rah (read ahead) has six sections that can be displayed together or individually:
- General Read-Ahead Info
- Active RA Request Queues
- Active RA Daemons
- Recovery RA Stats
- Partition RA Stats
- Thread RA Stats
The partition stats are more detailed, revealing the numbers behind the effectiveness calculations for each RA type. (Note that read-aheads are considered more effective if none of the requested pages is in the cache to begin with.) And these same stats are broken down by thread as well. We also added four pseudo tables to sysmaster, which map directly to sections 2, 3, 5, and 6 of that onstat -g rah output:
sys_ra_act_queues
sys_ra_act_daemons
sys_ra_ptn_stats
sys_ra_thrd_stats
After following up with Art over the winter we decided there was more we could do here. When tuning read-aheads he likes to compare the total number of pages read by the daemon vs. the number of those pages that were actually used by the requesting scans. What was missing for him was a breakdown of those numbers by partition and thread. So onstat -g rah now contains that info, and there is a new ‘rapgs_used’ column in both sys_ra_ptn_stats and sys_ra_thrd_stats.
Our ability to rebuild sysmaster in any fixpack was another nice capability added in 14.10.xC7 and we took advantage of it again in xC8. In addition to the schema changes I mentioned above we added a session id (‘sid’) column to sysactptnhdr and used that column to improve the performance of the syssessiontempspaceusage view, adding an ‘allocated_kb’ column there for good measure. It’s convenient that we can make these kinds of changes without triggering the conversion process and breaking rolling upgrades, which brings me to Connection Manager.
When migrating to 14.10.xC8 from xC6 or xC7, in order to perform a rolling upgrade it is important that you first upgrade CSDK to version 4.50.xC8, and ensure that your connection managers are all running this latest version before beginning the server upgrades. The reason is related to the automatic sysmaster rebuild. The latest version of CM will play well with the temporary interruption that occurs during that rebuild, and pre-4.50.xC8 versions will not.
The good news is that no downtime is required to upgrade your connection managers. Simply upgrade them one at a time to version 4.50.xC8, and your rolling server upgrade should then work correctly. As always we recommend sufficient testing of any process you plan to use in production. The other good news is that once your connection managers are running version 4.50.xC8 you will not need to take this extra step in future rolling upgrades.
Another request that Art made was to bring back RA_THRESHOLD in some form. Recall that read-aheads are performed in batches, the size of which is configurable. If you set RA_PAGES to 128, the read-ahead daemon will prime the cache 128 pages at a time. Alternatively if you set AUTO_READAHEAD to 1,500 your default batch size will be 500. As a scan processes these cached pages it’s important for the daemon to remain ahead of the scan, reading the next batch before the previous batch is fully consumed. But fetching the next batch too early can waste I/O, because the scan may complete or be otherwise interrupted without processing any of the final batch.
With the advent of AUTO_READAHEAD, RA_THESHOLD was deprecated, and the threshold was hard-coded to 50%. As of 14.10.xC8 you have regained the ability to set that threshold using an optional third argument to AUTO_READAHEAD:
AUTO_READAHEAD <mode>[,<batch_size>[,<threshold>]]
Note that setting the threshold above 50 delays the reading of the next batch, whereas setting it below 50 results in earlier reads of the next batch. When you use large batch sizes you may want to set the threshold to say, 75. Small batch sizes might benefit from a setting of 30 or 40. I would say though that for most applications the default of 50 will work fine.
There’s one more 14.10.xC7 feature we’ve touched in xC8: archives on RSS nodes. In xC7 that archive would not go forward if we determined that your instance contained any unlogged objects such as raw tables, no-log databases, or unlogged smartblobs. The concern was that by allowing an archive on the RSS of objects that are not replicated we would be providing a false sense of security. Using that archive to restore a primary would not properly recover these objects. We still have this concern and we urge you to understand the ramifications of bypassing this check, but in xC8 we will allow you to do that by setting a new configuration parameter: ARCHIVE_UNLOGGED_OBJECTS.
The default value is 0 (off), which means an archive on an RSS will not proceed once we’ve discovered an unlogged object there.
A setting of 1 (quiet mode) will allow the archive to proceed but will notify you that at least one unlogged object has been found.
A setting of 2 (verbose mode) will allow the archive to proceed and will list every unlogged object discovered.
An archive from an RSS node that contains unlogged objects will still be a perfectly legitimate means of creating or restoring a secondary node. It should only be used for restoring a primary node if all unlogged objects can be subsequently recreated by some other method.
We also made one platform-specific change I should mention. Those of you on AIX/POWER, you should see improved scaling across NUMA nodes (SRADs) in xC8. We have more work to do in this area but found a discreet, safe change that made enough of a difference by itself that we decided to release it now rather than wait for a major release.
That’s all I have for now. We hope you find the 14.10.xC8 fixpack useful. There is talk of making xC9 a bugfix-only release. If that comes to pass I hope you’ll recognize the sacrifice we developers have made for the cause of stabilization. Fortunately for our sanity we have version 15 in the works.
Have fun
Karen Qualley
Informix Corner
Some Words from the IIUG President
Hello IIUG Members and Informix Enthusiasts WorldWide,
20 minutes of your time? Post a product review on Gartner Peer Insights
As a member of the IIUG Community, you are a vocal participant in IBM’s evolution. We appreciate your contribution and most of all your opinion. Please take 20 minutes and post an anonymous review today on Gartner Peer Insights. Use this custom Gartner-generated URL, which takes you to the Cloud Database Management Systems Market. If your review publishes, you will be eligible to receive a $25-USD-valued VISA gift card (applicable worldwide), or you may choose to donate your gift to a charity, via the Gartner Peer Insights’ platform. Remember your opinion and experience matters, and both are valued on this community and outside of IBM!
Kathryn DuPont
Client Advocacy Manager
Data and AI
IBM
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)
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)
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
Informix Growth
Our business has continued to grow, especially so during the recent lockdowns many countries experienced. This puts extra pressure on our already-busy Informix OLTP systems and we wish to express our appreciation for the support we receive from IBM/HCL to help us keep everything running as it should as demand increases. I wish to share a few examples, useful if you need help building an argument to upgrade to 14.10 at your workplace.
With version 12.10 we found Informix Enterprise Replication (ER) performance wasn’t as good as it could be and provided evidence that other methods were much faster and had less impact on other system activity. Along with other customers we participated in a beta program which resulted in HCL providing some highly effective improvements. For example, using loopback replication and ‘cdr sync’ is now the fastest and easiest way to make a copy of a database table, faster (on our hardware) than high performance loader or using external tables, and with the not-insignificant benefit that changes during the synchronization are replicated. The overall impact of a large ‘cdr sync’ is reduced as Informix manages ER memory and queue sizes much more effectively than before. This is in 14.10.xC6 onwards. If you’ve avoided ER in the past or not looked at it, perhaps now is the time to re-evaluate it.
On busy systems you may have seen a large number of waiters on “ddh chain XXX” mutexes. It is possible to tune the data dictionary cache and put busy tables in their own buckets but, even then, mutex waits can choke a system. In 14.10.xC7 this problem is mostly eliminated thanks to a careful reworking of the cache, reducing the amount of time individual threads hold the mutex. This version also fixes a long-standing issue with the relatively obscure ‘spa cache’, which caches some system tables to do with authorization at session level. In many situations this cache was not being used.
I have seen in IIUG forums some users reporting 14.10.xC5 is slower than 12.10 versions. While you should do your own testing, we were able to validate a fix for this issue in 14.10.xC6. I am confident the latest 14.10 release is at least 6% faster than 12.10 in almost all situations, benefiting from newer compilers, plus you get the well-received HDR and RSS replication optimizations we’ve heard so much about.
I hope this illustrates some things happening with Informix, even if a lot of it is buried in the technical detail and aimed at making sure customers get the performance and boring reliability they need from their systems. We should see 14.10.xC8 soon and there is at least one more performance improvement I know of coming in that.
Ben Thompson
Informix Tech Talks on YouTube
Next Informix Tech Talks:
Using Connection Manager by Tom Beebe
Date: Thursday, May19, 2022, 2:00pm EST
Description: Connection Manager is one of the most useful and most underused additional tools with Informix. It helps manage connection loads, handles routing, can give extra security and functionality to legacy applications and will help IT staff better manage system failovers. It is also free. In this talk we will go through setting up Connection Manager in a number of different settings and with an eye on real use cases and improving your security both for modern and legacy systems.
Please register – Using Connection Manager
Informix SQL Performance Tuning Tips by Jeff Filippi
Date: Thursday, June 16, 2022, 2:00 pm EST
Description: Learn how to improve your Informix SQL statements to optimize performance on your Informix Database system.
Please register – Informix SQL Performance Tuning Tips
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 Talk YouTube Channel
Our channel on YouTube for Informix Users has over 39 Informix Technical Videos, with over 244 subscribers and more the 2,805 views! 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
There is no presentation for our April Tech Talks. Instead, the IIUG World 2021 Conference Session Replays were published for your review.
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
Informix HQ – API calls for Groups/Servers administration
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
Recent posts
Migrate database from Solaris 10 to Solaris 11
Informix Roadshow Events – Central and Eastern Europe June 6-June 22, 2022
New Informix Webinar from IBM!
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
Please note: The list below contains only blogs that were active in the last six months.
There was nothing new in the past six months.
Technical Articles
https://www.oninitgroup.com/technical-articles/
https://www.oninitgroup.com/faq-items/cloud-backups-with-informix-14-10/
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