The world of database systems technology has just been taken to a new level by Informix!

 That’s right, that RDBMS that was supposed to be dead 20 years ago according to pundits, but has actually been alive and well all these years.

IBM and it's IP partner HCL have just announced the release of Informix v15.0 which blows the lid off claims that RDBMS cannot handle the massive data that needs to be processed in today’s world. Informix 15 has removed nearly all of its limits. New features are industry shattering.

Here’s an overview:

Informix v14 limited a data page to 255 rows. V15 raises that to over 32000 rows.

Informix v14 limited a table partition to 2^24 pages. V15 raises that to 2^48 pages. Most tables no longer require multiple partitions

Informix v14 supported several pages sized up to 16K. V15 adds 32K & 64K pages quadrupling the amount of data per page and improving the performance of indexes for massive tables.

V15 increases both the maximum chunk (aka storage file) size and the maximum number of chunks supported.

The maximum data under management under v14 was ~2PB (already more than any other RDBMS). Under v15 that increases to half a yottabyte. That’s more than four times the entire contents of the Internet and, in practical terms, this is unlimited storage! No other RDBMS comes close to this capacity.

V15 supports invisible indexes. Invisible indexes are fully maintained and updated but are not considered by the optimizer for processing queries. This can be a static state for an index or a dynamic session specific condition. This can improve performance for querying tables that are highly dynamic such as staging tables and can be used dynamically to test whether an index is actually necessary to efficient performance.

V15 adds the ability to acquire the query plan for a live active query (and some historical queries that have completed) without setting up for it before execution. This can help to diagnose slow production performance.

V15 enhances Informix’s already impressive support for non-English languages and non-Latin alphabets.

V15 includes built-in support for connecting to IBM’s WatsonX Generative AI platform.

V15 supports External Large Objects simplifying the management and use of static Big Data objects such as text documents, sound and video recordings, Web scrapings, etc.

Here’s a link to register for an IBM Webcast on December 3rd describing the new release:

Pushing the boundaries of data scalability: An overview of Informix 15 Tue, Dec 3, 2024 9:00 AM EST

https://ibm.webcasts.com/starthere.jsp?ei=1692726&tp_key=fffe83b236

Informix v15.0.0 is coming soon!

 Watch this space for the earliest description of the new features in Informix v15.0.0 as soon as I am permitted to talk about it.

For now, I can expose that the version numbering scheme is changing. V15 will be 15.0.0 the first sub will expose feature updates and the second sub level will expose bug fixes and patch releases. There has been no word yet on how IBM will note special patches and platform specific fixes.

 My friends,

I wrote a letter for the Action Network letter campaign:

Netflix: Removfe the Antisemitic Blood Libel Film "Farha"

That reads as follows:

The Jordanian film Farha, by director Darin J. Sallam, in which IDF soldiers are seen murdering a Palestinian family during the War of Independence, will be released on Netflix in December. The film takes place in 1948 in Mandatory Palestine and is about a 14-year-old Palestinian girl whose father locks her in a warehouse because of IDF attacks in the village. The production claims to be "inspired by true events", however this is a lie. The design of the character of Farha as a girl whose whole future is ahead of her, and who finds strength hiding from the cruel enemy, echoes the story of Anne Frank. If Farha's equivalent is Anne Frank, then according to the film, IDF soldiers are equivalent to Nazis. And if there is someone for whom this parallel is not clear enough, Salam inserts in the middle of the film the scene in question in which Israeli soldiers capture a family of fleeing Palestinian refugees, abusing them in her friends and in the end they are executed. A father, a mother who has just given birth, and two daughters - all are murdered in cold blood by the soldiers on the orders of their sadistic commander, a few moments after he threatens to open the mother's stomach to check if there is a fetus inside and what its gender is. The baby himself is left to die, after one of the soldiers receives an order to end his life, but "without wasting a bullet". The helpless Farha listens to the cries of the baby who is left lying on the ground in the hot sun. Heartbreaking crying, which lasted about a minute. Then silence. Confirmation of death. So what is so different about "Farha"? The film presents the IDF soldiers as bloodthirsty monsters who amuse themselves with the death of helpless innocents. In Salem's film the Israelis kill Just. For no reason.  They're doing it for fun. Such events never occurred. The film is a total lie. It is a dangerous blood libel and incitement against the IDF that will lead to more antisemitism. Netflix MUST NOT distribute this dangerous inciteful blood libel. Send a letter to Netflix management demanding to remove the film.

It's easy and takes just 30 seconds. Whether you support Israel or not, no one should support outright lies and innuendo masquerading as a documentary!

Can you join me and write a letter? Click here: https://actionnetwork.org/letters/netflix-removfe-the-antisemitic-blood-libel-film-farha?source=email&

Thanks!

Informix delivers!

This will be a quicky.

There are two things that I have been asking for in Informix for a while. One is what I consider a bug fix, bout could be considered a feature. The other is a failure to complete a feature that was never released officially and finally release and document it. Both are now in the lastest release of Informix, v14.10.FC6!

A real ROWID for partitioned tables

When table partitioning (aka fragmenting) was introduced partitioned tables did not supply access to the pseudo-column ROWID that has always been a part of Informix (and most other RDBMS) tables. It was the physical address of a row in the table and could be used to quickly access a given row. However, the rows of a partitioned table existed in multiple partitions and the Nth row in each partition would all have the same ROWID it was impractical to support the feature. So you had no built-in unique identifier you could use for tables created without a unique key. Shortly after that the option to create a partitioned table WITH ROWIDS was introduced which added a real but hidden integer column to the table and creates an index on it. However, if you created the table without the WITH ROWIDS clause and the table was very large it was impractical to add that feature to an existing table.

It seems that for a long time there has actually been a physical address of a row in a partitioned table and I have written about it before. This was the undocumented pseudo-column ifx_row_id which is a 45 byte CHAR type value containing the partition number and rowid of the row as a colon separated string. Unfortunately if you used the ifx_row_id to query a row the engine would perform a table scan rather than using the value as a row address and directly accessing the data. A SET EXPLAIN will show this behavior. Well, version 14.10.FC6 has fixed that. If you run such a query now, it is lightening fast even on a massive table and the SET EXPLAIN output will show an index scan query plan for ifx_row_id. YEEHA!

Querying LVARCHAR type columns in an array fetch

This was finally fixed in the CSDK v 4.50.xC5! Since the introduction of LVARCHAR type columns if you used an array fetch to fetch a batch of many rows into your program's memory space in a single FETCH operation you would get a spurious error (-1831 Combination of FetArrSize, Deferred-PREPARE, and OPTOFC is not supported) which have nothing to do with the actual problem which was using FetArrSize > 1 with a query that returned an LVARCHAR. This issue prevented one from using my dbcopy utility to copy any table that has one or more LVARCHAR columns or writing one from writing their own mass data processing applications. This was a large problem because array fetching is several times faster than row-by-row fetching for very large data sets.

Stepping into a different realm for a few minutes

This morning I was introduced to a 5 minute video recorded by Rabbi Lord Jonathan Sacks, former Chief Rabbi of the United Kingdom talking about the New Antisemitism. At the end Rabbi Sacks makes a warning that I think is relevant in this time of the incidents of overt racism in the United States and elsewhere. I therefore offer this link, not only because as a proud person of Jewish heritage it speaks of things that have affected my family and myself, but because I think that it speaks to all of us:

https://youtu.be/3UAcYn4uUbs

After that I sought other words of wisdom from Rabbi Sacks and found this TED Talk from 2017 where he asserts that today's focus on the self is a major problem in society and that a simple shift to a focus on "us" and "we" and the "other" may be the cure:

https://www.youtube.com/watch?v=F-QFwhWmC3U

If you have something constructive to offer in comment, whether you agree with Rabbi Sacks or not, I welcome the dialogue. If, on the other hand, you post comments of hatred to any person or group at all I will delete your comment. So just don't bother!

IIUG Forums have moved to the IBM Community Site

IIUG Forums

For those who are not aware, the IIUG SIGS or Forums, including the old comp.databases.informix Usenet group, are no longer available. They were using some very old home built software running on the old IIUG server which was quite literally falling apart.

IBM has graciously created a new Community site for IIUG forums and blogs which is accessible through the IBM web pages. Back in the depths of time, shortly after the IBM acquisition, I was opposed to efforts to bring the forums in to IBM's systems. Honestly, we had no reason 18 years ago to trust IBM would continue to support the best database system on the market, ie Informix. Today, the story is different. IBM has been a true partner to the Informix community, so I have joined the rest of the IIUG Board of Directors in making this change. The new forum is linked to the IIUG Site under the Resources menu item. The link is: www.iiug.org/community

You will need an IBM ID if you do not already have one, but that is free for the asking. When you log in to the site for the first time, please go to the Informix group page and make a “Hello world” post so that the group moderator can validate that you are not a spammer or bot. That will take an hour or so after which you will be able to post instantly! You can also create your own Blog there for the community if you want.

It is very important that EVERYONE who cares about Informix database systems join the IBM Informix Community forum and ONLY post there. IBM sees that site and counts both the number of members and the number of posts there and uses that information to determine how much attention they should pay to our favorite database system!

I learned something new!

Have you ever needed the functionality of ROWID for use with a partitioned table that you did not create using the WITH ROWID clause? Well, I recently found out that such a facility is available!

It is not compatible with the traditional ROWID, so you cannot just use it in applications that were written for ROWID use on single partition tables, but it works and it does not require any indexing or rebuilding your massive multi-partition table! The undocumented pseudo-column named ifx_row_id. This is a VARCHAR(45) type and it returns a string of the form "ppppppp:rrr" where the p's are the fragment's partnum and the r's are the row's actual rowid within the partition. Also it works exactly the same for single partition (ie non-fragmented) tables as it does for multi-partition tables.

You can select the ifx_row_id for any row in any table:

select ifx_row_id from systables where tabid = 1;

ifx_row_id                                    

5244160:769                                 


You can select data by ifx_rowid:

select * from systables where ifx_row_id = '5244160:769';

tabname          systables
owner            informix
partnum          5244160
tabid            1
rowsize          500
ncols            26
nindexes         2
nrows            307.0000000000
created          01/30/2019
version          65638
tabtype          T
locklevel        R
npused           22.00000000000
fextsize         16
nextsize         16
flags            0
site             
dbname           
type_xid         0
am_id            0
pagesize         2048
ustlowts         2019-11-03 08:44:57.35225
secpolicyid      0
protgranularity  
statchange       
statlevel         


If you do need a numeric "rowid" you could convert it to a BIGINT:

CREATE FUNCTION "art".ifx_row_id_to_rowid ( ifx lvarchar ) returning bigint;
define partnm, rw, bigrowid bigint;
define pos int;

let pos=instr( ifx, ':' );

let partnm=left( ifx, pos - 1 )::bigint;
let rw=substr( ifx, pos + 1, 10 )::bigint;
let bigrowid=(partnm * pow(2, 32 )::bigint ) + rw;

return bigrowid;
end function;  

New RFE I have posted

Please vote for this RFE if you agree with it.

RFE URL:

https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-325

RFE Description:

When creating a stored procedure or function with the IF NOT EXISTS optional clause included, the option text is currently included in the saved source code for the procedure.

This should be changed such that the optional clause is removed before saving the procedure source code in sysprocbody.

Justification:

This can confuse some software, both IBM and third party, written to analyse and support Informix and is not necessary to be saved as its addition does not change the understanding of the procedure source nor how it was created. The very fact that the source with that text was saved indicates that the procedure did not exist at the time it was last created which would be true and the only valid inference even without that text.

Similarly, when the behavior of CREATE OR REPLACE PROCEDURE/FUNCTION is fixed, that syntax should also not be saved as it also does not add to the understanding of the routine's creation and will further break third party tools.

IIUG World 2019 is coming!

The 2019 IIUG World conference registration site is open!

Register now for the Early Bird discount and save! (Don't forget to get your IIUG Member ID number so you can also get the members' discount!)

https://www.iiug.org/conf/2019/iiug/register.php

Also it is not too late to submit user session proposals to the conference board!

Informix to the Rescue!

It was 1990. I was brought into a major New York mutual fund and private wealth management firm (call it NB) along with another consultant to review their systems and help them plan for the future. At the time their systems were all written in FORTRAN running on Prime Systems' hardware using a non-SQL relational database system that only ran on Prime. Prime Systems had just announced that they would no longer be manufacturing hardware and would only continue supporting existing computer systems for a few years. Because of this NB needed to find new hardware, a new database system, and port all of their applications to this new environment. We were to help with the product evaluation and recommendation and estimate the porting effort at the end of which one or both of us might be hired full time.

Along the way we evaluated Sun, HP, and DEC hardware (NB was adamant that they were not interested in IBM systems). We mostly looked at various UNIX variants (System V, BSD, and OSF-1), although DEC kept saying things like "Yes, yes we have Alpha based systems running OSF and System V. You really should be looking at VAX/VMS systems!" But NB was convinced that proprietary OS's were the past, they'd just been bitten by Prime/Primos, and wanted to go with a UNIX variant. We had meetings with Oracle, Sybase, Informix, Unify, Progress, and Ingres including some demos and discussions with developers and support engineers about features and internals.

Anyway, we carefully gathered specifications, counted lines of code and numbers of modules, evaluated plans for new projects in the pipeline that had been halted after Prime's announcement, met with hardware and database vendors and after maybe six months or so we made our recommendations. Here they are with a concise brief of our reasons:

• Hardware - Sun server blades
  HP and DEC each had multiple OS's and hardware platforms. On the OS side, UNIX wasn't the main product for either company. We were concerned about their commitment to UNIX in the future and Sun was 100% UNIX and was gaining ground in the industry.
• OS - UNIX System V - Solaris
  See hardware...
• Database - Informix OnLine v4.01
  NB didn't like the locking model on some of the databases. Oracle didn't have any development tools beyond a very simplistic form manager and an ESQL implementation that was incomplete against the developing standard. Also Oracle's SQL dialect did not conform well to the SQL-89 standard. Informix OnLine had row level locking, stored procedures, triggers, referential integrity, and was the first RDBMS that could be archived successfully while fully online (hence the name) processing transactions. Informix had started as a development tool company that happened to have a great database, so there was a very complete ESQL/C implementation as well as a 4th Generation Language (4GL) and high level menu, forms, and reporting tools for building quick applications when responsiveness to business requirements was needed. Sybase was similar in design to Informix, but, at the time, it did not have an ESQL/C implementation at all, only its Open Client (OC) library. While several years later OC became the ODBC v1 interface library standard, at the time it was purely proprietary to Sybase.
• Development environment - ESQL/C (for its portability across SQL databases)

They said "Thank you." for our recommendation report, hired both of us full time, and then went ahead and bought:

• Hardware - DEC Alpha - because DEC's stock was performing better in the market than Sun's (NB was, after all, a financial organization).
• OS - OSF-1 - because both DEC and IBM were driving the development - "How can it not be big!"
• Database - Sybase - because all of Wall Street was using Sybase, so NB should also! Oh, and Sybase promised to deliver an working ESQL/C in 90 days.

Anyway, long story long, we spend four months getting things up and running on the new systems and ported the first application, NB's client billings and invoice generation. First problem we ran into was how to represent portfolio values. Some financial instruments were priced in Japanese Yen, Italian Lire, and Hungarian Lira. Any client that owned many shares of any of these securities would have either a native currency value with more whole unit digits or a US currency balance with more fractional digits than a Sybase DECIMAL type could hold (15 total digits fixed as 12 to the left of the decimal and three to the right). The head of development and I attended a Sybase Financial Users Group meeting and asked them "What are you doing about this? Do any of you have, or know about, an Open Server module that can handle larger numbers?" (Open Server was a library of functions one could use to add functionality to Sybase - a unique feature at the time.) The answer was basically that "there are no such Open Server modules" because no one was willing to use the Sybase Open Server libraries to basically build a customized server (SOS modules were linked into the base server). "Then how are you handling the larger numbers for big customers with Yen and Lira securities?" we asked. The answer was "We don't have any such, and we're hoping we never do." NB did. (For what it's worth, Informix's DECIMAL type supported up to 32 decimal digits of precision, either floating point or configurable fixed point.) Strike one!

Then we started to really code the application and found that the ESQL/C that Sybase delivered was a) not ready for prime time (apps built with it kept crashing) and b) was brain dead, and missing several features of the language that we needed like named cursors and multiple cursors in a single application. Sybase wanted 12 months to get those features implemented for us. Strike two.

Strike three was performance. Our initial tests were that our brand spanking new DEC Alpha, cutting edge systems, running Sybase, would take up to 4 days to process the data and generate the invoices that the several years old, out-of-date technology of the Prime systems could get done in around one day. Strike 3. 

We called Informix, loaded up Informix Online v5.02 and ESQL/C, exported and imported the database (again), recompiled the code improving it using the features we couldn't get from the Sybase ESQL/C, and wham! We were done with Billing and invoicing in another month or so and getting the invoice runs done on a Sunday afternoon (OK after they finally went out and bought the faster and higher volume printers we had recommended because we were now generating reports faster than the operators could change the paper in the printers they originally purchased - forgot that part). Later other interactive systems were written in Informix 4GL and even a couple using Informix's Forms, Menus, and Reports modules. Go Informix!

Let me know if you want to hear more stories like this.