IIUG Insider (Issue #240) July 2020

by

Highlights: Thank you for taking the IIUG Survey 2020

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 Thank you for taking the IIUG Survey 2020

3) IBM Informix Forum Corner

4) RFE Corner

5) How To

6) Events

  6.1 Informix v.14.10.xC4W1 Technical Deep Dive webcast – Replication

  6.2 Informix Database Server Monitoring Webinar by Lester Knutsen

  6.3 Informix v.14.10.xC4W1 Technical Deep Dive webcast – Java and system administration

7) Informix Resources

  7.1 Informix Blogs and Wikis

  7.2 Social Media

  7.3 Forums, Groups, Videos, and Magazines

Editorial

The whole world is still struggling with the Corona pandemic. Everything is slowing down.

We are doing our best to provide webinars and content on our website and in the Insider.

Personally, I have an eye surgery scheduled for August. Hopefully it will all be over in September.

Gary Ben-Israel

IIUG Insider Editor

IIUG Board of Directors

gary@iiug.org

2) Highlights

2.1 Thank you for taking the IIUG Survey 2020

We had a great response from the IIUG community. We are still processing the data and once data is compiled we will report the results in a future Insider.

In the meantime, let me share the information regarding the IIUG Insider.

Nearly 90 percent are receiving the Insider. Thank you, guys. Hope you enjoy it.

Nearly 70 percent found out about the survey from the Insider. As an editor it feels good to see that a newsletter can still be ahead of Facebook, Twitter and LinkedIn.

There were many requests and suggestions regarding Insider content. I will review these carefully and do my best to improve.

Gary Ben-Israel

3) IBM Informix Forum Corner

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

Recent posts

Upgrade with Success from Informix 11.70 to Informix 14.10

Major Issues with Windows CSDK4.50.FC4W1

Fixed Defect List

Pipes & light appends

Upgrade from 12.10.FC12 to 12.10.FC14

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

Allow BACKUP_FILTER to be set by onmode command

Votes: 1

Created July, 28 2020

Status: Needs review

Allow storage expansion and extension during an archive

Votes: 7

Created June, 13 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: 102

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

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

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

Created: December 24, 2018

Status: Future consideration

Gary Ben-Israel

5) How To

Views referenced in other views

It is common that a view selects from another view.

The problem is that when we drop a view all the views that are selecting from it are dropped as well. This is recursive meaning that If view_1 selects from view_2 and view_2 selects from view_3 dropping view_3 will drop view_2 and view_1 as well.

When we drop a view, we want to find all the views that will be dropped with it so we can make sure we have the SQL to recreate them.

We use a stored procedure view_in_view that scans sysviews recursively.

create function view_in_view(v_nm varchar(128))
returning lvarchar(32000)
define v_str lvarchar(32000);
define v_nm1, v_nm2 varchar(128);
define p_match1, p_match2 varchar(255);
define tid integer;
define tf boolean;
define j int;
if (select count(*)
      from systables
     where tabtype = "V"
       and tabname = v_nm) = 0 then
  let v_str = v_nm||" - Not found";
  return v_str;
end if
drop table if exists vv__temp;
create temp table vv__temp(ord smallint, view_name varchar(128), flg smallint);
insert into vv__temp(ord, view_name, flg) values(1, v_nm, 0);
let j = 1;
let tf = 't';
while tf
let tf = 'f';
foreach
  select view_name
    into v_nm1
    from vv__temp
   where flg = 0
  let p_match1 = "*."||v_nm1||" *";
  let p_match2 = "*."||v_nm1||",*";
  foreach
    select tabid, tabname
      into tid, v_nm2
      from systables
     where tabtype = "V"
       and tabname not in (select view_name
                           from vv__temp)
    if (select count(*)
          from sysviews
         where tabid = tid
           and viewtext matches p_match1 or
              viewtext matches p_match2) > 0 then
      let j = j + 1;
      insert into vv__temp(ord, view_name, flg) values(j, v_nm2, 0);
      let tf = 't';
    end if
  end foreach
  update vv__temp
     set flg = 1
   where view_name = v_nm1;
end foreach
end while
foreach
  select ord, view_name
    into j, v_nm1
    from vv__temp
   order by 1
  if j = 1 then
    let v_str = v_nm1||":";
  elif j = 2 then
    let v_str = v_str||" "||v_nm1;
  else
    let v_str = v_str||", "||v_nm1;
  end if
end foreach
drop table if exists vv__temp;
return v_str;
end function 

6) Events

6.1 Informix v.14.10.xC4W1 Technical Deep Dive webcast – Replication

August 12 @ 10:00 am – 11:00 am

The IIUG and IBM are pleased to announce a series of webcasts covering the features released in Informix v.14.10.xC4W1, including some that didn’t make it into the documentation! This is the second in the series: August 12, 2020 at 10 am Central (August 12, 2020 15:00 GMT) Topic: Replication The events will be held through a WebEx training session. The events will be recorded and available for download later along with the presentation materials. Information on these downloads is included…

Find out more

6.2 Informix Database Server Monitoring Webinar by Lester Knutsen

August 20 @ 2:00 pm – 3:00 pm

How do you use the command-line utilities to manage and monitor an Informix Server? We will discuss onstat, oncheck, and the new InformixHQ to manage your server. Register here

Find out more

6.3 Informix v.14.10.xC4W1 Technical Deep Dive webcast – Java and system administration

August 26 @ 10:00 am – 11:00 am

The IIUG and IBM are pleased to announce a series of webcasts covering the features released in Informix v.14.10.xC4W1, including some that didn’t make it into the documentation! This is the third in the series: August 26, 2020 at 10 am Central (August 26, 2020 15:00 GMT) Topics: Java and system administration The events will be held through a WebEx training session. Registration will open soon for this webcast The events will be recorded and available for download later along…

Find out more

7) Informix Resources

IIUG website
www.iiug.org

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

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