IIUG Insider (Issue #247) February 2021

Highlights: Congratulations to the 2021 IBM Champions

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

This March it will be a year since this pandemic totally changed my way of life as well as many others around the world. We are all doing our best with a bad situation.

The IIUG is no exception. Last year at this time the IIUG Board was meeting in-person to discuss the upcoming year. We were discussing locations and dates for our 2020 IIUG Informix Technical Conference. We were even scheduling several 1-day events in Europe and South America. Due to the world shutdown all scheduled events were canceled while others never got off the ground.

This year will be different. In the last Insider, we announced the first ever IIUG Virtual Technical Conference. We are still working on the logistics for this event. We are working with IBM for speakers and content. We could use your help. These events are always better when we can get our users involved. We opened the call for speakers at the beginning of February and are looking for user speakers for the event. To learn more, review the IIUG Informix Virtual Technical Conference section below.

We have also started two new ventures this year.

  1. We have started a new technical series we are calling Informix Tech Talks. Our goal is to hold one every month or two. We are also looking for anyone who would be interested in doing a short presentation for the upcoming months.
  2. We have created an IIUG Informix YouTube channel to share Informix content with our community. If you have prerecorded content you would like to share, just let us know we would be glad to help you get it posted and share with the community.

You can learn more about these new ventures below.

We look forward to keeping you informed on all things Informix, but we sure miss your smiling faces. Hope to see you some time in the future.

Be safe and take care.

Rhonda Hackenburg
IIUG Insider Editor
IIUG Board of Directors
rhonda@iiug.org

Highlights

Congratulations to the 2021 IBM Champions

The IIUG Board of Directors would like to congratulate the following Informix users for being 2021 IBM Champions. A special recognition to our 2 life-time members: Cindy Lichtenauer and Lester Knutsen.

These Champions are recognized by IBM for being leaders and influencers. They are mentors, speakers, bloggers, basically enthusiasts and advocates of IBM Informix and the Informix community.

Maybe you know someone who would qualify for IBM Champion status for 2022 or maybe you feel you have what it takes to qualify. You may want to consider submitting the nomination for 2022. Visit the IBM Champions program page to learn more about the program and when nominations will open for 2020 or to view the entire list of 2021 Champions in all categories.

IIUG Survey 2020

IIUG Survey Results – About You

In this last look back to our recent IIUG survey, we’ll show some facts we found out about you. Well, actually, all of us, Informix users and fanatics.

You are most likely in Europe or in North America. Most of our respondents located themselves in Europe or Middle East (38.6%) and North America (32.1%), followed by our Asian friends (14.3%). However, we had respondents from all over the world: Central and South America (8.4%), Australia and Oceania (4.1%) and Africa (2.5%).

You’re a long time Informix professional, most likely with more than 20 years of experience with Informix: 43.6% of our respondents said they have been working with Informix 20-30 years, and big 16.2% said they have more than 30 years of Informix experience! One other large group are people working 10-20 years with Informix (26.8%), while there were only 13.4% people who work with Informix for less than 10 years.

Apart from reading this Insider, most of you found out about the survey on LinkedIn or via WhatsApp, followed by IBM Community forums.

Thank you for your participation in the survey, and for following us here. IIUG will continue with surveys, in shorter form, but the ones that will enable all of us to observe changes in the Informix community.

Ognjen Orel
IIUG Board

IIUG Corner

IIUG Informix Virtual Tech Conference

In last month’s Insider we announced we were going to offer our FIRST virtual Informix technical event. This pandemic is still affecting travel and face-to-face meetings, so we have to consider new alternatives to talk about Informix. Everything is going virtual and we have decided to change our technical conference model.

The 2021 IIUG Virtual Technical Conference will be held late May or early June we are still working on the logistics. The event will be multi-day with different times to make it convenient for members to attend all or part of the conference. The format of the conference will be a combination of 1-hour pre-recorded and live presentations. Prerecorded sessions will be presented with chat and discussion opportunities.

We opened the Call for Speakers at the beginning of February. Visit the IIUG website to submit your Speaker Abstract.

Our conferences are driven by the user community and we always look forward to what our user speakers bring to the content of the IIUG conference. Do you have experience with Informix or Informix related products? Do you have real case studies related to Informix solutions? Have you discovered any Informix hints or tips? Are you interested in sharing your Informix knowledge and skills with the Informix User community? If the answer is yes to any of these questions the IIUG Conference Committee invites you to submit a proposal for this 2021 virtual event. This new format is ideal for those of you who have thought about sharing your knowledge but are uncomfortable speaking in front of others.

Continue to visit our website for update and find other IIUG presentations. As always, if you have any questions you can contact us at iiug@iiug.org or you can contact me at rhonda@iiug.org.

Rhonda Hackenburg
IIUG Board and Conference Committee

IIUG Tech Talks Webcast – March 25

Please join us for our second Informix Tech Talk on March 25 at 2:00 PM EST.

Informix SQL Performance Tuning Tips

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

Date: March 25, 2021

Time: 2:00 – 3:00 EST

Speaker: Jeff Filippi, Integrated Data Consulting, LLC

REGISTER HERE!

If you are interested in becoming a Tech Talks presenter, please contact us at iiug@iiug.org.

IIUG YouTube Informix Channel

Have you visited our new YouTube Informix Channel yet? IF not, please take a look and let us know what you think. This is still a work in progress, Informix Tech Talks by the IIUG – YouTube.

We are still looking for content to feature for this new IIUG venue. Maybe you already have a video you have recorded or maybe this is something you have thought of doing but were not sure how to reach your target audience.­­­­­ If you have content you want to share or know someone how might, please contact us at iiug@iiug.org we would be happy to share your knowledge.

Don’t forget to subscribe to our Channel.

Rhonda Hackenburg
IIUG Board and Conference Committee

IIUG Tech Talk REPLAY

Title: Three ways to capture data changes when they happen!

Speaker: Art Kagel

Description: Informix supports three technologies that we can use to capture changes to our data as they happen: – The Change Data Capture API – Push Data Triggers (aka Smart Triggers the Java API for Push Data Triggers) – Asynchronous Post Commit Triggers The talk is a survey of the three methods to capture data changes.

Click here to replay or download the slides.

Rhonda Hackenburg
IIUG Board and Conference Committee

Informix Corner

Informix and Sequential Scans – Scott Pickett

This is an article authored by Scott Pickett. Scott is the IBM Informix World Wide Technical Sales person and a thirty-five year exclusive user and consumer of Informix products.

This article started out as the result of an IBM Informix User Community post I read last year that I answered on the IBM Informix Community Site. A user essentially wanted to know how to detect and resolve sequential scans. I decided I should elaborate more fully on this matter, as this is a problem which in my experience, I have seen at many customer installations. This problem requires accessing several different parts of our Informix ecosystem for a database administrator, all of which you have at your disposal if you are a user of Informix. As an aside, the approach chosen herein is not the only one that is possible. There will be things not presented here for reasons of time and space; as such, I consider this article to be not comprehensive.

Read more of what Scott has to say on this topic.

HCL Lab Services

HCL Lab Services offers consulting, performance tuning, upgrades, migration assistance, and premium advisory services for the IBM Informix product. These services are offered around the world directly to customers or through IBM as well as other partners.

Many of our customers and partners have expressed difficulty in finding deep IBM Informix skills, especially in certain geographies. The goal of HCL Lab Services is to bridge this gap and help our clients to continue to grow their business leveraging their existing and new investments with IBM Informix. We also want our customers to upgrade to the latest IBM Informix version 14.10, wherever possible, so that they are protected for foreseeable future instead of running on out of support versions 11 and below. Lab services can also offer help if you are considering modernizing your Informix environment utilizing IBM Cloud Pack for Data (CP4D). We hope to build a strong collaboration with IBM and other partners to share our expertise and augment customer’s IBM Informix skills. There is a great opportunity to help our clients to optimize their IBM Informix environment and thus even further reducing the legendary low TCO (Total Cost of Ownership) with IBM Informix.

HCL Lab Services have several service offerings that can be customized to meet individual client needs. However, recently, we created part numbers for the following two offerings that customers can order directly out of the HCL Software product catalog.

  • IBM Informix Health Check and Performance Tuning
  • IBM Informix Premium Tech Advisory Service

The first offering is a 40-hour block of services for performance tuning that can be bought from the HCL Software product catalog and deployed any time of customers choosing within one year. The second offering is a premium advisory service of 10 hours/month for a one-year term. Both these part numbers are available for customers to purchase directly or IBM and partners can resell.

Whether you are an existing customer, partner, or new to IBM Informix, you can leverage HCL Lab Services to supplement your IBM Informix skill inventory, optimize your environment or accelerate your solution delivery and deployment. HCL Lab Services offers complementary capabilities to our loyal partner base and looks forward to working with both customers and partners in revitalizing IBM Informix.

Karen Qualley
HCL

IBM Webinar REPLAY

Title: Looking Ahead with IBM Informix in 2021 Webinar

Speaker: Karen Qualley and Karthik Gopalakrishnan

Description: Join IBM Informix Offering Management to hear the 2021 plans for IBM Informix. The team will also share how it listens to clients and partners by reviewing requests for enhancements submissions.

Click here to replay.

Rhonda Hackenburg
IIUG Board and Conference Committee

RFE Corner

IBM 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 to place new requests. It is friendly and has a nice look and feel.

Recent

Informix documentation for oncheck command should be crystal clear

Informix documentation for oncheck command does not explain clearly that this command should be run only during a maintenance window or during an off-hours period because the oncheck utility places a shared lock on system catalog tables when the database is checked and that the shared lock prevents any other updates, inserts, or deletes on the database until the check has completed. Various aspects of this information are found on a variety of sources of IBM documentation, but the information is not expressed clearly on the page that is intended to describe this command. I had to ask Informix Support multiple questions before I could get the answer I needed. Previously I had the oncheck command running on our production machine on Sundays, but even then it would sometimes cause problems for other production applications that were running on that day.

Votes: 1

Created February 16, 2021

Status: Under review

Informix ESQL/C ifx_var_getdata() should do charset conversion when binding with CLVCHARPTRTYPEFJLK

Environment: Linux Debian 10.7 or Windows 10 Informix server: IDS-14.10.FC5 Informix client: CSDK 4.50.FC5

With the DB in UTF-8 (DB_LOCALE = en_us.utf8) and client app in another character encoding (CLIENT_LOCALE=en_us.8859-15):

When fetching an LVARCHAR column, and binding with ifx_sqlvar_t sqltype=CLVCHARPTRTYPE, after the fetch we want to get the character string with ifx_var_getdata().

But it appears that the sequence of bytes returned by the ifx_var_getdata() function is pure UTF-8, and is not converted to ISO-8859-15 …

Documentation says that this function returns ASCII data … we assume that no conversion is done at all and it returns just the UTF-8 byte sequence.

The string should be converted to the charset of CLIENT_LOCALE, just as when binding with CVCHARTYPE and a self-allocated buffer. But that solution is not acceptable for LVARCHAR because we would need to allocate large buffers to cover the max LVARCHAR size!

https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.esqlc.doc%2Fids_esqlc_0007.htm

“..The lvarchar pointer type is not equivalent to a C-language char pointer. Informix® ESQL/C maintains its own internal representation for the lvarchar pointer type. This representation is identical to the representation of a var binary host variable, except that it supports ASCII data as opposed to binary data…”

Votes: 1

Created February 12, 2021

Status: Under Review

Support IDataReader::GetBytes / IfxDataReader::GetBytes for BSON fields

We’re using the BSON capabilities of the Informix server in order to store data in a separate BSON colum – like additional information for the data row or optional fields that may vary from row to row (e.g. translations to different languages).

With the current Client SDK (v4.50xC5), it is not possible to access the binary representation of that field by issuing a SELECT statement using the Informix.Net.Core driver as the driver currently does not support the GetBytes function for any other field types than BLOBs or BYTEs.

That requires us to either issue multiple bson_value_xxx statements to access all those optional fields -or- to cast the field from BSON to JSON on the server side to then perform the creation of the client-side data object by translating the returned values for a second time.

A more direct approach by returning the byte represenation of the BSON column would have two nice side effects:

a) There’s no need for double conversion (either by calling bson_value_xx and then issuing the corresponing GetValue calls from the IDataReader to build up the data object at client side -or- by casting BSON to JSON and then parsing JSON at client side), thus the load on the server is reduced

b) The limit from 32767 characters (currently imposed for a VARCHAR – what the result of the BSON to JSON conversion is) would be omitted

Votes: 2

Created February 5, 2021

Status: Under Review

onbar should be able to restore logs from primary AND HSR RO SECONDARY

… to prevent full backup after HDR primary/secondary switch !

At the moment (Version 12.10.xC14) cold restore after HDR primary/secondary switch is only working if:
– both nodes have the same DBSERVERNAME/INFORMIXSERVER set because the namespace which onbar uses
to store the objects depends on that parameter/environment.
– a complete new ixbar file is build from primary and secondary with correct order
– physical and logical restore must be started from that node that holds the last full backup
– logical restore stops than with object not found (onbar returncode 100) – logical restore suspended
(don’t know if RESTARTABLE_RESTORE depends on that – but we have set it)
– logical restore than need to be restarted with data from other node to get last logical logs
– it could be complicated with that workflow if you need a point in time restore

Votes: 1

Created February 3, 2021

Status: Under 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: 108 (+2)

Created: December 24, 2018

Status: Planned for future release

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: 89 (+2)

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: 79 (+4)

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

Created: December 24, 2018

Status: Future consideration

Rhonda Hackenburg

IBM Informix Forum Corner

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

Recent posts

Looking forward with IBM Informix 2021 replay is now available

Slow Running Query

Error converting data type DBTYPE_DBDATE to date.

[Informix][Informix ODBC Driver]Unspecified System Error = -21005

issue with the ODBC Driver

Spot the difference

Slow Running Query

Simple Large Object Replication

monitoring session memory

HDR

in-place alter – get fields

backup tables

Authenfication

monitoring session memory

Things That Should Never Happen

Undocumented logical log flags

Slow running backup

Streamline your organization’s data security

Index page size recommendations

DATABASE vs CONNECT TO: Invalid characters error?

ESQL/C: Fetching LVARCHAR with ifx_var_getdata() when DB and client locale do not match

OS upgrade and Informix Server

Validate backup : archecker –tdsv and CommVault

nb of extents ?? dbschema vs oncheck

InformixHQ on Windows questions

Unknown error message 966.

Rhonda Hackenburg
IIUG Board

Informix Resources

IIUG website
www.iiug.org

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

Informix Blogs and Wikis

Technical articles

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

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

Social Media

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

Twitter : https://twitter.com/iiug

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

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.

Editors: Gary Ben-Israel, Rhonda Hackenburg

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

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

Leave a comment

Your email address will not be published. Required fields are marked *