IIUG Insider (Issue #270) January 2023

Highlights: IBM Community Update – Changes to the Data & AI Communities

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

2023 has begun. So far, I have heard very little from IBM or HCL.

Maybe they are extending the holiday season. You guys are not sending me content either.

So, most of my work is searching. This month Art came to my rescue.

I’m doing the best I can to providing you content. If you have anything you think is of value to other users, please send it to me.

Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors
gary@iiug.org

Highlights

IBM Community Update – Changes to the Data & AI Communities

Hello Everyone,

On behalf of the IBM Community team, we’d like to wish you Happy New Year! The community continues to evolve and grow, and we wanted to let you know about some upcoming changes that we are making to the community of IBM products within the Data and AI division.

Under the guidance of our leader, Marius Ciortea, we are introducing the new Data and AI community and there will be 5 topic areas that are within it. And within the topic areas are the existing product/solution topic communities. As a part of the new structure some topic groups will be merged with others, and some will be archived.

These changes will help to keep the communities invigorated by bringing similar topics together and enabling improved discussions for broader benefit. The refreshed Data and AI Community will be structured as follows:

These changes will go into effect on or around Jan. 30th, and we will send out another notification immediately prior to the changes going live. Note that your current bookmarks and links will be rerouted automatically, so you will still be able to find all the information in the system.

Your IBM Community team (Sonia Singh and Nickolus Plowden) welcomes your comments and feedback as we continue to evolve the Data and AI Community.

Of course, you can still go direct to the IBM Informix community via special link at www.iiug.org/community any time.

Informix Corner

Informix Tech Talks on YouTube

Past Informix Tech Talks Replays

Replay of the Last Informix TechTalks: WAIUG Virtual Meeting December 8, 2022:

Visit WAIUG’s website for more information.

All Informix Tech Talks replays are available at: https://www.iiug.org/en/category/techtalks/

And on YouTube at: https://www.youtube.com/c/InformixTechTalksbytheIIUG

 

Informix Tech Talks YouTube Channel

We now have 345 subscribers to our YouTube channel.
We have 89 videos on YouTube on our Channel.
Our Channel has received over 8,342 views so far!

Informix Tech Talk YouTube Channel Videos

Past Informix Tech Talks Replays

Bruce Simms

 

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 Bruce Simms or Rhonda Hackenburg for additional information.

Rhonda Hackenburg
IIUG Board

 

 

Create an Orable style Global Temp Table — ASK

Here is a way to create Oracle style Global Temp Tables. Those are tables that are permanent but their contents are only visible to the session that created them and only for the duration of the session that inserted the data.

The only difference between these “global temp table” and Oracle’s version is that the contents are not automatically deleted at the end of the transaction or of the session that loaded them. We can get around clearing the global temp table at the end of the session by calling the delete function from the sysdbclose() routine for specific users that access the global temp table or in the generic one.

Instructions, notes, and a sample table are included in the following script.

Art

    Steps:

  1. Create the table with an extra BIGINT column to hold the session id

    Create the table with a different name, say __gtt_<table>.

    Create an index on the session id column and a UNIQUE index on the key column(s).

  1. Create a VIEW with the desired name of the Global Temp Table that

    selects all columns EXCEPT the session id column and a filter like:

    sid = DBINFO( ‘sessionid’ )

  1. Create a stored procedure that takes all of the columns of the

    table except for the session id column and inserts to all columns

    including inserting DBINFO( ‘sessionid’ ) to the session id column

  1. Create an INSTEAD OF INSERT trigger on the VIEW that calls the insert stored procedure in the

     FOR EACH ROW () clause.

  1. Create a stored procedure to delete all rows in the base table

     that takes the primary key column(s) of the temp table as its arguments. The procedure will include:

     WHERE sid = DBINFO( ‘sessionid’ ) AND <key column filters>

  1. Create an INSTEAD OF DELETE trigger on the VIEW that calls the delete stored procedure in the

    FOR EACH ROW () clause passing in the key column(s).

  1. Remove data distributions from the temp table: 

Consider naming the underlying real table something unusual such as beginning the name with one or more underscores.

If you will have many sessions using the Global Temp Table, consider creating the index on session id as a Forest of Trees index hashing on the session id with a prime number of buckets.

 

To emulate how Oracle Global Temp Tables remove rows inserted by a session at the end of the session, you can call the <delete> procedure from the sysdbclose() routine for those user ids that may be using that temp table of inthe global sysdbclose() routine.

 

CREATE TABLE “informix”.__gtt (

     sid BIGINT,

     key SERIAL(6) NOT NULL,

     value CHAR(10)

) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

{

     Please review extent sizing and adjust to allow for growth.

}

REVOKE ALL ON “informix”.__gtt FROM public AS “informix”;

GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON “informix”.__gtt TO “public” AS “informix”;

 

CREATE INDEX __gtt_i1 ON __gtt( sid );

CREATE UNIQUE INDEX __gtt_pk ON __gtt( key );

 

UPDATE STATISTICS LOW FOR TABLE __gtt DROP DISTRIBUTIONS;

 

CREATE VIEW “informix”.global_temp_table (

     key, value)

AS

SELECT

     x0.key ,x0.value

    FROM

     “informix”.__gtt x0

    WHERE

     (x0.sid = DBINFO (‘sessionid’) ) ;                                                                                                                 

 

 

CREATE PROCEDURE  “informix”.insert_gtt ( akey int, avalue char(10) )

insert into __gtt values ( dbinfo( ‘sessionid’ ), akey, avalue );

END PROCEDURE;

 

REVOKE EXECUTE ON PROCEDURE “informix”.insert_gtt(integer,char) FROM “public” AS “informix”;

GRANT EXECUTE ON PROCEDURE “informix”.insert_gtt(integer,char) TO “public” AS “informix”;

 

CREATE TRIGGER “informix”.i_global_temp_table

  INSTEAD OF

    INSERT

    ON “informix”.global_temp_table

    REFERENCING NEW AS neu

    FOR EACH ROW (

  EXECUTE PROCEDURE “informix”.insert_gtt(neu.key,neu.value ));

 

GRANT SELECT, UPDATE, INSERT, DELETE ON “informix”.global_temp_table TO “informix” WITH GRANT OPTION AS “informix”;

 

CREATE PROCEDURE clear_global_temp_table( akey int );

DELETE FROM __gtt WHERE sid = DBINFO( ‘sessionid’ ) and key = akey;

END PROCEDURE;

 

CREATE TRIGGER d_global_temp_table

  INSTEAD OF

    DELETE

    ON “informix”.global_temp_table

    REFERENCING OLD as alt

    FOR EACH ROW (

  EXECUTE PROCEDURE clear_global_temp_table( alt.key ) ) ;

 

Informix Training

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

 

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

Allow INSERT to write to external tables

Encrypting and Decrypting Stored Procedures

Allow renaming a database when BTS Index is defined

Add option to remove last 20 terminated sessions from onstat outtput

 

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: 104 (+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: 98 (+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: 88 (+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

Long Running Query thru shell Script        

INSERT data to a table and file both        

INFORMIX -> INSERT data to a table and file both

1 —

Dear Friends,
IDS 14.10.FC6 

We need to INSERT data to a table and the same time we need the same data to write to a file.

We cannot do this activity from the application end since there are different types of applications doing the INSERT operation.

So, we have to run this activity from DB end only.

Thanks !
Best Regards,
Indika Jinadasa

 

2 –

Indika:
One would think that one could simply create an external table SAME AS the original table with the desired format (you have a choice between DELIMITED, FIXED field, and INFORMIX (binary data) format. Then create an INSERT trigger on the original table that copies the inserted data to the external table.

Unfortunately each INSERT to an external table truncates the file. If it is a multi-row insert those rows all get inserted to the file, but a subsequent insert, even from the same session, will truncate the file first and overwrite it. So this will not work.

You could install the UTL_FILE datablade extension (see the Database Extension manual or search online) which contains the following functions:

  • FCLOSE procedure – Closes a specified file.
  • FCLOSE_ALL procedure – Closes all open files.
  • FFLUSH procedure – Flushes unwritten data to a file.
  • FOPEN function – Opens a file.
  • GET_LINE procedure – Gets a line from a file.
  • NEW_LINE procedure – Writes an end-of-line character sequence to a file.
  • PUT procedure – Writes a string to a file.

You could, again, create an INSERT trigger on the table in which you call a stored procedure that builds a string or several strings from the inserted columns and writes the string out to a file. You could format the string(s) as a delimited, fixed field, XML, or JSON record for consumption by whatever application needs to read it.

Another alternative would be to write your own database extension using “C” or Java to create a function to open a file in append mode from the BEFORE section of the trigger, format and write a row of data from the ON EVERY ROW section, and to close the file in the AFTER section of the trigger. It would not be difficult to do.

Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Gary Ben-Israel

Informix Resources

IIUG Website

Not a member, become an IIUG member and keep updated on all things Informix. It’s free.

IBM Informix Community

Not a member, join The IBM Informix community and connect with other Informix data experts.

IBM Informix RFE

Informix Requests for Enhancements.

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

Over 30 Years of Informix Blog Posts

Social Media

YouTube LinkedIn Twitter Facebook

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