IIUG Insider (Issue #230) September 2019

by

Highlights: Informix 14.10.xC2 available

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.

Contents:

1) Editorial

2) Highlights

  2.1 Informix 14.10.xC2 available

3) Conference Corner

  3.1 IIUG World 2019 Sessions

4) RFE Corner

5) Informix Corner

  5.1 Informix Channel on YouTube

  5.2 The edge and beyond (Video) https://youtu.be/NcHdm9TACbA

  5.3 Write a Review to Unlock a Donation!

  5.4 Compare the IBM Informix v.14.10 editions

6) Events

    6.1 IBM Informix Roadshows

7) Informix Resources

    7.1 Informix Blogs and Wikis

    7.2 Social Media

    7.3 Forums, Groups, Videos, and Magazines

1) Editorial

The greatest Informix event this year was the IIUG World 2019. The conference had many high quality technical sessions presented by the top Informix professionals. In addition to education, the conference was a unique opportunity to meet other Informix users and Informix development and support engineers.

For me, this conference was like a family reunion. Meeting many friends, hanging out and sharing memories, technical information and insights.

We missed our friends that could not make it this year. We would like to reach more users next year. The IIUG Board is looking for ideas. We are open to suggestions. Let me know if you have any.

Last month I wrote about the new Informix forum. It is very active and is the best place to find product information and answers to technical questions. It is also an opportunity for you to help others. Please join. Just go to the IBM community site http://www.iiug.org/community register and start sharing.

The highlight of this issue is the latest release of Informix 14.10. It is now October 14. I am releasing the Insider today because in Europe this date is 14.10.

Gary Ben-Israel

IIUG Insider Editor

IIUG Board of Directors

gary@iiug.org

2) Highlights

2.1 Informix 14.10.xC2 available

The following list is the most recent report of defect fixes for customer-reported defects in 14.10.xC2 releases.

APAR ABSTRACT
IT28600 LINUX INTERNAL STACK UNWINDING DURING ASSERTION FAILURES MAY CAUSE RECURSIVE EXCEPTION FAILURES
IT28752 CREATE SP INCLUDING A CREATE FUNCTION STATEMENT FAILS IN GETVARTYPE INSTEAD OF RETURNING A -201
IT28832 DATABASE USING SQL_LOGICAL_CHAR > 1 CAUSING MIGRATION FAILURE DURING UPGRADE TO VERSION 14.10
IT28982 ON AN UPDATABLE_SECONDARY, IF TABLE IS CREATED WITH VERCOLS, IFX_ROW_VERSION IS INCORRECTLY UPDATED AND FAILS WITH -244/-7350
IT29028 PROCEDURE CREATION SQL TEXT IS LOST FROM SYSPROCBODY TABLE DURING CREATE OR REPLACE PROCEDURE STATEMENT
IT29084 ENCRYPTED 12.10 INSTANCE CANNOT BE MIGRATED TO 14.10.XC1 WITH CIPHERS AES128 AND AES192. (AES256 MIGRATES PROPERLY)
IT29095 DBACCESS MAY THROW ERROR “34389 ILLEGAL CHARACTER” EVEN IF ALL CHARACTERS ARE VALID
IT29147 DBSCHEMA DOES NOT DISPLAY THE CORRECT COMMANDS FOR REPRODUCING THE PLOGSPACE
IT29531 ERROR 874 AND AF GENERATED WHEN USING EXTERNAL TABLE
IT29670 WINDOWS: ONTAPE BACKUP TO DIRECTORY WILL FAIL IF BACKUP FILE IS BIGGER THAN 2GB
IT29753 NVL IN CASE MAY THROW ERRORS 9628 OR 486
IT29904 MEMORY LEAK IN LGR MEMORY POOL ON SECONDARY SERVERS WHEN PROCESSING LARGE LOG RECORDS
IT29968 WINDOWS: CRASH WHEN DOING A TRUSTED CONNECTION
IT29969 SELECT STATEMENT WITH LEFT JOIN AND SUBQUERY IS EXTREMLY SLOW
IT29971 PERFORMANCE PROBLEM WITH LVARCHAR INDEX WITH SELECT FIRST QUERY
IT30091 PHYSICAL LOG OVERFLOW WITHIN THE CHECKPOINT WHEN A LOT OF DIRTY PARTITIONS ARE INVOLVED

Gary Ben-Israel

3) Conference Corner

3.1 IIUG World 2019 Sessions

The IIUG Board and Planning Committee would like to thank IBM, our sponsors, and speakers for your help with IIUG World 2019. Without your help, these events would not be possible. A huge thanks to all of our attendees, we hope you found technical sessions, tutorials and hands-on labs that met your needs.

We have had some great feedback about IIUG World 2019. Thanks for that.

As promised, the session slides are available for download. So, if you missed a session or were not able to attend IIUG World 2019, you can download the session slides at: http://www.iiug.org/sessions.zip. PLEASE NOTE: If a session is missing it is because the speaker did not upload their presentation. You will have to contact the speaker for missing sessions or let us know and we can try to contact the speaker for you..

If you want to know more about the sessions found in the zip file, you can find the session descriptions at: http://www.iiug.org/iiugworld/speakers/2019/pubsessgrid.php. If you want to know more about the conference or view our sponsors ads you can find that information in the conference guide: http://www.iiug.org/iiugworld/guide.pdf..

IIUG World Planning Committee

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

Lift 32K limit on SQL queries/results

SQL statements that exceed 32K in their collective data types currently crash. For example, if you select 10 LVARCHAR columns of 5000 each, totaling 50000 you get an error, crashing the application. This limit is way too low and it is causing our mission critical applications to stop, causing big problems. Even the small Cloudscape database did not have this limitation

Votes: 0

Created: September 29, 2019

Status: Needs Review

Shorten the time spent by “onstat -z” when zeroing internal structures

As was diagnosed in ticket TS002495910, in customer’s system “onstat -z” processing is causing about 1 second “block” every 5 minutes for all threads, that needed to acquire the global mutex. This causes problems in customer’s system (one of the largest polish banks), which performs servicing card transactions (Visa, Mastercards, POS, etc.), as multiple sessions are blocked. Moreover, when checkpoint request is triggered at the time of such block, then all sessions modifying data are impacted – this is due to the fact, that the global mutex wait occurs within critical section, and that causes long wait time for threads going out of critical sections, while other sessions are prohibited from entering critical sections.

The problem impacts all customer’s of the bank and also all institutions Poland wide, which perform card payment transaction validation in this bank’s system (an example of such institution is Poland wide petrol station operator). For example the 1 second “block” causes Visa or Mastercard transactions to be rejected at the time of block.

Currently there is no workaround for this behaviour – to eliminate the problem “onstat -z” was disabled for the time being.

Votes: 1

Created: September 26, 2019

Status: Needs Review

InformixHQ – Backup Sensor

I would kike InformixHQ Backup Sensor notify me whenever Backup Level 0 is greater than 24 (hours).

But the Backup Sensor did not work with “hours format”, just “timestamp”, so It is impossible to know

when the backup is older than 24 hours because you would need to do a calculation on the

monitored data before evaluating the alert.

Votes: 1

Created: September 18, 2019

Status: Needs Review

alter table online when modifying char length without killing the sessions

Today my db is accessed through many web server. the main table is frequently modified : char increased and new fields. i had to bounce the engine to remove all the sessions easily.

Votes: 1

Created: September 18, 2019

Status: Needs Review

create a foreign key allowing one to one tables relationships

Our Primary Key and Foreign key constraints always have allowed creaing and ensuring ‘1 to many’ table relationships. After researching, there is no direct way to implement such a case with Informix. The only safe way is to create a primary key on the ‘parent’ table, a unique index on the ‘child’ table plus a trigger with references that will catch insert and delete. This trigger will fire a SPL that will count the rows in the parent table matching the new key. If 1 row, the child row is created, else the SP returns an error. Same principle for DELETE

Votes: 1

Created: September 17, 2019

Status: Needs Review

Support query parameters in projection clause

See: https://www.ibm.com/mysupport/s/case/5000z00001MLNI2AAP/db2parameter-not-working-with-merge-statement

This works with DB2 according to IBM support staff.

Votes: 1

Created: September 11, 2019

Status: Needs Review

4.50 macOS Client Drivers

Targeting Developer, students, and consultants creating apps on macOS

Votes: 1

Created: September 5, 2019

Status: Future consideration

Popular

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: 85

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: 67

Created: December 24, 2018

Status: Future consideration

SQL interface to obtain the temporary space usage (tables, hash, sorts…)

A DBA and possibly a programmer needs an easy way to monitor who is responsible for the temporary space usage.

Currently it’s possible a user may be consuming a lot of temporary space and impacting other sessions, without the DBA being able to quick and easily identify the session responsible.

Other reports (from IIUG mailing list) and another RFE (36245) mentions the need for programmers to be able to find out the current session’s temp tables (onstat -g ses shows this).

The definitive solution for this should be an SQL interface showing:

– session ID

– object name

– object type (explicit temp table, implicit temp table, hash join, sort …?)

– used space

– chunk/dbspace being used

– owner nam

– ??? (PID and others, but probably thos can be gathered with joins)

This should supersede RFE ID 36245 and this includes the request for that RFE and more.

Votes: 64

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: 57

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: 57

Created: December 24, 2018

Status: Future consideration

Gary Ben-Israel

5) Informix Corner

5.1 Informix Channel on YouTube

Have you subscribed to the Informix Channel on YouTube? Here you can find demo and tutorial videos that deep dive into some of the newest Informix features. Watch one of our many InformixHQ videos, from getting started with InformixHQ to our latest video on the new customization features in InformixHQ for 14.10.xC2. Or watch our demos on ER to cloud, loopback replication, and more.

5.2 The edge and beyond (Video)

https://youtu.be/NcHdm9TACbA

5.3 Write a Review to Unlock a Donation!

Tech jobs are among the fastest growing in the country, yet girls are being left behind. While interest in computer science ebbs over time, the biggest drop off happens between the ages of 13-17. Girls Who Code was founded with a single mission: to close the gender gap in technology. Write a review, and we’ll donate $10 to Girls Who Code! https://www.g2.com/g2gives/girls-who-code/and/ibm-informix

5.4 Compare the IBM Informix v.14.10 editions

Learn the differences in features, functions, and pricing models across all IBM Informix v.14.10 editions

http://www.iiug.org/en/2019/10/08/compare-informix/

6) Events

6.1 IBM Informix Roadshows

2019 IBM Informix Roadshow – 14.1 Paris

October 17 – October 18

IBM Paris, 17 Avenue de l’Europe
Paris, France + Google Map

Find out more »

2019 IBM Informix Roadshow – 14.1 London

October 22

IBM London, 76/78 Upper Ground, Lambeth, London SE1 9PZ, UK
London, United Kingdom + Google Map

Find out more »

2019 IBM Informix Roadshow – 14.1 Rome

October 24 – October 25

IBM Office Rome, IBM Italia Spa
Rome, Italy + Google Map

Find out more »

2019 IBM Informix Roadshow – 14.1 Frankfurt

November 6 – November 7

IBM Office Frankfurt, Wilhelm-Fay-Straße 32, 65936 Frankfurt am Main, Germany
Frankfurt, Germany + Google Map

Find out more »

7) Informix Resources

IIUG website
www.iiug.org

IBM Informix home page
www.informix.com or directly at: http://www-01.ibm.com/software/data/informix/

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

7.1 Informix Blogs and Wikis

Blogs and Wikis that have been updated during the last month

More Blogs and Wikis

7.2 Social Media

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

Twitter : https://twitter.com/iiug

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

Informix IoT Channel : https://ibm.biz/BdH2nm

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

Leave a Reply

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


PageLines