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 (the actual limit is 65535 rows, but IBM stopped testing at 32000 so YMMV).

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, 128L, & 256K pages increasing by 16X 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 from 4TB to 8PB per chunk and the maximum number of chunks supported.

The maximum data under management under v14 was ~8PB (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 improve performance for querying tables that are highly dynamic such as staging tables and can be used 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 to the IIUG Tech Talks recording of a session describing the new features:

https://www.youtube.com/watch?v=1EcfixvQ_oM&t=1354s

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.

New series

Followers:

I am about to begin a series of posts that will hopefully be a monthly thing. The basis of the series is stories about how Informix performs against other options in the real world. Some will be older stories (I will probably start there). Others will be more recent. I encourage anyone who wants to send me stories they can relate or a pointer to someone who can relate the story to me privately and I will post these with appropriate sourcing or anonymity as requested.

I am traveling today, so I will start later this week with a story from my own experience from ancient history relating an incident where Informix and Sybase went head-to-head.

Some of the series will be short anecdotes, others more full blown out tales. I hope you enjoy.

IIUG World 2018

https://lnkd.in/d6siP7Y I will be presenting two sessions at the 2018 IIUG World Conference in Arlington, Virginia outside Washington DC next week. The first session is "Hello! Your data is calling!" where I will be presenting about the new "Smart Triggers" feature of Informix which allows users to create applications that passively receive notification when data in a registered table is modified. The second session is "Uninterruptable transactions with Informix!" in which I will demonstrate the Informix Transaction Survival feature that will allow active transactions to survive the crash of the primary server in a high availability or remote secondary cluster environment.

Looks like Oracle is worried about Informix again!

OMG, it looks like Oracle is beginning to worry about Informix! Why would I say that? Isn't Oracle "The world's most popular database!"? Why should they worry?

I dunno, but in February Oracle announced v18c (actually v12.2 but read on). This latest release of Oracle implements tons of features that have been in Informix for up to 28 years! The need to compete with Informix on features again seems to have surfaced!

Not to tout Oracle, but to point out how forward thinking the Informix development team is, witness:

• Annual major releases with quarterly updates. To celebrate they have renamed v12.2 to be v18c after the year of its release. One assumes that the Q1 release next year will be v19c.
• Ability to attach a table to another table as a partition - was that Informix 7.31?
• All identifiers have been increased from 30 bytes to 128 bytes - Informix v7.30 circa 1998
• Multitenancy
• JSON support including dot notation in queries
• Functions for converting table data to JSON
• JSON operators in SQL queries
• New API to allow JSON aware languages to query Oracle JSON documents
• Ability to update fields within JSON documents
• RAC based Database Sharding - well sort of. A shared RAC database can have its data segregated so that nodes only operate on a subset of the data. But it is still a monolithic store.
• NonRAC based Database Sharding - well sort of. Applications must be shard aware. Inserts, updates, and deletes are directed to the appropriate shard in the API layer, not within the database server shard cluster.
• "Connection Manager" enhanced to manage load balancing between multiple servers. Originally this was closer to Informix Connection Multiplexer feature.
• Application Continuity - This is similar to Informix's Transaction Survival allowing transactions to complete when a server fails.
• Session private temporary tables - Informix v4.01 the initial release of Informix Online circa 1990?
• New Oracle autonomic features to allow for unmanaged cloud databases.

What are reasons to migrate away from Informix?

OK, I haven't lost my mind. I have been active on a social media site called Quora where people ask questions on just about any topic. Some are technical, some are social, some are just annoying. Recently someone asked the question above. Eric Vercelletto posted one answer and I posted another. Here is an expanded version of my response for your amusement.

The single most common reason to migrate away from Informix is FUD. Other RDBMS vendors have been spreading the FUD that Informix is dead legacy technology since the IBM acquisition in 2001 and that has resulted in many shops moving away from an RDBMS that had successfully supported their organizations for many years. Unfortunately IBM was one of the worst offenders. IBM sales people seeing dollar signs in their vision, if they sold new DB2 licenses instead of an Informix license renewal at 20% of the price, told customers the same thing "Don't stick with Informix it is going away!". That’s the main “reason”.

What are reasons to NOT migrate away from Informix? Well, the truth is that Informix is not a dead legacy technology. In the 16 years since IBM bought Informix the database from Informix Corp. IBM has made more improvements and innovations to the product than Informix itself had in the prior 18 years since the company started. Today Informix is still the best OLTP engine on the market. The Informix Warehouse Accelerator feature handles complex data warehouse queries against massive data marts several hundred times faster than any RDBMS, even faster than its sister product DB2 Blue Accelerator. Informix is the only RDBMS that can not only handle semi-structured JSON/BSON data as native data but can seamlessly integrate MongoDB style collections with relational tables and serve as a plug-in replacement for MongoDB extending that API with access to relational tables (as JSON results), generic SQL statements, and working reliable ACID transactions that include relational and JSON data today (Mongo promises to have its brand new ACID transaction feature ready next year). Informix timeseries support is as good as any dedicated timeseries database with the added benefit that Informix’s Virtual Table Interface allows one to present timeseries data to analytical tools that do not understand timeseries as if it were a flat relational table. Informix was the first RDBMS to fully support scale out sharding to create huge distributed databases spread across dozens or hundreds of physical servers build over heterogeneous technologies from different vendors.

On to other topics

Since taking over development and marketing of Informix and a number of other IBM software products last year, HCL has been more concerned with getting its ducks in a row and so little new or exciting has come out of HCL's Informix development group. Version 12.10.xC9, the first full release developed completely under HCL's auspices, did introduce smart triggers which is a good thing.

That said, version 12.10.xC10 actually included several new features and improvements:

• added compression of blobspace BLOB data, 
• backup to cloud storage services, 
• the ability to swap primary and mirror chunks which can allow us to upgrade a server's storage to faster devices with no downtime, 
• new sysmaster data and onstat options to help identify unused and little used indexes, 
• the ability to retain data distributions and other statistics when truncating a table (useful for staging tables that are truncated and reloaded periodically), 
• the ability to reconnect to a smart trigger session without missing data when an application exits and restarts, 
• expanded audit details about sessions and users, 
• added compression of string fields in timeseries subtypes, 
• expanded geodetic data support for additional coordinate systems (x,y coordinates; GPS; industry specific coordinates).

There are two updates to the IBM online Knowledge Center pages about Smart Triggers:

Generic information about how to register for Smart Trigger Push Data:

 https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.erep.doc/ids_erp_pd_01.htm

JDBC API for Smart Triggers:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_st_01.htm

Other than this the online Knowledge Center pages have also lagged. Also there has not been a new set of Informix PDF documentation since 12.10.xC8. I do put the onus for the documentation SNAFU firmly on the shoulders of IBM, though, not HCL because after transferring responsibility for the software to HCL IBM disbanded the Informix documentation team without informing HCL that they might want to take that effort over as well. I think that this issue is in the process of being resolved.

Big Announcement

Over this past weekend, HCL announced that Informix instances are now available on AWS, the Amazon Cloud. There are dozens of different configurations that will cost from pennies an hour to six dollar figures per annum to meet the needs of organizations from startups to large enterprises. If you are considering moving your databases to the clouds, there are now alternatives to the IBM cloud for Informix users. HCL promises other major cloud providers will become available soon.

New features in Informix 12.10.xC9

I am stoked! HCL has released the first update to Informix Dynamic Server developed under their auspices. There are two significant new features in the .xC9 release, one is an enhancement to how time series and spatial data work together that adds significant performance and utility to that feature and the other is a completely new feature that users have been asking for for years. I will cover them in order:

Track Location and Time Together

STS_SubtrackCreate() function which creates the "subtrack" table over which the spatiotemporal  index is created. That done the STS_SubtrackBuild() function populates the subtract table and builds the initial index contents. Spatiotemporal indexes are relatively static, however you can configure the Timeseries such that it automatically updates the index when data elements are added to the Timeseries record.

The biggest change in the .xC9 release is improved time granularity of the spatiotemporal data making it easier to search and locate allowing new searches that answer "When was an object in a specified area?" "What objects were in this area at this time?" "At what time were there some objects in this area?"

Applications to Receive Asynchronous Notice of Data Changes 

Client applications can now create Smart Triggers that register them to receive notification when there are changes in a data set. The API uses SELECT statements and WHERE clauses to identify which specific data each application is interested in, and an application can register to receive push data from multiple source tables. Once registered the server will push new and modified data from the server to those clients interested in those specific rows. 


Because the client applications do not have to poll the server looking for new data, greater scaling and responsiveness is achieved by those apps. At the same time the database server's parallel architecture can feed the data to all clients by asynchronously reading logical log file changes. This design lets client applications scale linearly without adding significant overhead to the database server. Since the changes are scraped from the logical logs asynchronous to the session threads actually modifying the data, by using the Enterprise Replication log scraping threads, there is no performance effect on the OLTP applications that are making the changes to the database.

Previously you might have emulated this behavior using insert, update, and delete triggers on the tables that called to a C or JAVA library function, but the process of trapping the trigger and sending data synchronously would slow down the front-end transactions causing potentially serious scaling and concurrency problems for applications.Registering is fairly straight forward and is documented in the Enterprise Replication Guide. Basically you call a registration function passing in a BSON record containing fields defining the table you are registering for, a SELECT statement with an appropriate WHERE clause to filter the rows you are interested in, a label you want to use to distinguish data blocks from one table from those originating from another, a timeout setting, the number of elements you want to receive in each message, how many messages to allow to queue up, and the earliest transaction time you want to receive data updates from.I am particularly stoked about this one because I think it will be key to the success of a new project I am working on for a client. Perhaps when it is all finished I will be able to get permission to talk about it.

Going Forward

There has been a long gap since my last post. I apologize. I have not been ignoring the community nor have I given up on Informix as some have suggested and gone off to do other things. On the contrary, the main reason for my silence is that the past year has been my busiest in a long time. If I ever entertained fears that Informix is dying this year would have put those to bed for me for sure. I have seen one client upgrade their servers to the latest releases three times. Two clients implement IWA successfully. One going from Proof of Concept to production in four months and in the process saving over a million dollars over the alternative free "open source" solution while exceeding the predicted performance the "other" solution promised! The other implemented a new vertical product for the industry that it serves that will allow their customers to perform more detailed analytics in less time with fewer resources.


I have spent several months helping another client expand their use of Informix throughout their organization through which effort they were able to improve the timeliness and reliability of the services they provide to their customers and so to their customers customers among whom are counted many reading this post (including me). So, you are welcome. 

One of my current projects, mentioned in passing above, is very different for me and has me excited because it is allowing me to do some database design. That's one of the more fun things I do. Recently performance tuning, installations, training, and feature implementation have taken up my time and I haven't had the opportunity to work on a design project in a while. Enjoying the change. 

IIUG Update

I just returned from the IIUG Board of Directors fall meeting. This year we met for the first time formally with HCL executives and development management. Some of you may have been dismayed by the Editorial in the recent IIUG Insider. I have to report that the feedback from HCL in response to Gary's concerns was overall heartening. It seems that mostly HCL didn't think it was important to keep the IIUG informed. There are new things in the queue that the Board members will be able to discuss publicly as soon as some outside hindrances to roll out are overcome. There is exciting news pending for market segments that previously could not take advantage of Informix. That's about all I can say for now, but hopefully the news will be released in time for the next Insider. Although Murphy is probably working hard to cause it to miss Gary's deadline by a day B^(


Stay tuned there is lots happening in the Informix world. And don't forget to start working on getting permission to attend the IIUG World 2018 conference in Washington DC in October 2018!

A new era for Informix begins now!

I returned from last week's International Informix Users Group Conference with some news. What kind of news it is I am not 100% certain. I am certain that it is BIG news. I am certain that it affects most of us in one way or another. I am not certain whether it is good news or bad news for the Informix user community or same-old same-old. I have been saying since I heard it that I am guardedly optimistic that this is good news.

For those of you who have not heard, IBM and a company called HCL have partnered to "jointly develop and market the Informix family of products". IBM has licensed the intellectual property rights for all Informix products to HCL for a period of at least 15 years with options for renewal. As part of the deal IBM will retain ownership but HCL will be responsible for developing the products and for tech support. Indeed HCL will be hiring all Informix developers and support personnel who are willing to make the change. (My understanding is that so far all US Informix developers and support people have agree to sign up and tentative offers have been made to the Informix folks in Europe and Asia pending government paperwork in those areas.) HCL will also be free to market and sell Informix as well as to develop "derivative products". Decisions about the product life cycle and roadmap will be made by IBM and HCL together.

What does this mean for existing customers? Immediately not much. You will still be able to renew your support and purchase additional Informix licenses from IBM. You will still call the same support phone lines or use the same online support portal. The technicians responding will simply be HCL employees and likely the same people you have been dealing with all along.

This is a link to a LinkedIn post by Mattias Funke Director Core Database & Data Warehouse Offering Management & Strategy at IBM explaining the deal:  https://goo.gl/CY4nyO

HCL is a global consulting and IT software group headquartered in India with locations throughout the world. Their Product and Platform business unit which was started in September is based in New York and will manage the Informix products in addition to their own product lines which concentrate on IOT, embedded systems, and cloud computing. These are all strengths of Informix and explains HCLs interest in the partnership. 

In discussions with their executives at the IIUG Conference we were told that HCL is very interested in focusing their own products on working with Informix and in making Informix THE player in the database market. 

IBM's internal politics have always prevented IBM from fully embracing Informix for the market leading database that it is and this has encouraged much of the FUD intimating that Informix is a dead or dying product. HCL has no such constraints on it. Someone said at the Conference "the gloves are off!" We may even see advertising about the benefits and features of Informix. I am hopeful. The Informix community will be waiting to see how HCL follows up on these opportunities and promises and how this changes the marketplace for Informix.

Some Very Cool Things Are Happening in the Informix World

I read two interesting things this week. One was an award that Informix won and the other is a White Paper Bloor put out. Both reference Informix as a platform for IoT or Internet of Things data storage and processing.

First, on July 14, 2016 at the annual Cisco Live conference in Las Vegas Nevada, Cisco presented IBM with an award naming IBM Informix as the "Best IoT Database" on the market today. I know this is no surprise to you, but this is the biggest third party acknowledgement of Informix's role in a major company's product and market strategy. Most of the businesses that depend on Informix for the competitive edge it gives them over their competitors who use other RDBMS and non-SQL databases are not willing to talk about it. This is a coup for Informix and a great boon for the Informix user community. Here's a link to the annoucement on the IIUG Site:

http://www.iiug.org/2016/07/14/well-deserved-trophy-for-informix/

The other item is the Bloor Report which describes IBM Informix as the perfect database for IoT installations "regardless of where (in the IoT pipeline) it needs to be deployed". Among other things, Bloor's Philip Howard wrote:

[It] is essential that any embedded database is invisible and remains that way. This is true regardless of whether you are simply collecting data and passing it on or whether you are performing some analytics on the data. In the latter case, in order to get good performance, you need, at least in
conventional environments, to create indexes, materialised views and other such database constructs in order to achieve that performance. While this is feasible it is not flexible in the event that additional requirements need to be supported. Every time you add functionality within the device or gateway you will need to change the supported indexes. Worse, different workloads may mean that different indexes, materialised tables and so forth will be differently suitable for different customers. Moreover, these workloads may change over time. What this will mean is that the database will need to be tuned on an ongoing basis in order to maintain performance, which is impractical in IoT environments. For all of these reasons a traditional relational database will not be suitable for embedding at the device or gateway level, precisely because these all require exactly this sort of tuning. Fortunately, this is not the case with IBM Informix because the product includes self-healing and self-tuning autonomics that handle these embedded environment requirements automatically. Secondly, there are some elements of database flexibility that need to be discussed with specific respect to IoT environments. Support for things like geo-spatial and time series data we will discuss later. In the context of flexibility, you must bear in mind that an IoT implementation may consist of multiple types of devices and gateways doing different things. Moreover, the sort of data you are collecting and processing may change over time. For both of these reasons a database that supports a flexible schema will be preferable and as a result of these considerations IBM Informix supports JSON (where each data object has its own schema) as first class objects within the database.
...
Depending on where (an IoT database) is implemented there will be rather different requirements. However, in our opinion IBM Informix is well-suited to IoT regardless of where it needs to be deployed. At the device and gateway level the product has a long-standing reputation as a “fire and forget” database that can be easily installed and maintained while, in the centre, it has the sort of capabilities and performance characteristics that suit hybrid operational/analytic environments. On top of this, native time series and geo-spatial support are requirements for many IoT use cases, so IBM Informix is well-placed in this market.

Very cool indeed! Here's a link to the full Bloor Report:

http://www.ibm.com/common/ssi/cgi-bin/ssialias?htmlfid=IML14542USEN

LVARCHAR not long enough?

I've got some news for you!  If you had been to the IIUG 2015 Conference last week (April 26-30 2015) you might already know this.  If not, relax, 'cause I was there.

Question: Have you ever had a requirement for a character type that can hold strings longer than the 32K limit on the LVARCHAR type?  Don't want to have to deal with the hassles of storing and retrieving a BLOB or SLOB?

Welcome to Informix version 12.10!  I'm not sure in which sub-release this was actually implemented, but it definitely exists in .xC4 and later.  There is a new extended type, longlvarchar which is limited to 2GB strings!  Strings under 4K are stored in-table like ordinary LVARCHAR values, but longer strings are moved, invisibly, to a SmartBLOB Space.  So, you can now declare:

create table big_stuff (
       really_long_string longlvarchar,
       .....
);

Very cool!  This is a short one, I know, but it's been a busy week getting back into the normal swing of days after returning from San Diego, Mother's Day is ahead, and a short trip to visit a client next week, so I'll have to fill you all in on other things later.  TTFN.

News from the IIUG

Here's a link to an announcement from the IIUG Board of Directors talking about this year's IIUG 2015 Conference celebrating the 20th Anniversary Year of the IIUG:

IIUG News March 20, 2015

Twenty years!  Wow.  Who knew when the founding members of the IIUG, Lester Knutsen, Carlton Doe, Walt Hultgren, Malcolm Weallans, and Cathy Kipp sat down to plan a new Users' Group for customers of a small database company who's products were just beginning to hit their stride that their idea would grow into the singular most influential users' group at IT giant IBM!

Are you planning to attend the IIUG 2015 Conference in San Diego on April 26-30?  If so, have you registered and booked your room yet?

If not, why not?  IIUG 2013 was at the same location in San Diego, California and we all loved it.  The staff took very good care of us, the food was excellent (how could it not be with Stuart Litel planning the menu - talk about the ultimate foodie!), the sessions were great as were the tutorials on the last day, hands-on labs with IBM Informix legends to guide us and let us play with features we don't get to use in our daily grind, and we got to speak with IBM executives and developers and learn about the future of Informix!  IIUG 2014 Conference in Miami Florida was just as good.

Best of all, we get to do it all again in five weeks at IIUG 2015!

If you don't come this year, not only will you miss the best source of Informix information and networking in the world, but, besides all that, you will miss my secret announcement!  I will be announcing a new product at IIUG 2015 that will give your organization a leg up on its competition! If you are not there, you may not learn about it until months later and will have missed the one and only opportunity to Beta test!  Want a hint?  OK, just one in the form of a question: Could your organization benefit from being able to get its analytics completed faster and at a FAR lower cost than anything you may have or contemplate purchasing? DUH!

See you in San Diego!

On Hybrid Database Development

At the recent IBM Insight conference in Las Vegas I presented a session entitled: 

"My Data is Relational But My Coders Want to Use JSON!  Help!"

Over 30 development managers and developers attended.  When asked most said they attended because this was a common problem that they are all either dealing with already or expect to have to deal with soon.  

Here, as well as I can translate a presentation to text, is what I had to say:

I assert that JSON and other semi-structured data formats present unique challenges for organizations. In order to take advantage of these new structures and the development paradigms that they support and encourage and integrate them into our existing systems, we wrestle with three alternatives:

1. Keep these new data and the apps that utilize them independent of our legacy data and keep our legacy apps independent of this new data
2. Perform ETL/ELT frequently to maintain all of this data in both relational and semi-structured databases.
3. Develop applications that can access multiple data sources concurrently.

I maintain that none of these alternatives is acceptable and I want to propose a better path to future application and database development. First a digression to define terms which you can feel free to skip.

There are many classes of data:

• Structure data
• Unstructured data
• Semi-structured data
• Time stamped data composed from any data class
• Geographically located data composed from any data class
• Spatially located data composed from any data class

Structured Data refers to the data we have in relational databases for the most part. Despite the predictions of pundits relational databases are here to stay.  There are several well defined application segments for which relational databases are the best tool for the job.

Semi-Structured Data is data that may have a natural structure to it but where the structure may vary from one data element to another.  This can also include elements where part of the information is structured while other sub-elements are unstructured.

Currently the most popular format for semi-structured data is known as JSON for JavaScript Object Notation.  JSON is a key:value record or document format that is quickly becoming a new standard for data interchange replacing CSV, XML, and other interchange formats.  JSON is the basis for the RESTFUL web service protocol.  It is more space efficient than XML and easier to parse.  In addition, it is supported by a binary format, BSON, that is even faster to parse and extract individual sub-elements.  JSON is a dynamic schema format where each record, or document in JSON terminology, contains the schema details for the document which can be different from one document to another.  This promotes and supports RAD or Rapid Application Development.

MongoDB has become the most popular of the JSON databases or stores.  It saves JSON documents in BSON format and produces JSON output to consuming clients.  There are several sets of interoperable development tool sets known as "stacks" the most popular of which is call the MEAN stack as well as client libraries for most common languages.

MEAN stands for MongoDB, ExpressJS, AngularJS, and Node.js.

Class time over:

On to the problem.  We all have TBs of relational data.  Our experienced developers already know SQL and our schema. Some new development still fits with structured data. There is no sense in moving OLTP or structured DW data out of relational systems.  There is no financially supportable way to rewrite all of our existing systems from scratch just to take advantage of new paradigms.  Many new applications written primarily to use semi-structured data will need to present the relational data we've been collecting for the past 30+ years.

Many of us are capturing TBs of semi-structured data already. New developers that we want to hire only want to work in the NoSQL space.  Some new applications are natural fits for semi-structured data. RAD techniques require schema flexibility to succeed.  Many new and existing relational based applications could take advantage of all of that semi-structured data to expose new features without a massive redevelopment effort.

How to get relational data into MEAN stack applications and JSON data into C/C++/C# applications is the problem.  Traditionally when faced with data in multiple silos we would either use ETL techniques to copy the data from one silo to another or we would write data access layers using multiple libraries.  

ETL is time consuming and error prone.  We may have to sacrifice precision or internal relationships in the target system. Maintaining a timely, consistent, view is a major constant effort and expense.  Data duplication is itself an expense.  Fast storage is not cheap!

We can't just move everything into MongoDB either.  MongoDB like most NoSQL stores do not support multiple operation transactions, inter-object relationships (references and sub-documents are not relationships), XA transactions (so they cannot participate in transactions across multiple silos).  

The solution:

The solution is Hybrid Application Development using a Hybrid Database system using both traditional and RAD development tools.  Here is what is needed from a hybrid database for this to work:

• Access from traditional development stacks (C, C++, C#, Perl, etc.)
• Access from MEAN and other RAD development stacks
• Full ACID compliant transaction support for all data
• Full relational integrity and data normalization support for all data 
• Ability to store structure data with a predefined schema
• Ability to store semi-structured data with a dynamic schema
• Ability to present both types of data to both stacks in the native format of the data
• JSON as JSON
• Tables as tables
• Ability to present both types of data to each stack in its native format
• JSON as table data for traditional tools
• Table data as JSON for RAD tools

Some NoSQL stores can handle ACID consistency but most can only manage "eventually consistent" transactions.  Most, like MongoDB, can only guarantee consistent transactions and rollbacks for a single document, not for multiple documents in a collection nor for transactions that span multiple collections. Some NoSQL stores can support SQL or an SQL-like query language, however, they do not return data as rows and columns which is what is expected by SQL database access code. 

Some RDBMS systems can:

• Store JSON (or a binary form of it)
• Store a collection as a collection (rather than as a column type)
• Manipulate JSON fields within a document
• Create indexes on JSON fields within the documents in a collection
• Support referential integrity between JSON document fields and relational columns
• Join JSON documents to relational tables

But most cannot do it all.  I believe that these capabilities are the future of database and application development. Today there is one RDBMS product that CAN do it all:

• Accept connections from MEAN stack and other MongoDB clients without modifying the application code
• Accept connections from relational SQL clients
• Support transactions on relation tables and JSON collections that include multiple tables/collections and multiple rows/documents
• Allow joins between JSON collections and other JSON collections or relational tables
• Enhance MEAN applications to support issuing SQL statements and return data in JSON format for those clients
• Permit SQL clients to treat fields in JSON collection documents as ordinary columns
• Support JSON and BSON as class 1 native data types for use as columns in relational tables or as a type to define a collection
• Direct RESTFUL interface to expose all data as web services without middleware
• Store JSON collections as collections
• Permit any MongoDB aware client to treat relational tables collections
• Support most MongoDB DDL and server management commands (including sharding)
• Auto create databases and collections just as MongoDB does, on the fly

I know I'm sounding like a salesman, but I have nothing to sell you other than an idea and my help to achieve it in your organizations, so, bear with me.  What is this magical database system?  It is the latest incarnation of one of the first RDBMS products in the marketplace:

Informix Dynamic Server v12.10.xC4 from IBM

Informix, aka IDS, has all of the required features I mention above but it brings much more to the table:

• Larger documents than MongoDB
• Larger databases than MongoDB
• Industry leading data replication technology
• Four classes of data replication that can all work together
• More capable time proven sharding technology than MongoDB
• Data distribution and centralization capability
• Support for Timeseries data
• Support for Geospatial data
• The only database system that can combine Timeseries and Geospatial data to track your IoT data through SpaceTime
• User defined data types that perform as well as native types
• World class OLTP transaction rates
• Traditional and MEAN stack applications can connect and use both JSON and SQL data in their own native formats
• Centralized databases up to 128PB
• Distributed databases without size limits
• Server fail over and load balancing fully configurable using SLA specifications
• Five 9's reliability
• Near Zero downtime
• Upgrade server versions without downtime
• Instantly bring up additional servers to load balance during peak periods
• Support large numbers of concurrent transactions
• Row level locking (MongoDB locks entire collections only)
• Extremely tunable engine
• Autonomic features for low maintenance overhead and to permit unmonitored operations
• Highly embeddable
• Optional compression of data and index keys
• Advanced query optimizer
• Multiple tenancy
• Informix Warehouse Accelerator returns data up to 1200X faster than the base server for complex queries over huge data sets
• Timeseries in JSON
• GeoJSON support in addition to native GeoSpacial support
• Lucene text search on JSON documents and relational tables
• High speed data loading technology for faster loads of streaming data
• MQTT integration to link to IoT devices

Wrap up:

The future of software development will require hybrid applications!

The future of software development will require hybrid databases!

The future is here now!

Why wait?